[RESOLVED] Problem opening some EXCEL files with bound controls

[camoore]

I have two problems, of related but different nature. This question refers to only the first :

(VB6 running under XP)

On my form I have placed an ADODC1 and a DATAGRID1. The DATAGRID has its DATASOURCE set as ADODC1.

I am using this simple code to load a .xls data file to the ADODC : The string PATH has been set to the path of the desired .xls file - eg. "C:\Test.xls"

CHARA = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH & ";Extended Properties=""Excel
8.0;HDR=No;IMEX=1"";"

Adodc1.ConnectionString = CHARA

Adodc1.RecordSource = "[SHEET1$]"

Adodc1.Refresh

Adodc1.Caption = PATH

On certain .xls files this works fine, and all the rows/columns appear in the datagrid. However when I attempt to load other .xls data files I get an error message :

"Invalid SQL Statement. Expected Delete, Insert, Procedure, Select or Update."

I suspect the problem lies with the connection string. Can anyone please assist?

camoore

[camoore]

No replies received, but I slogged on at it and found the problem - which owes to my inexperience in this area.

The problem was not the connection string but with the line :

Adodc1.RecordSource = "[SHEET1$]"

I had thought that this would link ADODC1 to the first sheet in the selected .xls file. Well, it does so long as that first sheet is named "Sheet1" but does not if the first sheet has been assigned some other name. It was therefore necessary to write a little routine which examines the .xls file and lists the names of the sheets (in a list box). Then the user may click on the sheet name desired and that name is used in the ADODC1.Recordsource = statement.

I derived the sheet name listing procedure from this forum thread #236839 :

http://www.vbforums.com/showthread.p...orksheet-names

The code therein suggested by ARMBRUSTER works, but I found certain problems with it and wrote my own version based on the 236839 suggestion.

I will mark this thread as Resolved. Should anyone want my amended sheet name listing code then please just make a further post to this thread, or send a P.M.

camoore