VBA Import Code to Access Help
Results 1 to 1 of 1

Thread: VBA Import Code to Access Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default VBA Import Code to Access Help

    Hi Everyone,

    I am creating an import code for Access from Excel that takes every excel file in a folder, imports the designated Sheet (i.e only Sheet1) and within a designated area of space in the sheet(i.e Sheet1!A32:T1881) and import it to an access called Table called "Test1". This I have working.

    In every Workbook I have a seperate sheet (i.e Sheet2) that only contains text name (I.e Company1) in Cell A1.

    I am trying to get every line of excel that gets imported into the Access table to have a separate field "Company Name" for each record in the Import.

    Private Sub Command0_Click()

    Dim strFile As String
    Dim strPath As String

    DoCmd.SetWarnings False

    ' Set file directory for files to be imported
    strPath = "X:\Trans\DISPATCH\Business Analysis - Dispatch\Liam\Files"
    ' Tell it to import all Excel files from the file directory
    strFile = Dir(strPath & "*.xlsx*")

    ' Start loop
    Do While strFile <> ""
    ' Import file
    DoCmd.TransferSpreadsheet transfertype:=acImport, TableName:="Test1", FileName:=strPath & strFile, HasFieldNames:=True, Range:="Sheet1!A32:T1881"
    ' Loop to next file in directoryI
    strFile = Dir

    MsgBox "All data has been imported.", vbOKOnly

    An example could be like if Sheet2 said Company1 then the import would look exactly like Sheet1 with just a column that for every row that was pulled from Sheet1 has an identifier that says Company1 in a separate column. If that makes sense. I'm just trying to identify which company each import belongs to.
    Last edited by Joe4; Aug 20th, 2019 at 12:36 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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