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.
Code:
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 :
Code:
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?