Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    5

    link excel form to access table

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

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Can you elaborate on what you are trying to do?

  3. #3
    Join Date
    Aug 2005
    Posts
    5
    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.

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Quote Originally Posted by data123
    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:

    Code:
    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

  5. #5
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    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.

  6. #6
    Join Date
    Aug 2005
    Posts
    5
    thanks. I'll let you know how it works.

  7. #7
    Join Date
    Jun 2006
    Posts
    3
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Call Excel Data into Access table
    By jiguvaidya in forum Import/Export Data
    Replies: 0
    Last Post: 09-15-2008, 04:58 AM
  2. Link ComboBox to field in a table
    By DrDebate in forum Forms
    Replies: 0
    Last Post: 04-27-2007, 08:03 AM
  3. Link table using MAC PC file sharing problem
    By TaiYipStreet in forum Access
    Replies: 0
    Last Post: 07-19-2006, 08:02 AM
  4. Import/Link Pictures into a table
    By shm138 in forum Import/Export Data
    Replies: 3
    Last Post: 03-17-2006, 08:12 AM
  5. How do link a web form to a access db ?
    By Al in forum Programming
    Replies: 0
    Last Post: 03-16-2006, 06:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums