Cycling through worksheets in a different workbook

[ccordner]

Hi

I want to get some data from a workbook, but it could be saved anywhere, under any name and the sheet I want might not always have the same name.

So I've built a userform with a combobox, in the hope that people can choose the worksheet they want.

Any ideas what's wrong with this code?

FileName = Application.GetOpenFilename("Microsoft Excel Spreadsheet (*.xlsx),*.xlsx," & _
                                        "Microsoft Excel Macro-Enabled Workbook (*.xlsm),*.xlsm," & _
                                        "Microsoft Excel 97-2003 Spreadsheet (*.xls),*.xls," & _
                                        "View All Files (*.*),*.*,", 1)
    
OldFileName = FileName
Workbooks.Open FileName
FileName = Right(FileName, Len(FileName) - InStrRev(FileName, "\"))
Load frmSelectWorksheet
For Each WSheet In FileName
    frmSelectWorksheet.cmbWorkSheet.AddItem Worksheet.Name
Next

Thanks
Chris

 

[Domenic]

Maybe something like this...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()


    [color=darkblue]Dim[/color] Filename [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] wkbOpen [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet


    Filename = Application.GetOpenFilename( _
        "Excel Workbook (*.xlsx), *.xlsx," & _
        "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm," & _
        "Excel 97-2003 Workbook (*.xls), *.xls," & _
        "All Files (*.*), *.*,", 1)
                                            
    [color=darkblue]If[/color] Filename = "False" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        
    [color=darkblue]Set[/color] wkbOpen = Workbooks.Open(Filename)
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] wkbOpen.Worksheets
        frmSelectWorksheet.cmbWorkSheet.AddItem wks.Name
    [color=darkblue]Next[/color]
    
    frmSelectWorksheet.Show


[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]