Enumerating Excel worksheets within Access 
Author Message
 Enumerating Excel worksheets within Access

Does anyone know how I can enumerate the worksheets within
an excel workbook from within in Access?  I want to select
an excel file, show a listing of the worksheets in that
file and then allow the user to pick the worksheet to
import.

Thanks

Mike



Sun, 04 Dec 2005 01:59:15 GMT  
 Enumerating Excel worksheets within Access


Quote:
> Does anyone know how I can enumerate the worksheets within
> an excel workbook from within in Access?  I want to select
> an excel file, show a listing of the worksheets in that
> file and then allow the user to pick the worksheet to
> import.

  ' don't forget to add the references!
  dim xlb as Excel.Workbook
  dim xls as Excel.Worksheet

  dim strListBox as String

  ' open the workbook somehow
  Set xlb = OpenSomeWorkbook()

  ' now look at each worksheet
  For Each xls in xlb.Worksheets

    ' add a semicolon after the first one only
    If Len(strListBox).0 Then strListbox = strListBox & ";"

    ' and store the name in it
    strListBox = strListBox & xls.Name

  Next xls

  ' okay, now set up the listbox
  With Me!lisSheets

    ' Look up this parameter because this is from memory!!
    .RowSourceType = "ValueList"

    ' Set the sheet names into it
    .RowSource = strListBox

    ' and update the display
    .Requery

  End With

... or something like that. This is untested air code, but it should give
you an idea or two.

All the best

Tim F



Sun, 04 Dec 2005 03:17:56 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. VBasic macro to delete worksheet from within Excel

2. creating more than one excel worksheet within 1 routine in vb6

3. Exporting Access tbl to Excel - Worksheet Names

4. Access/VBA: Creating New Excel Worksheets?

5. Linked Excel worksheet losing data. Access table OK

6. Save Access report to specific Excel Worksheet

7. Import multiple excel files with inconsistent number of worksheets in each into ACCESS

8. List predefined Excel ranges/worksheets from Access

9. Excel worksheet to Access

10. VBA code to access PasteSpecial -> Excel Worksheet

11. : Accessing Excel Worksheet using ADO

12. Accessing unnamed cell range in Excel worksheet?

 

 
Powered by phpBB® Forum Software