Using Access of Count Worksheets Loop (1 Viewer)

[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.
          [COLOR=#FF0000][B]WS_Count = obj.ActiveWorkbook.Worksheets.Count[/B][/COLOR]
        
       ' 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

 

[mh123]

you haven't loaded a workbook in this block of code?

 

[jadown]

Here is the complete code that I have. The block above is what I just added. Without the block of code above it works fine. But with the code above I get the error message stated above.

Dim intChoice As Integer
Dim strPath As String
Dim fd As Object, sFileName As String
'Opens the Excel file
Set fd = Application.FileDialog(3)
With fd
.AllowMultiSelect = False
.Title = "Browse to Select a File"
If .Show = -1 Then sFileName = .SelectedItems(1)
End With
'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


'Change the column headings on the spreadsheet
If Len(sFileName) = 0 Then Exit Sub
Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")
'Collects current path
'make the file dialog visible to the user
intChoice = Application.FileDialog(4).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
strPath = _
Application.FileDialog(4).SelectedItems(1)
'displays the result in a message box
End If
'This will
With xlObj
.Workbooks.Open sFileName
.Visible = True
.Worksheets.Select
.Cells.Select
.Selection.Replace What:="=", Replacement:="'="
.Selection.Replace What:="=", Replacement:="'="
.ActiveWorkbook.SaveAs FileName:=strPath & "\" & "ImportFile.xlsx"
.Quit
End With
Set fd = Nothing
Set xlObj = Nothing

 

[Gasman]

As has already been mentioned you have not opened an excel file at that point in the code?.

You do not do that until

.Workbooks.Open sFileName

Not until then, can you manipulate anything on the sheet/workbook.

So place your code to go through the sheets after you have the file open.

HTH

 

[jadown]

Thanks for you help.