Link to Excel Spreadsheets from Access

  • Here is some code that you can use to link all the Excel files in a directory to your Access Database.

    The code assumes you have headers in Row 1 of your spreadsheets

    [vba]Option Compare Database
    Option Explicit

    Sub Link_To_Excel()
    'WillR - (December 2004)
    'Macro Loops through the specified directory (strPath)
    'and links ALL Excel files as linked tables in the Access

    Const strPath As String = "C:\Will\Link Files\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number

    'Loop through the folder & build file list
    strFile = Dir(strPath & "*.xls")
    While strFile <> ""
    'add files to the list
    intFile = intFile + 1
    ReDim Preserve strFileList(1 To intFile)
    strFileList(intFile) = strFile
    strFile = Dir()
    'see if any files were found
    If intFile = 0 Then
    MsgBox "No files found"
    Exit Sub
    End If
    'cycle through the list of files & link to Access
    For intFile = 1 To UBound(strFileList)
    DoCmd.TransferSpreadsheet acLink, , _
    strFileList(intFile), strPath & strFileList(intFile), True, "A1:J50"
    'Check out the TransferSpreadsheet options in the Access
    'Visual Basic Help file for a full description & list of
    'optional settings
    MsgBox UBound(strFileList) & " Files were Linked"
    End Sub

    Hope it helps