Import Excel spreadsheet into Access

[moobcinos]

Hi All,

I am a newbie to VBA coding and am trying to do a relatively simple thing. I am attempting to automate the process of importing an Excel spreadsheet into an Access database, run a couple of queries against the data in the newly imported table, then export the resulting reports to Word format if possible.

Below is the code I am using:
***********************************************************
Function GET_EXPORT_DATA()
On Error GoTo GET_EXPORT_DATA_Err

CurrentDb.Execute "Delete * from [Export Scorecard]"
DoCmd.TransferSpreadsheet acImport, 8, "Export Scorecard", "T:\BIM\BPM\_ExportDB", True, ""
DoCmd.OpenQuery "CTE Query", acViewNormal, acEdit
DoCmd.OpenQuery "DayToPGI", acViewNormal, acEdit


GET_EXPORT_DATA_Exit:
Exit Function

GET_EXPORT_DATA_Err:
MsgBox Error$
Resume GET_EXPORT_DATA_Exit

End Function
******************************************************

The problem is that when I try to run it, I get the error message below:

'The Microsoft Jet Engine cannot open the file “”. It is opened exclusively by another user, or you need permission to view the file.'

Any assistance provided would be greatly appreciated. Thanks!

 

[PaulBricker]

Take the "" out of the last argument. Access doesn't know what to do with that.

Paul

 

[Remou]

Double check that "T:\BIM\BPM\_ExportDB" is the name and path of the file to be imported.

 

[moobcinos]

I tried both of these suggestions to no avail.

 

[Remou]

Have you checked the folder permissions?

 

[bubba100]

Is the name of the file _ExportDB or _ExportDB.xls?

 

[moobcinos]

@Remou - I thought it may be the folder security as well, so I copied the spreadsheet to my local machine and modified the code to point to it. I still got the same error

@bubba100 - I noticed that too after the fact, so I changed it to _ExportDB.xls, which is the name of the file. I still got the same error

 

[moobcinos]

This is what my code looks like now. I moved the spreadsheet I'm trying to import onto my Desktop just for testing to see if it was a security issue on the network drive in which it resides.
***********************************************************
Option Compare Database

Function GET_EXPORT_DATA()
On Error GoTo GET_EXPORT_DATA_Err

CurrentDb.Execute "Delete * from [Export Scorecard]"
DoCmd.TransferSpreadsheet acImport, 8, "Export Scorecard.xls", "C:\Documents and Settings\DWALK18\Desktop", True
DoCmd.OpenQuery "CTE Query", acViewNormal, acEdit
DoCmd.OpenQuery "DayToPGI", acViewNormal, acEdit


GET_EXPORT_DATA_Exit:
Exit Function

GET_EXPORT_DATA_Err:
MsgBox Error$
Resume GET_EXPORT_DATA_Exit

End Function
***********************************************************

 

[PHV]

What about this ?
DoCmd.TransferSpreadsheet acImport, 8, "Export Scorecard", "C:\Documents and Settings\DWALK18\Desktop\_ExportDB.xls", True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

That is a bit tangled. The syntax is:

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

so

[tt]DoCmd.TransferSpreadsheet acImport, 8, "Export Scorecard", "C:\Documents and Settings\DWALK18\Desktop\Export Scorecard.xls", True[/tt]