Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Importing Excel files into Access database

    I have two excel files I need to append to an access database. These two files will be pulled from a folder on a continual basis. Once I append these two files I plan on deleting them from the folder. Then the process starts again where the folder will buildup with new files that I will need to append to the database once again.
    Example: Student-TXCB01 needs to be appended to Student_Table_Import. Course-TXCB01 needs to be appended to Course_Table_Import. Student and Course will always be part of the filename. What appears after the hypen will change.
    I want to build a button with code to do this process automatically.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jean,

    Here's one way to do what you want. I hope this works for you.

    Code:
    Sub ImportExcelData()
    
       Dim zXLFPath  As String
       Dim zXLFName  As String
       
       Do
        zXLFName = InputBox("Enter the Excel Filename ONLY!" & vbCrLf & _
                            "DO NOT enter the path or extension", "Excel File Name Entry")
            
       Loop Until Trim(zXLFName) <> ""
       
       If Trim(UCase(zXLFName)) = "EXIT" Then Exit Sub
       
       zXLFPath = "G:\Excel\Archives\" & zXLFName & ".xls"
              
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Student_Table_Import", zXLFPath, True
             
    End Sub
    Notes:
    You'll need to change the zXLFPath directory path to point to the directory holding your files.
    You can add variables to prompt for the table to be used if you want to use the same code for both imports just follow the example used for prompting for the file name.
    You many need to change this constant {acSpreadsheetTypeExcel12} if you are using a version of excel earlier than 2010.

    Post back if you have questions.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You many need to change
    And also perhaps add an x to the end of this.

    zXLFPath = "G:\Excel\Archives\" & zXLFName & ".xls"

    and also check that the file exists:

    If dir(zXLFPath) <> "" then

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Student_Table_Import", zXLFPath, True
    else
    msgbox "The file cannot be found."
    end if
    Last edited by johnhutchison; 2012-03-17 at 19:55.
    Regards
    John



  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Good points both! Thanks for cleaning up my code once again.

    FYI: If you add the x to .xls to get .xlsx for 2007 & 2010 Excel you also need to change the acSpreadsheetTypeExcel12 to acSpreadsheetTypeExcel12xml as appropriate for your version.
    Last edited by RetiredGeek; 2012-03-17 at 20:04.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Importing Multiple Excel Files into Access

    Hi

    Instead of a input box and typing in a filename can I get a browse folder and then pickup the file I want to import? Typing in the filename each time is too time consuming.

    Jean

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jean,

    Give this a try. It also addresses some of John's points as it allows selecting .xls or .xlsx files and auto adjusts the file type in the Transfer command and also only allows you to pick existing files.
    Code:
    Sub ImportXLS()
    
       Dim zXLFPath  As String
       Dim zXLFName  As String
       Dim iFileType As Integer
       
       zXLFPath = PickFileDialog("G:\BEKDocs\Excel\Archives")
            
       If Trim(UCase(zXLFName)) = "EXIT" Then Exit Sub
       
       If UCase(Right(zXLFPath, 1)) = "X" Then
         iFileType = acSpreadsheetTypeExcel12Xml
       Else
         iFileType = acSpreadsheetTypeExcel12
       End If
                 
       DoCmd.TransferSpreadsheet acImport, iFileType, "BridesDB", zXLFPath, True
             
    End Sub      'ImportXLS
    
    Private Function PickFileDialog(zTargetDir As String) As String
    
    'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext
    
       Dim fDialog As Office.FileDialog
       Dim varFile As Variant
       Dim zCurDir As String
       
       'Set up the File Dialog.
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
       With fDialog
          'Allow user to make multiple selections in dialog box
          .AllowMultiSelect = False
          .Title = "Please select the file to import"
    
          'Clear out the current filters, and add your own.
          .Filters.Clear
          .Filters.Add "Excel 2003", "*.xls"
          .Filters.Add "Access 2007-10", "*.xlsx"
          
          'Set the initial directory using passed argument string
          .InitialFileName = zTargetDir
          
          'Show the dialog box. If the .Show method returns True, the
          'user picked a file. If the .Show method returns
          'False, the user clicked Cancel.
          If .Show = True Then
           cmdFileDialog = .SelectedItems(1)
          Else
           cmdFileDialog = "EXIT"
          End If
       End With
       
    End Function        'PickFileDialog
    Of course, remember to change my testing parameters in the code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I agree that use a File Picker is a good idea. This is the sort of form I use for importing from Excel.

    ImportForm.gif

    I separate the file selection from the import using a Browse button, and have a Check Data in Excel button so the user can double check the file before importing.

    I find importing from Excel much more problematic than you would expect. The column headings need to be just right, and people seem to always mess with the templates you give them etc.
    Regards
    John



  8. #8
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi

    I never got this part of the code to work:
    DoCmd.TransferSpreadsheet acImport, iFileType, "BridesDB", zXLFPath, True
    It bombs on this line of code. Please help?

    Jean

  9. #9
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi John

    I never got the code to work correctly. It bombs on this line of code:
    DoCmd.TransferSpreadsheet acImport, iFileType, "BridesDB", zXLFPath, True
    I think I replied to RetiredGeek but I am not sure? I was wondering if the code could also append directly to a table in my database when it gets imported? Hope I am clear!--I have been on vacation and just got back to this issue.

    Jean

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jean,

    Did you change the "BridesDB" to the name of your Access table to add the data to?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    HI

    I did change BridesDB to a table name.

    Jean

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I just tried this using RG's code with the FileDialog and I needed to make a change to the PickFileDialog function as below:

    Code:
         PickFileDialog = .SelectedItems(1)    
    
    
         PickFileDialog = "EXIT"       
    
    instead of     cmdFileDialog  in both cases
    And this line needed to be changed, to a folder I have.
    zXLFPath = PickFileDialog("G:\BEKDocs\Excel\Archives")

    This line worked as is, because if the table does not exist it just creates it.
    DoCmd.TransferSpreadsheet acImport, iFileType, "BridesDB", zXLFPath, True


    When you say it "bombs" can you tell us more about what happens? Do you get any errors or messages?
    Regards
    John



  13. #13
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi

    The error message is: This action or method requires a File name argument.
    This is what I have:
    Private Sub Command0_Click()
    Dim zXLFPath As String
    Dim zXLFName As String
    Dim iFileType As Integer

    zXLFPath = PickFileDialog("c:\temp\txc\")

    If Trim(UCase(zXLFName)) = "EXIT" Then Exit Sub

    If UCase(Right(zXLFPath, 1)) = "X" Then
    iFileType = acSpreadsheetTypeExcel12Xml
    Else
    iFileType = acSpreadsheetTypeExcel12
    End If

    DoCmd.TransferSpreadsheet acImport, iFileType, "Students", zXLFPath, True
    End Sub 'ImportXLS


    Private Function PickFileDialog(zTargetDir As String) As String

    'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext

    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim zCurDir As String

    'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
    'Allow user to make multiple selections in dialog box
    .AllowMultiSelect = True
    .Title = "Please select the file to import"

    'Clear out the current filters, and add your own.
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls"
    .Filters.Add "Access 2007-10", "*.xlsx"

    'Set the initial directory using passed argument string
    .InitialFileName = zTargetDir

    'Show the dialog box. If the .Show method returns True, the
    'user picked a file. If the .Show method returns
    'False, the user clicked Cancel.
    If .Show = True Then
    cmdFileDialog = .SelectedItems(1)
    Else
    cmdFileDialog = "EXIT"
    End If
    End With

    End Function

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Do you even see the File Dialog?
    Does it help if you make the change I showed in red?
    Regards
    John



  15. #15
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi John

    I got it working--I changed what you suggested and it works perfect--thank you for the help.

    Jean

Page 1 of 3 123 LastLast

Posting Permissions

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