importing data from excel to access using ms excel vba

Question

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

can you try opening a dao recordset. perform select query. and push the result set to your range? — Krish
– Krish, Commented May 1, 2015 at 8:04
check the odbc drivers in control panel/administration tools — scraaappy
– scraaappy, Commented May 1, 2015 at 10:18
would the odbc drivers be under odbc data sources in admin tools? — Nikolai Nikitine
– Nikolai Nikitine, Commented May 1, 2015 at 10:32
thank you, krish. using a different method was my next option. will try it later in the day. — Nikolai Nikitine
– Nikolai Nikitine, Commented May 1, 2015 at 10:34
This is one of those process and not programming questions. MS Access should really be the central handler in data management as it interacts with whole data objects (not select ranges), comes equipped with user interface and report generator, and finally it is layered on a database relational SQL engine. Consider running DoCmd.TransferSpreadsheet inside Access, then use Excel as the end-use program for final presentation/graphics. And yes, Excel workbook must be closed to run this command. Hence, use Access as the handler! — Parfait
– Parfait, Commented May 1, 2015 at 17:00

ZX9 · Accepted Answer · 2015-05-01 17:51:09Z

1

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.

answered May 1, 2015 at 17:51

ZX9

9973 gold badges16 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikolai Nikitine Over a year ago

thank you. added that method to the list.+ adodb connection imports. will try all three on the client's machine and see which one works best.

Mark Horner · Accepted Answer · 2015-05-03 20:03:19Z

0

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.

answered May 3, 2015 at 20:03

Mark Horner

1181 silver badge6 bronze badges

1 Comment

Nikolai Nikitine Over a year ago

The client is on the same version of office (2013) that's an idea about a temp file. i'll give that a shot too. thank you!

Collectives™ on Stack Overflow

importing data from excel to access using ms excel vba

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related