TransferSpreadsheet Method

[badmutha]

Does someone know is it possible to use standard windows
"open file" dialog box to specify the Excel Spreadsheet - file from which to import data instead of always changing the file name from the VB code? And how do you specify the worksheet using the TransferSpreadsheet (my xls-files have almost 30 different sheets) ?

Any easier/different solutions to my problem are also appreciated...

 

[JPeters]

Are you trying to export these spreadsheets and then open them in excel? Or are you just trying to open existing spreadsheets using Access? Could you clear this up a little bit for me. I have a few suggestions that I would love to make, I just need to know a bit more first. ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[GHolden]

I can help with the second part

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "QQuestionAnswer", "D:\test.xls", False, "Sheet1!A1:A3"

Put the sheet name in the cell range to import

 

[JPeters]

Want Query to Open an Excel Spreadsheet
thread181-326269

Read this. It explains what I think you are trying to do.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[badmutha]

What I`m trying to do is import data from a Excel Spreadsheet (from all the worksheets at the same time, if possible) to an existing Access table using a "open file" - dialog box to specify the file...

I hope this clears it up... sorry my english is not perfect.

 

[JPeters]

Ooooh, so I had it reversed.. thought that you were trying to export then open. Eek. I must need coffee. ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[raftech]

I just wrote a routing that does this:

Step 1 was to write a routine that stored all the file names into a table.

The second step was to loop through the list of files, and open each one in a recordset that could be used to populate a master table

If this is what you're looking for, let me know and I'll get you some code or lead you to the right help files so you can figure it out.

Rob

 

[badmutha]

Maybe I could use that method. At least it gives me a starting point... Any code examples or info about those help
files would be much appreciated, thanks...