VBA count sheets excel from Access

[Coleman984]

Excel.Application.ActiveWorkbook.Sheets(Sheets.Count) doesn't work gives Method of sheets error.

 

[GohDiamond]

Excel.Application.ActiveWorkbook.Worksheets.Count
This works in the VBA editor in Excel
I personally couldn't get it to work in the MS Access 2003 VBA Editor. I think it has to do with active reference libraries under tools menu in the VBA editor.

Hope that points you in the right direction.
Goh

 

[Coleman984]

Excel.Application.ActiveWorkbook.Worksheets.Count
This works in the VBA editor in Excel
I personally couldn't get it to work in the MS Access 2003 VBA Editor. I think it has to do with active reference libraries under tools menu in the VBA editor.

Hope that points you in the right direction.
Goh

I created a function in excel to give the sheets count worksheet level and pointed access to the cell to get the sheet count. Maybe not the best way but it works.

 

[stopher]

Excel.Application.ActiveWorkbook.Worksheets.Count
This works in the VBA editor in Excel
I personally couldn't get it to work in the MS Access 2003 VBA Editor. I think it has to do with active reference libraries under tools menu in the VBA editor.

Hope that points you in the right direction.
Goh

Yes, just add the "Microsoft Excel xx.x Object Library".

The the code would look like:

Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWkb = xlApp.Workbooks.Open(FileName:="c:\temp\testfile.xlsx")

    Debug.Print xlWkb.Worksheets.Count

xlWkb.Close
xlApp.Quit

Set xlWkb = Nothing
Set xlApp = Nothing