Excel autosize columns

I create a new excel report everyday from SQL Reporting Services. My problem is that I need to have all the columns autosized to fit the data in the report. I want to be able to run a batch process or run some kind of macro in access or something that I can schedule ao open the excel file, autosize the columns and lock the column headings. Does anybody have any ideas of what I can use for this? Access? Winbatch? Another tool?

FYI, I know how to write the code for the macro Im just not sure how to apply it to the newly created file automatically?

'macro code
Dim appExcel As Object
Dim myWorkbook As Object
Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("C:\test\test.xls")

appExcel.Run
appExcel.Visible = True
Set appExcel = Nothing
Set myWorkbook = Nothing
 

LVL 1
johnnyactionAsked:
Who is Participating?
 
johnnyactionAuthor Commented:
I could not write the code in a macro because the file was recreated on a daily basis. So, I created an SSIS package with a Script component to open each file and do the autosize and freeze panes from there. Thanks anyway everyone...
0
 
skipper68Application Development ManagerCommented:
Something like this placed in the WorkSheet_Calculation event, it will run each time a calculation is run.  Or you can just use the autofit in your code.

Option Explicit

Private Sub Worksheet_Calculate()
     UsedRange.Columns.AutoFit
End Sub
0
 
rvelizCommented:
To autosize the columns use:

dim myWorksheet as Worksheet

myWorksheet.Range.("A:F").EntireColumn.AutoFit

Open in new window


Columns should work as well instead of Range.

To lock the cells try

 
myWorksheet.Range("A1:F1").Locked = True
myWorksheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True

Open in new window

0
 
johnnyactionAuthor Commented:
i figured it out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.