Solved

Freeze Panes through .VBS Script

Posted on 2008-10-16
6
2,583 Views
Last Modified: 2013-11-10
I have a script that will freeze panes and a few other things like turn auto filter on etc.
What I am needing it to do is what it does now AND apply it to all workbooks in the same file,
looks like it is selecting by .ActiveSheet but I am unsure how to select and apply to ALL of them.

Thanks and code is attached.

ednetmanFormatExcel "c:\monthly\Report.xls"
 
Sub ednetmanFormatExcel(ByVal vFilePath)
 Dim xlApp
 Set xlApp = CreateObject("excel.application")
 With xlApp.Workbooks.Open(vFilePath) 'open file
  With .ActiveSheet
   .Rows(1).Font.Bold = True '1.  Bold the headers (always in row 1)
   .AutoFilterMode = False 'turn off any existing autofilter just in case
   .Rows(1).AutoFilter '2. Turn on AutoFilter for all coloms
   .Columns.AutoFit '3. Set Column width to AutoFit Selection
   '4. Set a freeze under column 1 so that the header is always present at the top
   .Range("A2").Select
   xlApp.ActiveWindow.FreezePanes = True
  End With
  .Close True 'save and close
 End With
 xlApp.Quit
 Set xlApp = Nothing
End Sub

Open in new window

0
Comment
Question by:smyers051972
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:ExcelGuide
ID: 22731066
use "for each" instead of "with", so try the following. Please test this and post your results, i was not able to test it.
ednetmanFormatExcel "c:\monthly\Report.xls"
 
Sub ednetmanFormatExcel(ByVal vFilePath)
 Dim xlApp, xlSheet
 Set xlApp = CreateObject("excel.application")
 Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
 
 With xlApp.Workbooks.Open(vFilePath) 'open file
For Each xlSheet In xlApp.ActiveWorkbook.Worksheets
With xlSheet
   .Rows(1).Font.Bold = True '1.  Bold the headers (always in row 1)
   .AutoFilterMode = False 'turn off any existing autofilter just in case
   .Rows(1).AutoFilter '2. Turn on AutoFilter for all coloms
   .Columns.AutoFit '3. Set Column width to AutoFit Selection
   '4. Set a freeze under column 1 so that the header is always present at the top
   .Range("A2").Select
   xlApp.ActiveWindow.FreezePanes = True
  End With
loop
  .Close True 'save and close
 End With
 xlApp.Quit
 Set xlApp = Nothing
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 22731235
There is a loop with out do error I tried to put the "do" where i thought would be the right place but no go :(

Thanks and I shall await your reply!
0
 
LVL 17

Assisted Solution

by:ExcelGuide
ExcelGuide earned 200 total points
ID: 22731312
my stupid mistake....sorry for that
ednetmanFormatExcel "c:\monthly\Report.xls"
 
Sub ednetmanFormatExcel(ByVal vFilePath)
 Dim xlApp, xlSheet
 Set xlApp = CreateObject("excel.application")
 Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
 
 With xlApp.Workbooks.Open(vFilePath) 'open file
For Each xlSheet In xlApp.ActiveWorkbook.Worksheets
With xlSheet
   .Rows(1).Font.Bold = True '1.  Bold the headers (always in row 1)
   .AutoFilterMode = False 'turn off any existing autofilter just in case
   .Rows(1).AutoFilter '2. Turn on AutoFilter for all coloms
   .Columns.AutoFit '3. Set Column width to AutoFit Selection
   '4. Set a freeze under column 1 so that the header is always present at the top
   .Range("A2").Select
   xlApp.ActiveWindow.FreezePanes = True
  End With
Next xlSheet 
  .Close True 'save and close
 End With
 xlApp.Quit
 Set xlApp = Nothing
End Sub

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:smyers051972
ID: 22731368
Expected end of statement encountered, sheesh its not our day today!
Will wait for further instructions :)

Thanks!
0
 
LVL 38

Accepted Solution

by:
Shift-3 earned 300 total points
ID: 22731392
Here is my shot at it.  I've tested this successfully.


ednetmanFormatExcel "c:\monthly\Report.xls"
 
Sub ednetmanFormatExcel(ByVal vFilePath)
    Set xlApp = CreateObject("excel.application")
    xlApp.Visible = False
    xlApp.DisplayAlerts = False
    Set objWorkbook = xlApp.Workbooks.Open(vFilePath) 'open file
 
    For Each objWorksheet in objWorkbook.Worksheets
        objWorksheet.Activate
        With objWorkbook.ActiveSheet
            .Rows(1).Font.Bold = True '1.  Bold the headers (always in row 1)
            .AutoFilterMode = False 'turn off any existing autofilter just in case
            .Rows(1).AutoFilter '2. Turn on AutoFilter for all coloms
            .Columns.AutoFit '3. Set Column width to AutoFit Selection
            '4. Set a freeze under column 1 so that the header is always present at the top
            .Range("A2").Select
        End With
        xlApp.ActiveWindow.FreezePanes = True
    Next
    
    objWorkbook.Close True 'save and close
    xlApp.Quit
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31506737
Thank you both of you!  I still gave points between both because you both helped me. MUCH appreciated.  There will be more soon I am sure :)

Thanks again!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question