Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: importing excell files in access

    Hi. I need to be able to import excel worksheets in access on a regular basis (about 60 a day), and I wonder if it could be utomated in some way through VB.

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Automate import Excel files into Access

    Quote Originally Posted by BackStage
    Hi. I need to be able to import excel worksheets in access on a regular basis (about 60 a day), and I wonder if it could be utomated in some way through VB.
    You can automate the process in VBA. You can use the FileDialog object to select the folder where the Excel spreadsheets are located, then the FileSearch object to search through the folder for all Excel files to be imported and then use the DoCmd.TransferSpreadsheet command to import the Excel file into Access. You could use code such as this:

    Private Sub cmdImportFiles_Click()
    ' Convert the Excel files to a text file so that they can then be imported
    Dim intFileCount As Integer

    On Error GoTo ErrorHere

    With Application.FileSearch
    .NewSearch
    .LookIn = "Folder of Excel files path name" ' You could use the FileDialog object to let the user select the folder with the Excel spreadsheets in them orjust hard code it.
    .SearchSubFolders = False ' set to true if you want subfolders to be searched
    .FileName = "*.xls"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute Then
    For intFileCount = 1 To .FoundFiles.COUNT
    ' You might need to change some of these options depending on your version of Excel, range names, field names etc
    DoCmd.TransferSpreadsheet transferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="MyTable", FileName:=.FoundFiles(intFileCount), HasFieldNames:=True
    Next intFileCount
    Else
    MsgBox "No Excel files were found", vbInformation, "No Excel Files Found"
    GoTo ExitHere
    End If
    End With
    ExitHere:
    Exit Sub
    ErrorHere:
    MsgBox "Error In: Form '" & Me.Name & "'" & vbCrLf _
    & "Procedure: cmdImportFiles_Click" & _
    vbCrLf & "Error Code: " & Err.Number & _
    vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
    Resume ExitHere
    End Sub

    I hope this helps
    Justin

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    Thanx mate, really helped me a lot.

Posting Permissions

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