Reading Excel file from VB6

[laura]

I was able to read the Excel file from VB, but when I try to display the columns, it displays null. Does anyone know why? My cell columns contain numeric.

Thanks, Laura

[JHausmann]

Can you post your code?

[laura]

Here is my code. The problem is rs.Fields(I).Value show null when it should have numeric values.

Private Sub Command1_Click()
'uses ADO 2.x, ADO 2.x for DDL and Security
Dim ax As ADOX.Catalog
Dim cn As ADODB.Connection
Dim rs As Recordset

Dim I As Long
Dim j As Integer

Dim strTemp As String

Dim tbl As ADOX.Table

Set cn = New Connection
Set ax = New Catalog

'open connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Zip\ADO\Xls2Mdb\004.xls;Extended Properties=Excel 8.0"

ax.ActiveConnection = cn

'loop thru tables
For Each tbl In ax.Tables

Set rs = cn.Execute("Select * from [" & tbl.Name & "]")

'display all table names
Debug.Print "TABLE: " & tbl.Name & vbCrLf & "========================" & vbCrLf

'display all field names
For I = 0 To 7 'rs.Fields.Count - 1
strTemp = strTemp & rs.Fields(I).Name & vbTab
Next I

Debug.Print vbTab & vbTab & strTemp
strTemp = ""

'display all values
Do Until rs.EOF = True
j = j + 1
For I = 0 To 7 'rs.Fields.Count - 1
strTemp = strTemp & rs.Fields(I).Value & vbTab & vbTab

Next I

strTemp = strTemp & vbCrLf
rs.MoveNext
Loop

Debug.Print strTemp
Next tbl

End Sub

[JHausmann]

I don't access Excel thru ADO, it just adds another place where things can go wrong. However, it also has the disadvantage that everyone must have Excel installed to use my apps.

[laura]

Does MicroSoft know about this bug? Is there a fix for this?

[laura]

Any Database Gurus out there that can help me with this one?
Thanks!

[JHausmann]

You might try the 'transferspreadsheet' function to see if it returns anything 'odd' into the Access table it will create, assuming you have Access. The jet engine also tends to be less problematic...

[laura]

JHausmann, I'm new to this "DoCmd" object. How do I reference it or use it in VB? Thanks!

[JHausmann]

Under Project/references, select the version of MSAccess that is most appropriate for you. Then,

Dim accobj As New Access.Application


acconj.OpenCurrentDatabase ("filename.mdb")
accobj.DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel97,tempload,"c:\filename.xls"

should load "filename" into your database (filename.mdb) table "tempload".

[laura]

I need your suggestion. I'm working on a project that needs to process many Excel files. I don't want to convert these file to Access because of the overhead. I just want to read the content of the files and extract the infos from them. Any suggestion on how to approache this?

Thanks!

[ttingen]

Reference Excel in your VB app and then use its object model to obtain the information from the xls file. You can then process the information you obtain as you please.

Another way to do this that I've looked at is through XML. On msdn they have a sample application that performs similar functions. It has an Excel template that generates an XML file when the user saves the spreadsheet. The XML file can be uploaded to the server where the information is processed. They didn't use excel because of the overhead that it placed on the server. The XML parser on the server (which comes with IE5) processes the information much faster.

If you want to read about it here's a couple of pages you can look at:

Excel to XML:
http://msdn.microsoft.com/library/techart/fmcorpxml.htm

XML Processed on the server:
http://msdn.microsoft.com/library/te...corpcmpnts.htm