Excel autosize columns

Posted on 2011-04-20
Last Modified: 2012-08-14
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

Question by:johnnyaction
    LVL 9

    Expert Comment

    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
    LVL 1

    Expert Comment

    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

    LVL 1

    Accepted Solution

    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...
    LVL 1

    Author Closing Comment

    i figured it out

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now