Browse for excel files and append to Access table.

[newbieX]

I have an Access 2007 database called BusRoutes with two tables in it called Routes and Stops. I also have several excel spreadsheets that are based on an excel template that are populated by the drivers. These spreadsheets should always have the same format but not the same file names. I am trying to create a button that will perform the following actions:


Allow the user to navigate to the excel spreadsheet, select the “Bus Routes” tab, and append all the data to the “Routes” Access table.


Navigate to the same excel spreadsheet and select the “Bus Stop” tab and append all the data to the Access “Stops” table.


The excel spreadsheets could be stored anywhere. I have no control over where on the driver’s computer he or she stores the excel spreadsheets.


The spreadsheet names will differ as their naming conventions’ are based on the route number and school year they are driven. The template is macro enable so will always end .xlsm.


The excel spreadsheets header names exactly match the corresponding table field names they are getting imported into. Tab names and header info are not alterable in the spreadsheet.


Oh and "e-mail_add" is a hyperlink is that makes any difference.


I know that I have to use TransferSpreadsheets Action to append the data but I am at a loss as to how to navigate to the spreadsheet, select the individual tabs, and pass this variable to the TransferSpreadsheets action.


I tried this as an attempt to at least try to return a file name value but bombed out at the second line with following error: Run time error-2147467259(80004005)': Method 'FileDialog' of aobject'_Application' failed.

Set dlg = Application.FileDialog(msoFileDialogFilePicker)

dlg.Title = "Select Excel Spreadsheet to import"
dlg.AllowMultiSelect = False
dataPath = dlg.SelectedItems(1)
Me!browseDataPath = dataPath
MsgBox("File is " & dataPath, vbOKOnly, "Check file name")

End If

Also tried :

Private Sub Command0_Click()
    Dim strFilter As String
    Dim strInputFileName As String

    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xlsm)", "*.xlsm")

    strInputFileName = ahtCommonFileOpenSave( _
                            Filter:=strFilter, _
                            OpenFile:=True, _
                            DialogTitle:="Choose an excel macro enabled file...", _
                            Flags:=ahtOFN_HIDEREADONLY)

    If Len(strInputFileName) > 0 Then
      MsgBox ("file name is " & strInputFileName)
    Else
        'No file chosen, or user canceled
    End If

End Sub

Bombed out at ahtAddFilterItem. Sub or Function not defined.

Help anyone?

[June7]

The file picker can pick the file but it cannot select the worksheet tab. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If the tab names never change, can include them in the range reference. Cell references must be included in the range. This might also be an issue if the range of data cells is always different. What would be the maximum?
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Try:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant, strFile As String
fd.AllowMultiSelect = False
With fd
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            strFile = vrtSelectedItem
        Next vrtSelectedItem
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Routes", strFile, True, "Bus Routes!A1:A10"
    End If
End With
Set fd = Nothing

Figuring out which category of spreadsheet is selected is also an issue. Is there any consistency in the workbook names?

[newbieX]

I copied your code and ran it but got the compile-time error "User-defined types not defined" on this line:
Dim fd As FileDialog

Ideas as to why? In VB window I went to Tools->References and have the first 4 boxes checked ( VB for Applications, MS Access 12.0 Object Library, OLE Automation, and MS Office 12.0 Access database engine Object Library.

[June7]

Microsoft Office x.0 Object Library seems to make it work.

[newbieX]

That did the trick. As you mentioned, the code works great when the range is static; however, the Bus Stops tab will have the same number of columns, but the rows will vary from 1 record to several hundred. When I declare a large range, I get an error message telling me that not all the data will be appended (which is correct as the range is larger than the records) but the error message is annoying and may freak out the user. Is there a work around when you don't know the number of records (i.e. the number of Excel rows?)

[June7]

Maybe error handler code could deal with the error.

Otherwise, would require opening the Excel file as a VBA object and manipulating the file to discover the last occupied row.