Importing Excel files into Access database

[JeanM]

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.

[RetiredGeek]

Jean,

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

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.

[johnhutchison]

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

[RetiredGeek]

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.

[JeanM]

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

[RetiredGeek]

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.

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.

[johnhutchison]

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.

[JeanM]

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

[JeanM]

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

[RetiredGeek]

Jean,

Did you change the "BridesDB" to the name of your Access table to add the data to?

[JeanM]

HI

I did change BridesDB to a table name.

Jean

[johnhutchison]

I just tried this using RG's code with the FileDialog and I needed to make a change to the PickFileDialog function as below:

     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?

[JeanM]

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

[johnhutchison]

Do you even see the File Dialog?
Does it help if you make the change I showed in red?

[JeanM]

Hi John

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

Jean