Calling A Combobox Value in VBA Code

[beingnick]

Hello,

I am using Access 2007, but need to be compatible with 2003. I am importing a sheet from Excel into Access (code below). I would like the sheet name to be determined from a drop down on a form instead of being hard coded. So the user would select the sheet name from the drop down, click Import, and the VBA would import the tab the user selected. Also, different tabs would need to be imported to different tables. Is there a way to make the table name dependent on the tab selected?

I'm attempting to use the same function instead of having it repeated a bunch of times with only two small variables changed.

Thanks!

Option Compare Database
'------------------------------------------------------------
' Import
'
'------------------------------------------------------------
Function Import()
Dim sFilename As Office.FileDialog
 
Set dlgOpen = Application.FileDialog(dialogtype:=msoFileDialogOpen)
 
With dlgOpen
    .AllowMultiSelect = False
    .Show
End With
 
If dlgOpen.SelectedItems.Count = 0 Then Exit Function
sPath = dlgOpen.SelectedItems.Item(1)
 
On Error GoTo Import_Err
 
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tempTblWeeklyPipeline", sPath, True, "SAP OM Active$"
    DoCmd.SetWarnings True
    MsgBox "Import Complete"
 
Import_Exit:
    Exit Function
 
Import_Err:
    MsgBox Error$
    Resume Import_Exit
 
End Function

[MMcCarthy]

Set the tablename to a variable and use a case statement to set the value dependent on the tab.

If you need it to be compatible in 2003 then design it in 2003 not 2007. The backwards compatibility from 2007 to 2003 is not good.