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

0
johnnyaction
Asked:
johnnyaction
  • 2
1 Solution
 
skipper68Commented:
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 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
 
johnnyactionAuthor Commented:
i figured it out
0

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