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
and I get a syntax error in FROM clause, error no. 3131. So I write
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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
JMaherAuthor Commented:
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JMaherAuthor Commented:
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?
Open a Dummy Jet database e.g. Sample.MDB and attach your Excel 5.0 spreadsheet to it (Jet/Attachments..) e.g. as MySheet.
does the job without having to add any funny characters.
I've just tested it with a Ver 5.0 spreadsheet.

Good Luck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JMaherAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.