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.Visible = True
Set appExcel = Nothing
Set myWorkbook = Nothing

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...
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()
End Sub
To autosize the columns use:

dim myWorksheet as Worksheet


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

johnnyactionAuthor Commented:
i figured it out
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.