Importing a user specified spreadsheet and range into Access 2007 Help required.

[GerryP]

Hi, Firstly I am an absolute novice with respect to VB Coding.... so please be gentle !



This is what I would ideally like to do.

From an 'Import' button on an Access form:
1. Be asked to select the spreadsheet name (i.e. list xls files from the hard drive as the spreadsheet names will change over time)
2. Be asked for the range to import e.g A1:AF20 (this will change and therefore cannot be hard coded)
3. Import the specified range into a specific Access table called 'RC Data'
4. Confirm that the data has been imported (Just a Msg)

Additional Info. The spreadsheets will contain multiple worksheets but the required worksheet will always be called 'Survey'.

I'm thinking something like the 'DoCmd.TransferSpreadsheet' will work but I just don't know how to go about the coding to achieve the above.

Any help would be really appreciated.

Thanks
Gerry

[June7]

Use a form to input the import specifications then VBA code to concatenate reference to controls on form into the TransferSpreadsheet action statement.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "[RC Data]", "your file path/" & Me.txtSheetName, , Me.txtStartRange & ":" & Me.txtEndRange

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Displaying the list of Excel files from directory into a combobox RowSource is another issue. http://www.everythingaccess.com/tuto...es-recursively

[GerryP]

Thanks June7

I get a compile error msg (Method or Data Member Not Found) and Me.txtSheetName highlighted when I run the code. Do I have to DIM the statement 'Me.txtSheetName' as a string at the beginning of the code?

Many thanks

[June7]

No, txtSheetName is just my generic example for a textbox on a form, as are txtStartRange and txtEndRange. Use your actual names.

[GerryP]

Hi June7
Ah, that makes sense now (apologies for being slow). I've done the form and I can get the data into the table now which is great. One last question if I can. Is there an easy way of searching for the xls file without having type in the path and filename? I guess what would be utopia would be for the code to open the windows directory and then you can browse for/select the file that you want. No problem if it's not easy or practical, at least I can now get the data imported and the ranges are variable.

Many thanks

[June7]

Check out the FileDialog method

http://stackoverflow.com/questions/4...-in-access-vba

[GerryP]

That's great, Thanks for all you help June7