Allow user to pick worksheet

[dancg68]

I am trying to create a new form where the user can choose an Excel worksheet to import into the database. I found code on several pages to let the user choose the file but not the specific worksheet. Below is the code I'm using (thanks to Programming Made EZ on YouTube). I cannot find any code that lets the user choose the worksheet, but found references to creating a listbox or combobox to list the worksheets, but I don't know how to make that work. The workbook and worksheet name changes depending on the data needing to be imported.

Private Sub btnBrowseUpdate_Click()


Dim diag As Office.FileDialog
Dim item As Variant

txtFilename = ""
lbxSheets = ""

Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Select an Excel spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx"

If diag.Show Then
For Each item In diag.SelectedItems
Me.txtFilename = item
Next
End If

End Sub

Private Sub btnImport_Click()
Dim FSO As New FileSystemObject

If Nz(Me.txtFilename, "") = "" Then
MsgBox "Please select a file"
Exit Sub
End If

If FSO.FileExists(Me.txtFilename) Then
ExcelImport.importExcelSpreadsheet Me.txtFilename, FSO.GetFileName(Me.txtFilename)
Else
MsgBox "File Not Found"
End If

End Sub

[June7]

You have code for listing worksheets but don't know how to make work - why? Exactly what are you stuck on? What is that code?

I have never seen ExcelImport command.

Do you want to import to existing table or create a new table?

In future, please post code, especially lengthy snips, between CODE tags to retain indentation and readability.

[pbaldy]

I'm not familiar with that command either. With "standard" Excel automation, it's pretty easy. This uses a textbox named txtSheet (previous declarations, etc not shown):

    strWorksheet = Forms!frmMain.txtSheet
    Set xlCaesars = CreateObject("excel.application")

    xlCaesars.Workbooks.Open (strPathCaesars)

    Set xlSheetCaesars = xlCaesars.Worksheets(strWorksheet)

[Gicu]

Here is a sample file I recently created for someone on this forum (https://www.accessforums.net/showthr...665#post492665) that allows you to browse for an Excel workbook and link all sheets to the Access db and display them on a tabbed form, I hope it would give you some hints to do what you need.

Cheers,
Vlad

[June7]

Here's another approach that doesn't require a combobox or listbox, however, if there are a lot of sheets can be impractical. I have done this to build a list of about 20 items. More than that and gets too long for display.

Sub ImportSheet()
Dim diag As Office.FileDialog
Dim strW As String, strS As String, x As Integer, sResponse As String
Dim xlApp As Excel.Application, xlWb As Excel.Workbook, xlSht As Excel.Worksheet


Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.title = "Select an Excel spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx, *.xlsm"


If diag.Show Then
    x = 1
    strW = diag.SelectedItems(1)
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(strW)
    For Each xlSht In xlWb.Worksheets
        strS = strS & x & " : " & xlSht.Name & vbCrLf
        x = x + 1
    Next
End If
sResponse = "?"
Do While sResponse = "?"
    sResponse = InputBox("Enter number to select sheet" & vbCrLf & strS, "Select Worksheet", "?")
    If sResponse Like "[1-" & x - 1 & "]" Then
        strS = Split(strS, vbCrLf)(sResponse - 1)
        strS = Split(strS, " : ")(1)
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Test", strW, , strS & "$"
    ElseIf sResponse <> "" Then
        MsgBox "Not a valid entry. Enter a number from list or Cancel."
        sResponse = "?"
    End If
Loop

End Sub