list box gets its list from excel workbook worksheets 1

[scottian]

Does anyone know of a way to retrieve the names of the worksheets from a workbook and use them as the source for a list box?

I have looked in the forums and even googled it but cant find a solution.
Ive got a form and on this form there is a button which a user presses to start the import of a spreadsheet, but the workbook contains quite a few spreadsheets and not all of the sheets are imported. So at present the user has to open the book, select the right sheet, copy its name and paste into a field on the import form then click the button, but Id prefer the user to select the worksheet name from a list box.

Ive managed to come up with the code below based on a function by SkipVought. But it wont work, all it does is open the book then close it. the sheet names are not obtained.

can anyone help?

cheers


Private Sub btnButton_Click()
Dim Filename As String
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim shtName As String
Dim wkbName As String

Filename = ""
Filename = Dir(DBPath() & "*.xls")
wkbName = DBPath() & Filename

Set db = CurrentDb
Set objXL = New Excel.Application

Set objWkb = .Workbooks.Open(wkbName)
With objXL
.Visible = True

For Each objWkb In Workbooks
For Each objSht In Workbooks
DoCmd.RunSQL "INSERT INTO tblSheetList ( SheetName ) SELECT objSht.Name AS Expr1;"
Next
Next

objWkb.Close True
Set objSht = Nothing
Set objWkb = Nothing
objXL.Quit ' NOTE - changed sequence
Set objXL = Nothing
Set db = Nothing
End With
End Sub



"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[HarleyQuinn]

Is this a bit more like it?

Dim Filename As String
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
'Dim db As DAO.Database
Dim shtName As String
Dim wkbName As String

Filename = ""
Filename = Dir(DBPath() & "*.xls")
wkbName = DBPath() & Filename

  'Set db = CurrentDb
  Set objXL = New Excel.Application
   
      With objXL

         Set objWkb = .Workbooks.Open(wkbName)
                      .Visible = True
            For Each objSht In objWkb.Sheets
                     MsgBox objSht.Name
                     DoCmd.RunSQL "INSERT INTO tblSheetList ( SheetName ) SELECT  objSht.Name AS Expr1;"
            Next

        
        objWkb.Close True
  Set objSht = Nothing
  Set objWkb = Nothing
  objXL.Quit ' NOTE - changed sequence
  Set objXL = Nothing
  'Set db = Nothing
  End With

You need to check for each sheet in the workbook object you opened. The With objXL was a bit out of place as well but that's nothing really.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

I believe you want another Dir to loop through all the files.

Private Sub btnButton_Click()
Dim Filename As String
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim Sht As Object
Dim db As DAO.Database
Dim shtName As String
Dim wkbName As String

    Set db = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    
    Filename = ""
    Filename = Dir(DBPath() & "*.xls")
    
    Do While Filename <> ""
        wkbName = DBPath() & Filename
    
        Set objWkb = objXL.Workbooks.Open(wkbName)
    
        For Each Sht In objWkb.Worksheets
            'For Each objSht In Workbooks
            db.Execute "INSERT INTO tblSheetList ( SheetName ) Values ('" & Sht.Name & "')"
            'Next
        Next
        
        objWkb.Close True
        'Set objSht = Nothing
        Set objWkb = Nothing
        Filename = Dir
    Loop
    
    objXL.Quit ' NOTE - changed sequence
    Set objXL = Nothing
    Set db = Nothing
End Sub

 

[HarleyQuinn]

Good spot Remou, that was something I'd just completely bypassed when looking at the code

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PWise]

try this
Row Source Type must be Value list

Dim dirstr As String
Dim FileName As String
Dim cnXcell As ADODB.Connection
Dim rsXcell As ADODB.Recordset
dirstr = "C:"
FileName = "PayrollRec.xls"
If cnXcell Is Nothing Then Set cnXcell = New ADODB.Connection
If cnXcell.State = adStateClosed Then cnXcell.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dirstr & "\" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set rsXcell = cnXcell.OpenSchema(adSchemaTables)
Do Until rsXcell.EOF
Me.Combo0.AddItem (rsXcell.Fields("TABLE_NAME"))
rsXcell.MoveNext
Loop

 

[scottian]

Thank you very Remou. thats excellent

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks