Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use ADOX on Excel? How to automate Excel invocation on existing xls file from vbs?
I got a little ADO/VB script off some web site that performs SQL SELECT
statement on an xls file and it works! (see below) (1a) How do I enumerate all the tables and other features in the spreadsheet in VB.NET with ADOX or DAO? I have an existing program that presently will do this with SQL Server and MS Access: it will enumerate all the tables and fields names/types/values in those tables and display them in XML. When I try using ADOX I get: COMException was unhandled: Could not find installable ISAM. Check the error code property of the HRESULT returned by the COM object. How do I do that? (1b) When I try using DAO it says Unrecognized database format. Check the error code property of the exception to determine the HRESULT of the COM object. (2) As you can see from my comments, I tried to fire up excel automatically. Presently the script does not run unless excell is started up already on the xls file.I think my attempt did not work because the current worksheet was not properly selected? How do I automate the invocation of excel on my worksheet so I don't have to manually start it up before running the little script below? Thanks, Siegfried 10 11 On Error Resume Next 12 13 Const adOpenStatic = 3 14 Const adLockOptimistic = 3 15 Const adCmdText = &H0001 16 for kk = 0 to WScript.Arguments.Count-1 17 Wscript.Echo "begin " & kk & " = " & Wscript.Arguments.Item(kk) 18 19 ' http://www.activexperts.com/activmon...soffice/excel/ 20 ' Set objExcel = CreateObject("Excel.Application") 21 ' Set objWorkbook = objExcel.Workbooks.Open (WScript.Arguments.item(kk)) 22 ' objWorkbook.Select 23 ' objExcel.Worksheets("Sheet1").Select 24 ' objExcel.Visible = True 25 26 Set objConnection = CreateObject("ADODB.Connection") 27 Set objRecordSet = CreateObject("ADODB.Recordset") 28 29 objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 30 "Data Source=" & WScript.Arguments.item(kk) & ";" & _ 31 "Extended Properties=""Excel 8.0;HDR=Yes;"";" 32 33 If Wscript.Arguments.Item(kk) = "Test.xlsx" Then 34 Wscript.Echo "begin insert " & WScript.Arguments.item(0) 35 objRecordset.Open "INSERT INTO [Sheet1$] (Name, Number) VALUES('sdd',323)", objConnection, adOpenStatic, adLockOptimistic, adCmdText 36 Wscript.Echo "end insert " & WScript.Arguments.item(0) 37 Set objRecordSet = CreateObject("ADODB.Recordset") 38 End If 39 40 objRecordset.Open "Select * FROM [Sheet1$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText 41 42 call DumpFieldNames(objRecordset) 43 call DumpRecordSet(objRecordset) 44 45 46 ' objWorkbook = Nothing 47 ' objExcel = Nothing 48 ' objRecordset.Close 49 next 50 Wscript.Echo "end query " 51 Wscript.Quit 52 53 54 Sub DumpFieldNames(objRecordset) 55 dim sFieldNames 56 sFieldNames = "" 57 sFieldTypes = "" 58 For ii = 0 to objRecordset.Fields.Count -1 59 If sFieldNames = "" Then 60 Else 61 sFieldNames = sFieldNames & ", " 62 sFieldTypes = sFieldTypes & ", " 63 End If 64 sFieldNames = sFieldNames & objRecordset.Fields.Item(ii).Name & "(" & objRecordset.Fields.Item(ii).Type & ")" 65 sFieldTypes = sFieldTypes & objRecordset.Fields.Item(ii).Type 66 Next 67 68 WScript.Echo sFieldNames 69 WScript.Echo sFieldTypes 70 End Sub 71 72 Sub DumpRecordSet(objRecordSet) 73 74 jj = 1 75 Do Until objRecordset.EOF 76 77 ' Wscript.Echo objRecordset.Fields.Item("Name"), objRecordset.Fields.Item("Number") 78 79 dim sFieldValues 80 sFieldValues = "" 81 For ii = 0 to objRecordset.Fields.Count -1 82 If sFieldValues = "" Then 83 Else 84 sFieldValues = sFieldValues & ", " 85 End If 86 sFieldValues = sFieldValues & objRecordset.Fields.Item(ii) 87 Next 88 WScript.Echo sFieldValues 89 90 objRecordset.MoveNext 91 92 jj = jj + 1 93 if jj 8 then 94 exit do 95 end if 96 Loop 97 End Sub 98 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to automate excel file sending by outlook from within the file | Excel Discussion (Misc queries) | |||
Automate Excel download of csv file without proper URL? | Excel Programming | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
Open an existing excel speadsheet, opens Excel but not the file?? | Excel Discussion (Misc queries) | |||
existing excel file when clicked opens blank excel document | Excel Discussion (Misc queries) |