Importing Excel files into Access database

Topic

New Reply

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.

Reply | Quote

Replies

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:ExcelArchives" & 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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You many need to change

And also perhaps add an x to the end of this.

zXLFPath = “G:ExcelArchives” & 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

Reply | Quote

John,

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

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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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:BEKDocsExcelArchives")
        
   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:pathfilename.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.
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

I agree that use a File Picker is a good idea. This is the sort of form I use for importing from Excel.

30409-ImportForm

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.

Reply | Quote

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

Reply | Quote

Jean,

Did you change the “BridesDB” to the name of your Access table to add the data to? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

HI

I did change BridesDB to a table name.

Jean

Reply | Quote

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:

     [COLOR=#ff0000]PickFileDialog[/COLOR] = .SelectedItems(1)    


     [COLOR=#ff0000]PickFileDialog[/COLOR] = “EXIT”       

instead of     [FONT font=monospace][/FONT]cmdFileDialog  in both cases

And this line needed to be changed, to a folder I have.
zXLFPath = PickFileDialog(“G:BEKDocsExcelArchives”)

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?

Reply | Quote

Hi John

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

Jean

Reply | Quote

Hi John

I have come out of vacation mode and been testing further selecting multiple excel files with dialog box. It works great if I select one file at a time. I want to beable to select several files. I did change this line of code that allows me to select several files:
.AllowMultiSelect = True

When I open the table it shows only one file, which is the first file in the list.

I also get an error message when I choose CANCEL in the dialog box.

Jean

Reply | Quote

30609-happy

I would suggest trying to create a Macro to import the Excel spreadsheet. The macro editor will help you with the parameters for importing. Then it is much easier to figure out DoCmd.TransferSpreadsheet.

Another thought that I had is, you don’t need to import the data. I typically link to a file, then use a couple of queries to append the data to existing tables. Or you can create a Make Table query to create the data.

If you are going to do it regularly (weekly) then I would suggest linking so you don’t have to add the data twice to your database.

Reply | Quote

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:temptxc”)

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:pathfilename.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

Reply | Quote

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

Reply | Quote

The code tries to deal with this by having the FileDialog return Exit if you click Cancel.

Code:

      If .Show = True Then       
              cmdFileDialog = .SelectedItems(1)       
      Else        
               cmdFileDialog = “EXIT”      
      End If

But the subsequent code seems to add a path and an extension to that and treat it as a filename, which it can’t then find.

Try replacing:

If Trim(UCase(zXLFName)) = “EXIT” Then Exit Sub

with

If Dir(zXLFPath) “” Then

then add an End IF after the DoCmd.TransferSpreadsheet line.

Reply | Quote

You are right that changing to AllowMultiselect=true allows you to select multiple files, but

PickFileDialog = .SelectedItems(1) takes the first of the selected files and returns it.

To work with multiple files would require the function code to be rewritten to return an array of files, which would then need to be imported one at a time using a loop.

Reply | Quote

Hi John

Cancel works correctly now. As for multiple files and rewriting code will the outcome be any different than what it is now?

Jean
Thank you for hanging in there with me.

Reply | Quote

As for multiple files and rewriting code will the outcome be any different than what it is now?

If properly rewritten it could handle multiple files and import them.

Here is a version that works (I think).

Code:

Private Sub cmdImportexcel_Click()
    Dim i As Integer
    Dim zXLFPath As String
    Dim zXLFName As String
    Dim iFileType As Integer
    ‘Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    ‘Note: Returns a fully qualified filename, e.g. d:pathfilename.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 = “D:”

        ‘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
            For i = 1 To .SelectedItems.Count
                zXLFPath = .SelectedItems(i)
                Debug.Print zXLFPath
                If UCase(Right(zXLFPath, 1)) = “X” Then
                    iFileType = acSpreadsheetTypeExcel12Xml
                Else
                    iFileType = acSpreadsheetTypeExcel12
                End If

                DoCmd.TransferSpreadsheet acImport, iFileType, “BridesDB”, zXLFPath, True
            Next i
        End If
    End With

End Sub

This code brings the FileDialog (that was previously a separate function ) back into the sub. Not that there was anything wrng with using a separate function, but I found the multi file easier to handle this way.

Reply | Quote

Hi John

That works so much better than clicking the button each time for a file. Thank you again.

Jean

Reply | Quote

And you could loop through all the files in the folder using Dir to process them without prompting for file names

Code:

   strFile = Dir(strFolderPath & “*.xls”)
   Do While strFile  “”
      strFileFullName = strFolderPath & “” & strFile
      DoCmd.TransferSpreadsheet …..,strFileFullName,…
      Kill strFileFullName
      strFile = Dir
   Loop

Reply | Quote

Kill strFileFullName

I usually rename the file and/or move it to another folder rather than just delete it. But I agree that just leaving the file there is an invitation to import it again.

Reply | Quote

Hi Again

I have the following code working perfect but I have been researching and reading about letting the user input part of the filename instead of hard coding it.
This is want I have:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, “Temp Student Table Export1”, “C:tempANG” & “Student-” & Format(Date, “mm-dd-yyyy”) & ” ” & Format(Time, “hh-mm-ss AM/PM”) & “.xls”, True

Is there a way for the user before “Student-” to input something like ANGB01? or is there a way for a query that is executing before the transferspreadsheet that has a parameter asking for the same variable, meaning ANGB01 to automatically be part of the filename? I hope I am being clear!

Jean

Reply | Quote

is there a way for a query that is executing before the transferspreadsheet that has a parameter asking for the same variable, meaning ANGB01 to automatically be part of the filename? I hope I am being clear!

If the query parameter came from a form rather than a query prompt then you could include it in the file name automatically, rather than having to use an Input box to ask for it again.

Reply | Quote

Jean,

You can use an InputBox command, e.g.

Code:

Dim zDirQualifier as String

zDirQualifier = InputBox("Enter the Directory Qualifier:", vbOkCancel, _
                                     "User Entry Required:")

If zDirQualifier = vbCancel Then
  'Place code here to handle situation if user clicks the Cancel button
Else
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      "Temp  Student Table Export1", "C:tempANG" & zDirQualifier & _
      "Student-" &  Format(Date, "mm-dd-yyyy") & _
      "  " & Format(Time, "hh-mm-ss  AM/PM") & ".xls", True
End if

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi

That works great. I did take out the backslash in front of Student and I got the results I wanted. The input box pops-up and the user can enter ANGB01 and it becomes part of the filename. The result is ANGB01Student and so on. It was so easy. Thank you very much.

Jean

Reply | Quote

Jean,

Sorry, from your post #24 it looked like ANG was a directory not part of the file name. Gald it’s working for you. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

31306-smiler

I have written VBA code to read Excel data in cells into MS Access.

But, as part of the requirement, I have to do something more.

In the Excel Application:
Insert Tab | Text Group | Click on Object | Select Create From File tab => We can Insert external object, right!

I have to read Objects also attached to Excel file (like MS Word, Text File, PDF files). I am not understanding how to do this.

Can anybody please help me!
Thanks.

Reply | Quote

Hi all,

I just stumbled across this thread whilst looking for a way to import multiple worksheets from an excel workbook.
Sorry to hijack it a little but would it be possible to amend the code to import each individual worksheet within a workbook into a separate table?

i.e. i have a .xlsx workbook around 40mb which contains 11 worksheets with around 100,000 rows of data (12 columns of data),
preferably each imported into a separate table tbl_01, tbl_02, tbl_03 etc .

I have found the following which works with a limited amount of data, however it hangs when more then 10,000 rows of data.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpWktFilesSepTbls

Sorry but its been a while since i did anything in access.

Thank you for any replies

Mark.

Reply | Quote

31306-smiler

I have written VBA code to read Excel data in cells into MS Access.

But, as part of the requirement, I have to do something more.

In the Excel Application:
Insert Tab | Text Group | Click on Object | Select Create From File tab => We can Insert external object, right!

I have to read Objects also attached to Excel file (like MS Word, Text File, PDF files). I am not understanding how to do this.

Can anybody please help me!
Thanks.

Unfortunately your request is well beyond the scope of something we can answer in this forum. To do so will require knowledge of the object model for each of the different kinds of objects involved. In addition it will require extensive knowledge of Automation of those objects from Access. And embedding objects of those types within an Access table is a non-trivial subject as well. Depending on your time constraints, it will either require extensive study on your part before you begin to undertake such a project, or you will need to hire an expert in the field to develop a solution for you.

Reply | Quote

I believe that the VBA code in post #20 of this thread should be able to be used – you will probably need a separate TransferSpreadsheet statement for each worksheet within the Excel workbook however. If you are not comfortable with VBA in Access, you may want to use the suggestion in post #29 of this thread and use macros instead. Note that the version of Access you are using may impose some limits – and the version of the Excel workbook also will play a factor.

Reply | Quote

31422-blabla

I would suggest trying to create a Macro to import the Excel spreadsheet. The macro editor will help you with the parameters for importing. Then it is much easier to figure out DoCmd.TransferSpreadsheet.

Another thought that I had is, you don’t need to import the data. I typically link to a file, then use a couple of queries to append the data to existing tables. Or you can create a Make Table query to create the data.

If you are going to do it regularly (weekly) then I would suggest linking so you don’t have to add the data twice to your database.

Reply | Quote

Good morning, I’m glad I’ve found this forum and thread because I need to help with my mdb file. Im usind MS Acces 2010.

I had DB with execl files connected, I’ve just linked excel files into my mdb file. But its not enough now. I need to share mdb file for more users, so I need to be openable for them and to be searchable with some SQL queries.
I found on internet, that I should to add each one sheet from each xls file to my mdb as table by the procedure DoCmd.TransferSpreadsheet acImport.

I have 5 xls files,(eg called Ab1,Ab2,Ab3,Ab4,Ab5) from which should be transferred one specific sheet “List of content” to my mdb file, tables should have same name as excel files (Ab1,Ab2…..) and I dont need to be asked each time to specify files (Id like to specify them in vba file).
And also Id like to be this precedure done automatically when opening mdb file.

Can you please help me? I’m not programmer (very begginer), so any help will be appreciated.

Reply | Quote

Hi

I used the following code with a button in Access 2010. I allow the user to choose the excel files they want. This might be a start in helping you setup this procedure automatically. The code toward the end is specific to a delete query, an append query, and a table in my access database. Hope this helps.

Private Sub Command24_Click()
Dim i As Integer
Dim zXLFPath As String
Dim zXLFName As String
Dim iFileType As Integer
‘Dim fDialog As String

‘Requires reference to Microsoft Office 14.0 Object Library Office 2010.
‘Note: Returns a fully qualified filename, e.g. d:pathfilename.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 = “S:your directory”

‘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
For i = 1 To .SelectedItems.Count
zXLFPath = .SelectedItems(i)
Debug.Print zXLFPath
If UCase(Right(zXLFPath, 1)) = “X” Then
iFileType = acSpreadsheetTypeExcel12Xml
Else
iFileType = acSpreadsheetTypeExcel12
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery “Import Course Excel Table Delete”
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, iFileType, “Import Course Excel Table”, zXLFPath, True
DoCmd.SetWarnings False
DoCmd.OpenQuery “Append Course Excel Data”
DoCmd.SetWarnings True
Next i
End If
End With

End Sub

Reply | Quote

Hi Jean,
thank you for your quick reply.
I tried to use this code, but I’ve got runtime error 7874 on line ” DoCmd.OpenQuery “Import Course Excel Table Delete”” and also “DoCmd.OpenQuery “Append Course Excel Data””. So Im little bit confused.

Reply | Quote