Using Access to Count Worksheets in Workbook

[jadown]

Hello,



I'm stuck once again. I would like Access to open an Excel file count the worksheets and then give each tab the name of the column heading in cell A1 of each worksheet. I had no problem with the code in Excel but when I tried to add it to Access I am getting an error on the WS_Count = obj.ActiveWorkbook.Worksheets.Count line. The error message is "Object variable or With block variable not set" I'm not understanding what I did wrong.


Here is the code:

'This block of code will cycle through each tab and name them
         Dim WS_Count As Integer
         Dim I As Integer
         
         Dim obj As Object
         
         Set obj = CreateObject("Excel.Application")
          
          obj.Visible = True
         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
          WS_Count = obj.ActiveWorkbook.Worksheets.Count
        
       ' Begin the loop.
         For I = 1 To WS_Count
           
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            obj.ActiveWorkbook.Worksheets(I).Name = obj.ActiveWorkbook.Worksheets(I).Range("A1").Value
                     
         Next I

[rpeare]

I don't think you can use 'activeworkbook' when you're working in access, you would have to actually set up the workbook and tell it which file to open

activeworkbook assumes you are trying to run the code in an excel file you already have open, something you're not doing with your code.


You'd need something more like this:
http://www.experts-exchange.com/Data..._28142208.html

[robbeh]

rpeare is right, you will need to open the workbook and reference that object.

Dim wb As Object
Dim obj As Object

Set obj = CreateObject("Excel.Application")
Set wb = obj.Workbooks.Open("workbook path")
WS_Count = wb.Worksheets.Count

Or something along those lines. You might need to reference the Microsoft Excel Object Library in Tools -> References.

[ssanfu]

Ken Snell has examples of importing and exporting Excel workbooks. I know you are not importing or exporting, but it shows examples of Excel automation from Access. Worth a look....

http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm