CODEWITHSUNDEEP
excelvba
Mikhail Ivanov
Mikhail Ivanov

Reputation: 21

VBA add FreezePanes to open file

I'm stuck. Try to add FreezePanes to all my excel files. I have 28 folders "municipalities" in each folder 16 files: 1first, 2second,... All filese Excel 2003 formats, and are divided into two types - with a figure in the title and without. Each file has several pages.
In parent directory i create file "base" in each two pages "municipalities" and "FilesList" and macro "Sub AddTo Freeze()" 

Sub addToFreeze()
    x% = firstDataBaseString
    Do While Application.Workbooks(thisFileName).Worksheets("municipalities").Cells(x, 2) <> Empty
        y% = firstDataBaseString
        actMun$ = Application.Workbooks(thisFileName).Worksheets("municipalities").Cells(x, 2)
        Do While Application.Workbooks(thisFileName).Worksheets("FilesList").Cells(y, 1) <> Empty
            actFile$ = TrimFormats(Application.Workbooks(thisFileName).Worksheets("FilesList").Cells(y, 1)) & addedToMunicipal & ".xls"
            openWaN$ = ThisWorkbook.Path & Application.PathSeparator & actMun & Application.PathSeparator & actFile
            Dim fileHaveNum As Boolean
            fileHaveNum = HasNumber(Application.Workbooks(thisFileName).Worksheets("FilesList").Cells(y, 1))
            If FileExists(openWaN) Then
                Dim openApp As Excel.Application
                Set openApp = New Excel.Application
                openApp.DisplayAlerts = False
                openApp.Visible = True
                openApp.ScreenUpdating = False
                Dim openBook As Workbook
                Set openBook = openApp.Workbooks.Open(openWaN)
                For Each ws In openBook.Worksheets
                    ws.Unprotect Password:="P$n177"
                    afName$ = TrimFormats(actFile)
                    sName$ = ws.Name
                    Workbooks(afName).Worksheets(sName).Activate
                    If fileHaveNum Then
                        Range("G4:G4").Select
                        ActiveWindow.FreezePanes = True
                    Else
                        Range("G6:G6").Select
                        ActiveWindow.FreezePanes = True
                    End If
                    ws.Protect Password:="P$n177"
                Next
                openBook.Close SaveChanges:=True
                openApp.ScreenUpdating = True
                openApp.Quit
            End If
            y = y + 1
        Loop
    x = x + 1
    Loop

End Sub

Every tyme when i try run macro his say "Subscript out of range". Or add FreezePanese to my "base" file....

Upvotes: 1

Views: 77

Answers (1)

Mikhail Ivanov
Mikhail Ivanov

Reputation: 21

Solution:
Dont use 

Dim openApp As Excel.Application
Set openApp = New Excel.Application
Dim openBook As Workbook
Set openBook = openApp.Workbooks.Open(openWaN)

Need use 

Dim openBook As Workbook
Set openBook = Workbooks.Open(openWaN)
Workbooks(openBook.Name).Activate

Upvotes: 1

Related Questions