docmd.transferspreadsheet

[mehman]

I am unable to transfer data from excel 2007 to access 2007 using the docmd.transferspreadsheet command.


Hence the workaround I am using is

- create a linked table in access linked to the excel file

- run a query which will append data from the linked excel table into the access table.


Any suggestions.


Thanks in advance.

 

[SirJB7]

Hi, mehman!


Though it's more an Access than an Excel question, maybe today's your lucky day. I have no 2007 Office version at a hand, so it's made under 2010, but I guess it would be suitable for you. So give a look at this file:

https://dl.dropbox.com/u/60558749/docmd.transferspreadsheet%20%28for%20mehman%20at%20chandoo.org%29.rar


I created a blank workbook, typed a few rows data for two columns, saved the workbook.

I created a blank database, created a table with proper definition, saved the database.


Then from the Immediate window of Access typed this command:

Application.DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "Tabla1", "CabloDocumentosPlanillasChandoo.orgdocmd.transferspreadsheet (for mehman at chandoo.org).xlsm", true, "Hoja1!A:B"


The workbook is .xlsm (with macros) type because just I saved it alike, but it has no built-in code, and this method will work with any Excel file type, as long as you specify the correct value for second parameter. I used the full path qualification for issuing the docmd, but being in the same folder you can just use the filename. Last parameter might be omitted if you want to import the whole first sheet.


The result is the worksheet imported within the table.


If you have any issue reproducing this, please upload the samples workbook and database.


Regards!