Importing Excel files monthly when path changes

Hi Experts,

My deparment relies on exporting certain information from our database into an Excel file. This information is "randomly" incorrect, but we were able to manage it because we only had a few rows to check on a monthly basis. The rules are changing, and we are going to need to start exporting historical information starting in the begining of July, which will push these Excel files up to the tune of hundreds of rows of data to check.

Since we can not rely on our current database to provide us consistant correct historical information on a month-by-month basis, I would like to set up an access database that will import those Excel files (both prior and current month) and compare the tables for variances.

I can handle the queries, but my problem is that the "prior and current month" excel files change location each month. Ideally, I would like for the analysts to open the database and click a button that says "import files" and have the program prompt them to locate both the prior and current month's files manually.

Who is Participating?
Yeah, that's not finding the proper reference to the object we are trying to use (FileDialog)
What version of Access are you using?  Regardless, you can use the MS Office Object Library to open a "File Open" window fairly easily.  Then you would use an File Import specification to import the data from the spreadsheet into a table.  You can create this import specification by doing an import manually and then saving the details of the import in an Import specification file.  You would then use the TransferSpreadSheet method with the Import Spec name as the 'filter' for the import.  There is quite a bit of VBA code involved.  Are you proficient with VBA?
elmo14Author Commented:
I'm using Access 2000. I'm "proficient with VBA" from the perspective of being able to understand what code is doing and "tweak" it slightly... but I'm not good enough to write complicated code from scratch just yet.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Here's a working example of how to get a fie path with a function called GetFileName().  After the "how to use it code", I'll include the code that needs to be placed in a public code module so you'll have to create a module if you don't already have one to put the code in.  

This code gets a graphics file. You can adjust the GetFileName() function to filter file types.  Get this stuff going and we'll get to the TransferSpreadSheet method. You might want to look it up in Access Help to get an idea of how it works.  You MUST have the MS Office Object Library reference turned on (see Tools | References in a code window and click on the library from the list)

This is in the On Click event of a command button
On Error GoTo Err_cmdOpen_SideHead_Click
Dim PhotoPath As String

PhotoPath = GetFileName()

If PhotoPath <> "" Then
    Me.txtPhotoPath_SideHead = PhotoPath
End If

   Exit Sub

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOpen_SideHead_Click of VBA Document Form_frmAnimals"
    Resume Exit_cmdOpen_SideHead_Click

This code goes in Publice Module.  GetDefault(3) is a function I created to retrieve a certain user default from a table.  You will need to replace that with some that make sense for you.
Public Function GetFileName() As String
On Error GoTo Err_GetFileName

'Declare a variable as a FileDialog object.
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant

    With fd
        'Set the initial view to the details view.
        .InitialView = msoFileDialogViewDetails
        .Filters.Add "Images", "*.jpg; *.gif; *.jpeg, *.bmp", 1
        .InitialFileName = CStr(GetDefault(3))
        .Title = "Select a Picture"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewThumbnail

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'If the user presses the action button...

        If .Show = -1 Then   ' -1 = True
            For Each vrtSelectedItem In .SelectedItems
                GetFileName = vrtSelectedItem
            Next vrtSelectedItem
            Exit Function
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

   Exit Function

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetFileName of Module mdlMain"
    Resume Exit_GetFileName

End Function
elmo14Author Commented:
OK... two questions.
1. The first block of code goes where? If I use the "codebuilder" and put the code into the area Access wants me to put it with the private sub starter, I'm getting an error on the "Private Sub Command5_Click()" error when I'm running the code of "Method or Data member not found."

2. Can you clarify what GetDefault(3) is doing so I know logically what it should be replaced with?
The first block of code is an example of how to get a filename.  You should set up a command button on the form where you want the user to look up a file.  My code is in the On Click event of a button called cmdOpen_SideHead.  So just create a button and place that code in the On Click event between Private Sub statement and the End Sub statement.  Then change all instances of the name cmdOpen_SideHead with the name of your command button.

On Error GoTo Err_cmdYourButton_Click    '<----replace here
Dim ExcelPath As String

ExcelPath = GetFileName()

If ExcelPath <> "" Then

  ' This means that the user didn't select a  file so you should place some code in here to decide what to do if that happens
' Exit sub

End If

Exit_cmdYourButton_Click:   '<----replace here
   Exit Sub

Err_cmdYourButton_Click:   '<----replace here
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdYourButton_Click of VBA Document Form_frmAnimals"  '<----replace here
    Resume Exit_cmdYourButton_Click   '<----replace here

GetDefault(3) is retrieving a text value that describes the Default File Path.  You can hardcode that if you like:

.InitialFileName = "G:\Shared\Excel Spreadsheets\"       'or whatever your filepath is.
elmo14Author Commented:
Looks good so far - that cleared up my issues with the command button. Now I'm getting errors on the second part of the code.

Public Function GetFileName() As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)

Getting the same "Method or Data member not found" error.

I have the MS Access 9.0 object library & MS Office 10.0 object library installed. I'm seeing some people mentioning an 11.0 object library, but it sounds like that is only for Access 2003.

That's dependent on what version of MS Office you have. Office 2003 is 11.0.  Did you put the GetFileName() function in a public module?  If so, go to the module and go to the Debug | Compile menu item in the code window. and see if you get an error.
elmo14Author Commented:
Yeah... I am getting an error on this line:

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Specifically the .FileDialog
That's an indication of a missing reference.  What other references do you have turned on?  I may be wrong about which library needs to be referenced. Here's what I have:  Try adding each of the these one at a time until the complie works.  I don't think OLE Automation or ADO 2.5 will have an effect but the others might.

Visual Basic for Applications
MS Access 11.0 Object Library
OLE Automation
MS ActiveX Data Objects 2.5
MS Office 11.0 Object library

For Access 2000, you should probably have version 9.0 of the Object Library.  I'm wondering if the feature isn't available for Access 2000.  I have other code to do the same thing but it's huge and uses the Windows common dialogue controls.  It's very complex and difficult to work with but it does work.

Incidently, Access 2000 was one of the worst releases of Access ever.  Make sure and do your Office Updates because Access 2000 will not work properly without them. Better yet, if you are just getting started on this project, I would try to upgrade to Access 2003.  It's vastly better and more stable with a lot of important features.  Have you investigated the Transfer SpreadSheet Method?  Also, once the file picker is working then you'll need to do a manual import to create your import specs file to use in the method.

elmo14Author Commented:
Arji - totally appreciate your help. I think it is an Access 2000 vs Access 2003 issue. Part of the problem exists in that our IT group is on the "brink" of upgrading the entire company to XP & MS Office 2003. As it stands right now, half of my group is using windows 2000 with Access 2000 and half is already upgraded (I have a world of issues with this, mind you). Since there are over 30 of us, and I would need for everyone to use the database... it seems that building it in Access 2003 would not work all of the time - and this is not the first time I've heard someone say "Use Access 2003 if you can." Getting a committment for global upgrade is not going to happen at this juncture... so I think I'm going to need to wait a few months before moving forward on this project.

Honestly, you have helped me learn a lot about VBA and Access just by talking with me. I appreciate that greatly. Can I bug you again once I have my group upgraded?

P.S. I have no problem with the Transfer Spreadsheet method (I wish I could write VBA that easily in Excel!!!).

Keep in mind that an Access 2000 database will run on Access 2003 without conversion but will be read-only (no design changes allowed) but it should work.  For an Access 2003 database you would have to convert it to an Access 2000 file to use it in Access 2000 (See Tools | Database Utilities | Convert Database).  Obviously, the solution is to get everyone on the same page.  You might also try an experiment on someone's Access 2003 system to see if the code works ok.  I've never used the openfile dialogue in previous versions so I can't tell you if it should work with Access 2000.  I'm surprised it doesn't.  Use the Debug | Complie before you run any new code so that you can if there is an inherent problem.  It sometimes catches typos.

Curious, what error did you get when compile stopped at:

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Same error as before?
elmo14Author Commented:
Yes... "Compile Error: Method or data member not found"
elmo14Author Commented:
OK. Let me put this on hold for a month and get a confirmation from my IT group as to when we are going to be upgraded.
Jeffrey CoachmanMIS LiasonCommented:

Here is what I use to do exactly what you asked:
< I would like for the analysts to open the database and click a button that says "import files" and have the program prompt them to locate both the prior and current month's files manually.>

Like Arji's Function, mine too goes in a Public Module:

Function FileToOpen(Optional StartLookIn) As String

 Dim path As String
 Dim FileName As String
 Dim a As String
OFN.lStructSize = Len(OFN)
OFN.lpstrFilter = "My Excel Files (*.xls)" + Chr$(0) + "*.xls" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
OFN.lpstrFile = Space$(254)
OFN.nMaxFile = 255
OFN.lpstrFileTitle = Space$(254)
OFN.nMaxFileTitle = 255

If Not IsMissing(StartLookIn) Then
 OFN.lpstrInitialDir = StartLookIn
' OFN.lpstrInitialDir = "C:\CorelDrw Files"
End If

OFN.lpstrTitle = "Please find and select the Excel File"
OFN.Flags = 0

a = GetOpenFileName(OFN)
If (a) Then
 path = Trim(OFN.lpstrFile)
 FileName = Trim(OFN.lpstrFileTitle)
 If Dir(path) <> "" Then FileToOpen = -1
 FileToOpen = Trim(OFN.lpstrFile)
 FileToOpen = ""
 path = ""
 FileName = ""
End If

FileToOpen = path

End Function

Great, now put the code below on your "Import" button:

Private Sub cmdImportExcel_Click()
On Error GoTo Err_cmdImportExcel_Click

Dim strNewTableName As String
    If IsNull(Me.txtNewTableName) Then
        MsgBox "You must provide a table name", vbInformation
        GoTo Exit_cmdImportExcel_Click
        strNewTableName = txtNewTableName
        DoCmd.TransferSpreadsheet acImport, 8, strNewTableName, FileToOpen, True, ""
        MsgBox "Done!, your new table is called: " & strNewTableName, vbInformation
    End If

    Exit Sub

    If Err.Number = 2391 Then
        MsgBox "The table: " & strNewTableName & ", already exists," _
        & vbCrLf & "and you are trying to import a different Excel file into it"
        Resume Exit_cmdImportExcel_Click
    ElseIf Err.Number = 2522 Then
        MsgBox "No Excel file was selected" _
        & vbCrLf & vbCrLf & "      ...Canceling"
        Resume Exit_cmdImportExcel_Click
        MsgBox "Code could not execute!" _
        & vbCrLf & vbCrLf & "Error " & Err.Number & ": " _
        & vbCrLf & vbCrLf & Error, vbExclamation
        Resume Exit_cmdImportExcel_Click
    End If

End Sub

On my form I have a textbox: "txtNewTableName" that is used to hold the name of the newly created Access Table (the imported excel file). And my button is called: cmdImportExcel

This should be "turnkey"...
Create the form, textbox and button.
Put the Function in a Public Module
Put the button code on the button.

Then when you type in a proposed new table name in the text box, and click the button, a "Browse" dialog box appears that you can use to navigate to the location of the excel file.

Hope this helps as well
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.