CODEWITHSUNDEEP
excelvbams-access
Nikolai Nikitine
Nikolai Nikitine

Reputation: 21

importing data from excel to access using ms excel vba

I'm having an issue that occurs randomly (run it on my machine - it works about 90% of the time, couple of attemps on client's machine it didn't work 100% of time) here's the code:

sub importdata()
Dim dbpath As String
Dim acc As New Access.Application
'bunch of stuff
acc.OpenCurrentDatabase dbpath & "\Database.accdb"        
acc.DoCmd.TransferSpreadsheet acImport, 10, "tbl_SalesData", dbpath & "\Dashboard 2015.04.17.xlsm", True, "DataForImport!"
acc.CloseCurrentDatabase
acc.Quit
'bunch of more stuff
end sub

DataForImport is the sheet where the data resides. It's in a table; however, access failed to recognize the table name "Sales" as a range to import, so i went with importing the entire sheet.

on the client's machine the code gives an error on the transferspreadsheet line (tries to open another instance of excel with the dashboard file and says that the file is already open, error three thousand something); however, on my machine it runs just fine.

Did anyone run into a similar issue? is there a better way to push data from excel to access?If not, does transferspreadsheet prefer closed files, so i need to close the file i'm importing data from (Dashboard) before running that piece of code and re-opening it after?

Thank you!

Answer:

saving a temp file and pushing from it works well

Workbooks.Add.SaveAs dbpath & "\tempwb.xlsx"
        Set tempwb = Application.Workbooks("tempwb.xlsx")
        Application.Workbooks(dashboard).Activate
        acc.OpenCurrentDatabase dbpath & "\Database.accdb"
        Application.Workbooks(dashboard).Sheets("DataForImport").Copy Before:=tempwb.Sheets(1)
        tempwb.Save
        tempwb.Close
        acc.DoCmd.TransferSpreadsheet acImport, 10, "tbl_SalesData", dbpath & "\tempwb.xlsx", True, "DataForImport!"
        Kill dbpath & "\tempwb.xlsx"
        acc.CloseCurrentDatabase
        acc.Quit
        Set acc = Nothing

Upvotes: 2

Views: 15468

Answers (2)

Mark Horner
Mark Horner

Reputation: 118

Access.application -> you are using early binding. Does your client have a different version of Office? You may want to re-write it using late binding (Such as dim acc as Object, then set Acc = CreateObject("Access.application") )then you'll probably see the same success rate on client's machine as yours.

For transfer spreadsheet you will need a closed file, to get around this there's a simple method I use which is something along the lines of;

xls.save 'save your current file
filecopy currentpath, dir(currentPath) & "\zzztmp.xls" 'make a copy in the same folder named zzztmp
docmd.transfer here with the TEMP file path
kill dir(currentPath) & "\zzztmp.xls" 'this will delete the temp file and our data is in Access and the user is happy

This way the user has no idea you have saved a copy and imported it into access, but it just means you don't have to close the file and confuse the user with their excel file vanishing then reopening to import into access.

Upvotes: 0

ZX9
ZX9

Reputation: 977

In theory, your code should be fine.

In reality, and because Office products generally don't like what you're trying to do, I highly recommend you do all imports in Access.

Then you only need you one line of vba:

DoCmd.TransferSpreadsheet acImport, 10, "tbl_SalesData", dbpath & "\Dashboard 2015.04.17.xlsm", True, "DataForImport!"

...or an equivalent non-VBA Macro, which can be built step-by-step.

Upvotes: 1

Related Questions