Solved

Freeze Panes through .VBS Script

Posted on 2008-10-16
6
2,457 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now