Import Spreadsheet

[afs24]

I'm trying to import this file from an excel file to an access database. I'm using the macro function but it's not working for some reason. This is how I wrote the location in the File Name section in the macro:

L:\QCKRESP\Vendor Operations\Summary Reserve On Hand Weekly OH

The L drive is mcssrv02. Would it be

mcssrv10:\QCKRESP\Vendor Operations\Summary Reserve On Hand
or
L:\QCKRESP\Vendor Operations\Summary Reserve On Hand
Weekly OH

Can I code this to make it easier? The table I'm importing it to is tbl_Pre. I need cells A1: F100 imported. Could you provide me with an example.

 

[SydneyGeek]

Do you have the filename in quotes? Otherwise, VBA will not interpret the name correctly.

 

[SydneyGeek]

Another thought: You could create a named range for the cells that you want to import. Access will then know exactly what to grab.

Currently away from an Access machine, but will have a look for an example when I get back.

 

[SydneyGeek]

As I mentioned in the previous post, using a named range is probably the best way to refer to your target cells.

The code to import the data is shown below:

Function ImportData()

DoCmd.TransferSpreadsheet acLink, 8, "MyRangeName", "Full path of file that contains the range.xls", True, "Destination Table"

End Function

I placed this inside a module called basImport. To call it you must make it a function -- then you can trigger the import from, say, a command button.

In the Click event of the command button type the statement

basImport.ImportData

That should do it.

Denis