Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003

    Question Unanswered: Import from Excel to Access Table

    Hi all

    I am having some problems trying to import from an excel spreadsheet to an access table.

    I ideally want to be able to prompt the user for the location of the excel file as it is possible it may change locations.

    Can anyone give me any ideas on where to start/what to do???

    It's been far too long since I've done VB programming for my own good!

  2. #2
    Join Date
    Nov 2004
    can you please be more specific.
    Are you trying to import or link to append the table?

  3. #3
    Join Date
    Mar 2003
    I am trying to append the information

    I have a spreadsheet with a column of data which needs to be manipulated with a string formula then saved as a different file name (eg data.xls)

    Then I need to get data.xls and append the information to an existing table in access (eg tblData)

    I have two problems

    1) how can i manipulate the excel spreadsheet from within access?? I know how to do the manipulation from within excel but I need to control it from access

    2) I can't work out how to append the information from the spreadsheet to the existing table

  4. #4
    Join Date
    Nov 2004

    Red face

    I will link the spreadsheet into Access and create an append query to insert your existing Access table with the data from your spreadsheet. Anytime your spreadsheet is updated, you have to re run the query so it also update the data in your access table.
    I hope it help.
    Last edited by ggo; 12-02-04 at 22:59. Reason: wrong thing

  5. #5
    Join Date
    May 2004
    Here's how to implement the file chooser:

    Then, without having to go into too much detail, the simplest way to bring data from excel to access is to use the DoCmd.TransferSpreadsheet function either by way of Macro or VB into a temporary table and then append the temporary data into the table you want. The DoCmd.TransferSpreadsheet function is listed in both Access Help and the VBA Object Browser. If those references don't suffice try searching to forum or
    Hack the Planet.

  6. #6
    Join Date
    Mar 2003
    Thanks for the link to the file chooser, I will build that in shortly

    At the moment I am trying to select the data from the spreadsheet so I can copy it to a new workbook. I can't link the information because it is coming from a pivot table.

    So far I have just hard coded the link in but there is a problem with my code... It will open the file but will not go to the correct sheet or change cells, what am I missing???

    Private Sub matData()

    Dim lngColumn As Long
    Dim xlx As Object, xls As Object, xlc As Object, xlw As Object

    Set xlx = CreateObject("Excel.Application")
    xlx.Visible = True
    Set xlw = xlx.Workbooks.Open("C:\access\matlevels.xls") ',,True
    Set xls = xlw.Worksheets("sheet1")

    '--- copy and paste data into new workbook then save -----------------------------

    Set xlc = xls.range("A9")
    Set xls = xls.range(selection, selection.end(xltoright)).select
    Set xlc = xlc.range(selection, selection.end(xldown)).select
    Last edited by RascalBird; 12-02-04 at 23:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts