link excel form to access table

[data123]

How do I link a excel form to an access table.

[matthewspatrick]

Can you elaborate on what you are trying to do?

[data123]

I'm trying to take a excel spreadsheet and have it automatically append to an access database table. I recieve several spreadsheet in from about 100 suppliers weekly. They contain information that need to be loaded into an access database. Is there away that I can auto append these into a table instead of having to export the spreadsheet one by one. The spreadsheet have the same name. I'm currently using a transfer spreadsheet marco that loads the spreadsheets, it only takes about 1 min for it to run per spreadsheet. But I have sometimes about 20 to 30 a day to do this for.

[matthewspatrick]

The spreadsheet have the same name. I'm currently using a transfer spreadsheet marco that loads the spreadsheets, it only takes about 1 min for it to run per spreadsheet. But I have sometimes about 20 to 30 a day to do this for.

If the workbooks are identical in structure, then what I would do is this:

1) As the workbooks come in each week (presumably by email), save each workbook (modifying the name so as to make it unique to the folder) into a particular folder

2) Use a VBA sub that will loop through that folder, and iteratively run DoCmd.TransferSpreadsheet to import the data from those workbooks

Here is a starting point for you, based on something I did in the recent past:

Option Compare Database
Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
    "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
    "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
  
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long

End Type

Sub ImportFromTrackers()
    
    ' sub will prompt user for a directory, then grab tracker data from all Excel
    ' workbooks in that directory (but not subdirectories!)
    
    Dim GetDir As String
    Dim fso As Object
    Dim Fld As Object
    Dim Fil As Object
    
    GetDir = GetDirectory("Choose folder with tracking workbooks")
    If GetDir = "" Then 'did not choose folder
        MsgBox "You did not choose a directory", vbCritical, "Aborting"
        Exit Sub
    End If
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Fld = fso.GetFolder(GetDir)
    
    For Each Fil In Fld.Files
        If UCase(Right(Fil.Name, 4)) = ".XLS" Then
            'put your TransferSpreadsheet statement here
        End If
    Next Fil

    MsgBox "Done

End Sub

Function GetDirectory(Optional Msg) As String
    
' Msg arguments allows for customized message in browser tool

    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long
    Dim x As Long
    Dim pos As Integer

' Root folder = Desktop
    bInfo.pidlRoot = 0&

' Title in the dialog
    If IsMissing(Msg) Then bInfo.lpszTitle = "Select a folder." Else bInfo.lpszTitle = Msg

' Type of directory to return
    bInfo.ulFlags = &H1

' Display the dialog
    x = SHBrowseForFolder(bInfo)

' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If

End Function

Patrick

[matthewspatrick]

BTW, that code is meant to go in a "regular" VBA module, but can be called from a Form module (e.g. from a commandbutton's Click event) or, as I did in my own project, assigned to the OnAction property of a CommandBarButton.

[data123]

thanks. I'll let you know how it works.

[n]

Hi,
Could i by chance get that code for the macro you created, i dont have as many spread sheets as you and that would really assist me. also, could you walk me step by step as to what your talking about pat, i'm really new at access and need to accomplish a goal similar to data, but i need to update specific fields within my tables