VBA to query Excel files


  • MS Access 2007

    I need to automate a data import process for an employee to browse to a file and import the data into a table.  Writing the queries to get and append the data to tables is easy - I need assistance in getting to the Excel files programmatically.  I see a couple options though welcome other suggestions:

    1) create a temporary link to the Excel file, query the data and append to the appropriate table, then delete the link

    2) via VBA, write SQL to get my data into a recordset, then write data into the appropriate tables, and then clean up the tbldefs in the code.

    There are a dozen different spreadsheets, each from a different customer, each a different template.  There are varying rows of header information before getting to the real data, and column names for the real data vary from template to template even.  I need to get only specific columns and have set out a mapping to do just that - I just don't know how to get to the Excel files programmatically.

    With thanks as always.

    • Edited by VBallRef Friday, May 10, 2013 9:39 PM
    Friday, May 10, 2013 9:38 PM

All replies

  • I'm pretty sure you can find something over here:

    Look for the link named:

    'Browse to a single EXCEL File and Import Data from that EXCEL File via TransferSpreadsheet (VBA)'

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, May 10, 2013 9:41 PM
  • I did try that, modifying the code where necessary for file/path references etc., but the database kept hanging at Set objExcel = GetObject(, "Excel.Application").

    I did find another thread about acImport vs acLink in reference to the TransferSpreadsheet method, so I am utilizing that and then running my necessary queries to obtain required data.  After this is done, I simply use DoCmd.DeleteObject acTable, "MyTableName" to remove the linked file.

    I still think it would be very beneficial to some advanced users (but not yet experts!) to know how to directly link to a workbook programmatically, query the data into a tblDef, manipulate the data accordingly and then clean up as appropriate.  While I can probably figure out the whole Excel application business (as above), it's getting to a specific worksheet and then a specific cell range as part of a SQL statement for a recordset.

    Appreciate the response, though!

    Friday, May 10, 2013 10:11 PM
  • What do you mean 'the database kept hanging at Set'?  I've used that code many times before; always worked fine for me. 

    What version of Excel/Access are you using?  Did you set your reference properly?

    Alt+F11 > Tools > References > Microsoft Excel XX.X Object Library

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, May 11, 2013 12:06 AM