We help IT Professionals succeed at work.
Get Started

fail to update footer in vba automation

hkgal
hkgal asked
on
303 Views
Last Modified: 2012-05-11
I've got a code to handle and update every worksheets of every Excel workbooks in a single folder. It works well with most cases. But if the Excel contains only one sheet, it fails:

Sub Sample()
    Dim wb As Workbook, ws As Worksheet
    Dim StrFile, pwd As String
    Dim msgFooter As String
    Dim MyFolder As String
    Dim pwdbool As Boolean
   
    '~~> Footer Message
    msgFooter = "OFFICAL22"
    MyFolder = InputBox("Pls enter the path of folder") & "\"
   
    StrFile = Dir$(MyFolder & "*.xls")
    Application.DisplayAlerts = False
    Do While Len(StrFile)
       
        pwd = "123"
        Set wb = Nothing
        On Error Resume Next
        Set wb = Workbooks.Open(MyFolder & StrFile, , , , pwd, pwd)
        If wb Is Nothing Then
            pwd = "abc"
           
            Set wb = Workbooks.Open(MyFolder & StrFile, , , , pwd, pwd)
            If wb Is Nothing Then
                pwd = "se456"
                pwdbool = True
                Set wb = Workbooks.Open(MyFolder & StrFile, , , , pwd, pwd)
               
               If wb Is Nothing Then
                pwd = "8880011"
                pwdbool = True
                Set wb = Workbooks.Open(MyFolder & StrFile, , , , pwd, pwd)

               
                      If Not wb Is Nothing Then
                       '~~> Add the relevent Text to the Footer
                           For Each ws In wb.Worksheets
                              With ws.PageSetup
                                  .LeftFooter = msgFooter
                             End With
                          Next
                      End If
               
                 Else
                    For Each ws In wb.Worksheets
                       With ws.PageSetup
                           .LeftFooter = msgFooter
                       End With
                    Next
              End If

               
            Else
                For Each ws In wb.Worksheets
                   With ws.PageSetup
                       .LeftFooter = msgFooter
                   End With
                Next
            End If
        Else
            For Each ws In wb.Worksheets
               With ws.PageSetup
                   .LeftFooter = msgFooter
               End With
            Next
        End If

        wb.Close savechanges:=True
        StrFile = Dir
    Loop
   
    Set ws = Nothing
    Set wb = Nothing
   
End Sub


I can't figure out why...pls help! Thanks!
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2008
Commented:
This problem has been solved!
Unlock 2 Answers and 19 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE