JMaher
asked on
a"SELECT" query on Excel in VisData
I am trying to open an Excel 5.0 using VisData in VB5. I go to VisData, open the file using "Excel", and when I right click on the table, I get the sheet up on screen. However, when I try to create a SELECT query, I get problems. In the Database window, it shows the table as being called
"Sheet1$". So I put in an SQL query
SELECT * FROM Sheet1$
and I get a syntax error in FROM clause, error no. 3131. So I write
SELECT * FROM Sheet1
and I get asked if it is a Pass Through  query. I say "No" and then get
Cannot find object "Sheet1". error 3011.
I cannot work out what the problem is. I have even tried using the query builder, with no success. I should also say that I have been able to access text files (using the correct commands of course) with no trouble whatsoever. I am totally stumped. please help.
"Sheet1$". So I put in an SQL query
SELECT * FROM Sheet1$
and I get a syntax error in FROM clause, error no. 3131. So I write
SELECT * FROM Sheet1
and I get asked if it is a Pass Through  query. I say "No" and then get
Cannot find object "Sheet1". error 3011.
I cannot work out what the problem is. I have even tried using the query builder, with no success. I should also say that I have been able to access text files (using the correct commands of course) with no trouble whatsoever. I am totally stumped. please help.
I've found that VisData (in VB6, and I've just verified it on another machine in VB5) will open an Excel 97 spreadsheet but will not be able to do anything with it. Try opening the spreadsheet in question and saving it as an Excel95 (Excel 5.0) worksheet. You'll notice that each table now has *two* entries (one with a $ and the other without), open the table without the $. You'll need to tell Excel that it's not a pass-thru query, though.
ASKER
Just to be difficult - I don't have Excel 97. It is already in 5.0 format. There is only 1 "table"; with the $. Also, I have tried this on a few different spreadsheets, and have tried everything I can think of in the 'From' clause. I have used double quotes, entered the full path of the file, tried full stopos, with and without everything I can think of. And yes, I HAVE tried Sheet1$ and Sheet1.
Would you like to e-mail me a spreadsheet to see if Excel97 somehow saves an Excel95 format (.xls file) differently than your version of Excel95 (stranger things have been known to happen)? The only difference, in VisData, between the two was that when my Excel97 application saved the file as an Excel 5.0 format file, it had Sheet1 and Sheet1$ available in the table list in Visdata. When I used the *same* file, in the Excel97 format, it only had Sheet1$ available.
Or, if you prefer, I can send you one of my spreadsheets for you to test. Let me know your preference.
I do agree that you can do nothing in Visdata, absolutely nothing, if all you have is Sheet1$ in the table list. I'm wondering if you need to apply a Service Pack to Excel 95 to correct the problem you're experiencing.
Or, if you prefer, I can send you one of my spreadsheets for you to test. Let me know your preference.
I do agree that you can do nothing in Visdata, absolutely nothing, if all you have is Sheet1$ in the table list. I'm wondering if you need to apply a Service Pack to Excel 95 to correct the problem you're experiencing.
ASKER
Well, at this stage I might send you a "dummy" test spreadsheet that I have made up in Excel 5.0. How do I send it to you?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried to follow what you said, but I didn't even know you could do something like that. So I used the help topics, and still had trouble. Could you explain in a bit more detail what I have to do? I'm sure it is simple, but unfortunately, so am I.
If you open VisData and got to File/Open Database.../Jet Engine MDB...
and open e.g. Nwind.mdb
All the tables in Nwind.mdb database will appear under your Tables window.
Now got to Jet/Attachments.. and click on New button.
In the form that will come up select your Connect String (Excel 5.0), Attachment Name (e.g. MySheet), Database Name (type in full path and file name to your .XLS file), Table to attach (select from the drop doun list single sheets to attach).
Click Attach button. Then Close button.
Now you will see in your Tables window
MySheet -> Excel 5.0
as an attached table. If you need any more sheets to attach, please repeat the steps described.
Good Luck
and open e.g. Nwind.mdb
All the tables in Nwind.mdb database will appear under your Tables window.
Now got to Jet/Attachments.. and click on New button.
In the form that will come up select your Connect String (Excel 5.0), Attachment Name (e.g. MySheet), Database Name (type in full path and file name to your .XLS file), Table to attach (select from the drop doun list single sheets to attach).
Click Attach button. Then Close button.
Now you will see in your Tables window
MySheet -> Excel 5.0
as an attached table. If you need any more sheets to attach, please repeat the steps described.
Good Luck