How to use ADOX on Excel? How to automate Excel invocation on existing xls file from vbs?

[Siegfried Heintze]

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/activmonitor/windowsmanagement/scripts/msoffice/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