• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

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

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now