Loop and modify all Excel worksheets

[ghudson]

I am using the below code to modify all of the worksheets in an Excel file from Access 2003. I will not always know the names of the worksheets. The below code is working [sort of] but it errors at the end with the runtime error # 91 - Object variable or With block variable not set. My test file has seven worksheets. Each sheet is being formatted but it errors and the file is locked because of the runtime 91 error. Looping and automation with Excel is giving me a headache.

Any suggestions of what needs to be tweaked to fix the runtime 91 error?

Also, is there a way to count the number of worksheets and use the number of worksheets found instead of hard coding the instead of Do While x < 8 [worksheets] I would prefer a count variable like
Do While x < iTotalSheets ?

Public Sub TEST()
    Modify ("X:\MyFile.xls")
    MsgBox "end"
End Sub
 
Public Sub Modify(sFile As String)
 
    Dim xlApp As Object
    Dim xlSheet As Object
    Dim x As Integer
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
 
    x = 1
 
    With xlApp
        .Application.Sheets("SHEET1").Select '1st worksheet but I do not like this
        Do While x < 8
        .Application.Rows("1:1").Select
        .Application.Selection.Replace What:=".", Replacement:="#", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Application.Cells.Select
        .Application.Selection.RowHeight = 12.75
        .Application.Selection.Columns.AutoFit
        .Application.Range("A2").Select
        .Application.ActiveWindow.FreezePanes = True
        .Application.Range("A1").Select
        .Application.ActiveSheet.Next.Select
        x = x + 1
        Loop
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
    End With
 
    Set xlApp = Nothing
    Set xlSheet = Nothing
     
End Sub

Thanks in advance for your help!

 

[richary]

1. Application.Activeworkbook.sheets.count will give you the number of sheets for your Do..Loop


2.If you don't want to use the .Sheets("Sheet1") method, you can use .Sheets(i) and refer to the ith sheet and construct a for..next loop or even a for each...Next loop.

As regards the runtime error, it may be caused by

.Application.ActiveSheet.Next.Select

since it will fail after the last sheet is formatted (I assume, unless there is a "dummy" sheet there)

if you use method 1. this problem may go away, if that is indeed the problem

 

[ghudson]

Thanks that pointed me in the right direction. Here is what I got working...

    Dim xlApp As Object
    Dim xlSheet As Object
    Dim x As Integer
    Dim i As Variant

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

    With xlApp
        For Each xlSheet In xlApp.ActiveWorkbook.Worksheets
            With xlSheet
                If xlSheet.Name <> "testing" Then
                    'MsgBox xlSheet.Name
                    xlSheet.Activate
                    .Application.Rows("1:1").Select
                    .Application.Selection.Replace What:=".", Replacement:="#", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                    .Application.Cells.Select
                    .Application.Selection.RowHeight = 12.75
                    .Application.Selection.Columns.AutoFit
                    .Application.Range("A2").Select
                    .Application.ActiveWindow.FreezePanes = True
                    .Application.Range("A1").Select
                End If
            End With
    Next xlSheet
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing

I get an Excel message box "Excel can not find any data to replace" if there are no values found in my find and replace code line above. Is there any way to supress that? I tried using .Application.EnableEvents = False and .Application.ScreenUpdating = False but that had no effect on the Excel message.

Thanks!

 

[richary]

I don't know how to turn off Excel's error report, sorry. You could you use VBA's Replace() function instead, though.

 

[Bat17]

Try it this way

Public Sub Modify(sFile As String)
Dim xlApp As Object
Dim xlWkb As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks.Open(sFile)
With xlWkb
    For Each xlSheet In xlWkb.Worksheets
        xlApp.DisplayAlerts = False
        xlSheet.Rows("1:1").Replace What:=".", Replacement:="#", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ', SearchFormat:=False ', ReplaceFormat:=False
        xlApp.DisplayAlerts = True
        xlSheet.Rows("1:1").RowHeight = 12.75
        xlSheet.Rows("1:1").Columns.AutoFit
        xlSheet.Select
        xlSheet.Range("A2").Select
        xlApp.ActiveWindow.FreezePanes = True
        xlSheet.Range("A1").Select
    Next xlSheet
End With
xlWkb.Save
xlWkb.Close
xlApp.Quit

Set xlApp = Nothing
Set xlWkb = Nothing
     
End Sub

It might be worth asking down in the Excel forum if FreezePanes can be done without having to do a selection first. Should run faster then.
May also be possible to do the replaceing/formatting on all the sheets in one go, without looping, but I am not sure of the range syntax for that.

HTH

Peter

 

[ghudson]

I had tried the DisplayAlerts = False before and it still does not work with your code. Odd but your method does the find and replace [including the warnings] okay but it does not do the formatting of the worksheets that I also need.

Guess I will have to educate the users to ignore the Excel ''warning'' if it happens since I can not trap for it nor prevent it.

Thanks for trying!

 

[Bat17]

Curious
In my set up, W2K and XL2K, the formating works OK and the Replace function does not give a message if it finds nothing!

Peter

 

[reclusivemonkey]

I may be teaching Grandma to suck eggs here, but does;

On Error Resume Next

work? Any non fatal errors I get in excel are skipped over when using this. However, whether the message you are getting is strictly an error, I am not sure.

 

[ghudson]

Bat17, it must be a 2003 version thingy. For your code does not work as expected with Access 2003 and Excel 2003.

reclusivemonkey, I did already try On Error Resume Next and that did not help since it is not a runtime error.

The message is more or less an information type of warning. Since it is not an Access runtime error I can not trap for it or suppress it.

When I run the code directly within Excel the warning does not come up. If I do a simple Ctrl+H [Find and Replace] then the warning does come up stating it can not find any data to replace.

Not sure if it is worth pursuing for I do not know how to do it but I wonder if the Access function Replace() could be used within the routine I have going on above?