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!

Expand|Select|Wrap|Line Numbers

 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

msquared

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.

Calling A Combobox Value in VBA Code

re: Calling A Combobox Value in VBA Code

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about