Consulting

Results 1 to 5 of 5

Thread: Import Excel Spreadsheet Into Access

  1. #1
    VBAX Tutor jo15765's Avatar
    Joined
    Oct 2011
    Posts
    281
    Location

    Import Excel Spreadsheet Into Access

    Their is a spreadsheet downloaded from a server daily. I want to through VBA create a table in access 2000 and import the spreadsheet (could be .xls or .xlsx) into the newly created table.

    This is what I was working with --- not working properly, but I think *fingers crossed* it's close to it
    Function ImportSpreadsheet
    Dim filename, table, worksheetName As String
    Dim hasFieldNames As Boolean
    Dim ws As Integer
    
    bhasFieldNames = False
    
    table = "TestingImportThroughVBA"
    worksheetName = "Sheet 2"
    filename = "C:\Testing\TestWorksheet.xls"
    
    For ws = 1 to 1
      Do While Len(filename) > 0
        DoCmd.TransferSpreadsheet acImport, acSpreadshetTypeExcel9, table, filename, hasFieldNames
      Loop
    Next ws
    End Function
    Last edited by jo15765; 01-15-2015 at 12:58 PM.
    Go to Heaven for the climate, Hell for the company.
    ~~Mark Twain

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    first you dont need loops here.
    remove them leaving one line, try:
    DoCmd.TransferSpreadsheet acImport, acSpreadshetTypeExcel9, table, filename, hasFieldNames, worksheetName & "$"
    dont forget $ sign after worksheet name.

    it's just an adoption to below code.
    below is a working code that i'm currently using to import multiple sheets (xl sheet name = acc table name) with Office 2010 by deleting current Access tables.
    as i don't have Office 2000 installed on my pc, i can't test the possible Office 2000 adoption of the code.
    afaik you can't directly import data from upper versions of Office programs.


    tutorial:
    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    Sub import_multi_XL_tbl()
        Dim wsList As Variant
        Dim xlFile As String
        Dim i As Integer
        
        wsList = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
        xlFile = "C:\MyFolder\MySubFolder\MyXLFile.xlsm"
        
        With DoCmd
            .SetWarnings False
            For i = LBound(wsList) To UBound(wsList)
                On Error Resume Next
                .RunSQL "DROP TABLE " & wsList(i)
                .TransferSpreadsheet _
                    acImport, _
                    acSpreadsheetTypeExcel12Xml, _
                    wsList(i), _
                    xlFile, _
                    True, _
                    wsList(i) & "$"
                On Error GoTo 0
            Next
            .SetWarnings True
        End With
    End Sub
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Tutor jo15765's Avatar
    Joined
    Oct 2011
    Posts
    281
    Location
    @Mancubus ty for that!

    What would I need to do if I wanted to import into a already created table and the spreadsheet I am importing headers do not match table headers (i.e. I need to provide field mappings)?
    Go to Heaven for the climate, Hell for the company.
    ~~Mark Twain

  4. #4
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    welcome.

    same. when you run the code by changing the worksheet name it will append the rows from new sheet to existing table.

    just make sure that the new worksheet has the same field (column) structure.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  5. #5
    Deleted by Moderator.
    Reason: No help except an outside link to a non member commercial site
    Last edited by SamT; 06-23-2017 at 06:43 AM.

Posting Permissions

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