CODEWITHSUNDEEP
excelms-accessvba
Lee Chant
Lee Chant

Reputation: 103

Import Excel file to Access with VBA

Good Afternoon,

I have been working on an Access database project this year, with VBA a complete unknown to me going into this. I have the VBA code importing Excel data into a temporary table in my database and then appending to the correct table, it works but I am curious if this is the most effcient way in regards to coding and memory usage?

Function TempReview()  
On Error GoTo TempReview_Err  

DoCmd.SetWarnings False  
DoCmd.OpenQuery "ClearTempReview", acViewNormal, acEdit  
DoCmd.TransferSpreadsheet acImport, 10, "TempReview", "M:\filename.xlsx", True, ""  
DoCmd.OpenQuery "AppendReview", acViewNormal, acEdit   

TempReview_Exit:  
Exit Function  

TempReview_Err:  
MsgBox Error$  
Resume TempReview_Exit  

End Function

I have similar features in other areas of the database, which I also wish to improve if I can.

Regards

Lee

Upvotes: 0

Views: 13173

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5819

You do not need the other two tables at all. When you use DoCmd.TransferSpreadsheet method to import. If the table you are passing exists the data will be appended into the table, if the table does not exist then a new table is created. 

Function TempReview()  
On Error GoTo TempReview_Err   
    DoCmd.TransferSpreadsheet acImport, 10, "yourActualTable", "M:\filename.xlsx", True, ""

TempReview_Exit:  
    Exit Function  

TempReview_Err:  
    MsgBox Error$  
    Resume TempReview_Exit  
End Function

Upvotes: 1

Related Questions