How Do I Convert an Excel XLS to an Access MDB using Excel VBA

0
votes

I need to use VBA from Excel to load an Excel workbook in access and transfer it out to a database.

Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Test.xls"

I got that far and Excel crashes and has to restart. My plan was to use the following but I can't get that far.

acc.DoCmd.TransferDatabase

Any ideas? I've googled for days and come up with nothing.

*Edit: Thanks for the responses so far
I absolutely have to use Excel VBA, unfortunately. There is an excel spreadsheet that has a bunch of empty columns that are being recognized by the Jet engine as defined columns, too many in fact > 255 (or is it > 256?). I do NOT want to open the Excel worksheet for any reason (this takes far too long over the network). I don't have the option or choice to format it correctly or clean it up. It's easy to convert an XLS spreadsheet into a MDB database inside of access as you all say, but that's not an option. So like I said, I need to use VBA in Excel to manipulate the access object to convert an XLS workbook to an MDB database; Once I have this, the rest will be cake. Thanks so much! I love this site.

2
databaseexcelms-accessvba
Why do you have to use Excel to do this? It seems that Microsoft recommends loading the Excel file from Access. - mechanical_meat
Is this a misprint acc.OpenCurrentDatabase "C:\Test.xls" ? Surely you mean acc.OpenCurrentDatabase "C:\Test.mdb" ? - Fionnuala
Brettville, welcome to StackOverflow. Could you edit your question and give us more detail? Can you give us a step-by-step of what you want to do? Are you trying to import an Excel worksheet into an Access db? - PowerUser

2 Answers

1
votes

This task is straightforward if you're able to use VBA from within Microsoft Access; e.g.:

DoCmd.TransferSpreadsheet , , _
"tblImportFromExcel","C:\path\to\myfile.xls", True, "A1:B200"

TransferSpreadsheet documentation.

1
votes

What is wrong with the suggested solution from Adam Bernier (with the addition from PowerUser concerning using an access object from within Excel); your last comment was after those suggestions and you did not reply.

Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Test.mdb"

acc.DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    Spreadsheettype:=acSpreadsheetTypeExcel8, _
    TableName:="tblImportFromExcel", _
    Filename:="C:\path\to\myfile.xls", _
    HasFieldNames:=True, _
    Range:="A1:B200"

(Adapt as needed, especially the HasFieldNames and Range). If this does not work, then there is probably something really wrong with your Excel-Sheet.

The only other thing I can think of (but that would mean to open the file) is to save the Excel-Sheet as XML and transform the values via XSLT in a more suitable format, then import the resulting XML. But that might be overkill (how complex is your file, how often do you need this import to proceed).

HTH Andreas