Is there any to use a VB program to sql imfo from a excel sheet to a DBGrid
Printable View
Is there any to use a VB program to sql imfo from a excel sheet to a DBGrid
"Is there any to use a VB program to sql imfo from a excel sheet to a DBGrid"
Do you want to show Excel data in a DBGrid or to use SQL against an Excel datasource?
In either case the answer is yes. The Jet Excel driver supports limited SQL and gives you a number of choices.
Depending on how much control you have to have (such as for export queries) you might have to look into linking ... tutorial source code for this is at: http://www.smithvoice.com/vb5expt.htm) but from what you are describing here you don;t have to go that far.
If you just want to use the DBGrid then you probably would be best to use a datacontrol for the binding. For that you just set the Datacontrol Database property to "Excel", set the DatabaseName property to the location of the workbook or worksheet path and so on.
To use the Excel workbook/sheet as an actual data source you just have to open it as a "Database".
In DAO:
Dim dbXL as Database
'this is the syntax
Set dbXL = Workspaces(0).OpenDatabase([path to Excel Location , just the folder path for Excel 3/4 fully qualified path for Excel 5 or higher], False, False, [Excel Specifier])
'this is an example:
Set dbXL = Workspaces(0).OpenDatabase("C;\MySheets", False, False, "Excel 4.0")
'this is another example
Set dbXL = Workspaces(0).OpenDatabase("C;\MySheets\MyEX97.xls", False, False, "Excel 8.0")
Excel 3 & 4: The path to the folder containing the worksheet files is the "Database", the worksheet files are the "tables"
Higher versions: The "Database" is the fully qualified path to the Workbook file, the worksheets inside that file are the "tables"
Specifiers:
Excel 3.0
Excel 4.0
Excel 5.0 (for excel 4/95)
Excel 8.0 (for excel 97)
Excel 9.0 (for excel 2000)
ADOX 2.1's Jet 4.0 provider provides the same functionality...
Dim cn as ADODB.Connection
Dim sXLPath as string
Set cn as new ADODB.Connection
cn.provider = "Microsoft.Jet.OLEDB.4.0"
cn.properties("Extended Properties") = [Specifier]
cn.open sXLPath
-Smith
------------------
http://www.smithvoice.com/vbfun.htm