Importing Excel files into Access database

[JeanM]

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

[johnhutchison]

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

      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.

[johnhutchison]

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.

[JeanM]

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.

[johnhutchison]

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).

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:\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 = "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.

[JeanM]

Hi John

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

Jean

[Cronk]

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

   strFile = Dir(strFolderPath & "\*.xls")
   Do While strFile <> ""
      strFileFullName = strFolderPath & "\" & strFile
      DoCmd.TransferSpreadsheet .....,strFileFullName,...
      Kill strFileFullName
      strFile = Dir
   Loop

[johnhutchison]

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.

[JeanM]

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:\temp\ANG\" & "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

[RetiredGeek]

Jean,

You can use an InputBox command, e.g.

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:\temp\ANG" & zDirQualifier & _
      "\Student-" &  Format(Date, "mm-dd-yyyy") & _
      "  " & Format(Time, "hh-mm-ss  AM/PM") & ".xls", True
End if

[JeanM]

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

[RetiredGeek]

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.

[johnhutchison]

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.

[jlebron]

happy.gif

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.

[kevinyoukilis264]

smiler.gif

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.