Help with VBA "DoCmd.TransferSpreadsheet acImport ......."

You need to instantiate another instance of Access, set its CurrentDatabase
appropriately, and then do your import. Something like the following
untested air-code:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase "C:\Folder\File.mdb"
appAccess.DoCmd.TransferSpreadsheet acImport, , strTableName, myPath &
strTableName & ".xls", True
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
news:

2003

I would like the data imported into a table in a non-current() database.

DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & strTableName
& ".xls", True
(The above command works but I want the data to go to strTableName in
another database]

Any coding help appreciated

TIA EagleOne

Excellent and Complete answer.


"Douglas J. Steele" wrote:

You need to instantiate another instance of Access, set its CurrentDatabase
appropriately, and then do your import. Something like the following
untested air-code:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase "C:\Folder\File.mdb"
appAccess.DoCmd.TransferSpreadsheet acImport, , strTableName, myPath &
strTableName & ".xls", True
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
news:
> 2003
>
> I would like the data imported into a table in a non-current() database.
>
> DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & strTableName
> & ".xls", True
> (The above command works but I want the data to go to strTableName in
> another database]
>
> Any coding help appreciated
>
> TIA EagleOne