Formatting Column Widths in Excel Workbook after exporting it from Access

I have an Access 2003 application that creates an Excel Workbook containing about 12 worksheets.  After the workbook gets created I have to go into it and format the column widths on each worksheet.  Is there a way to do this formatting using vba code?
dsoderstromAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
to open the excel file using codes and set column widths

dim xlObj as object, j as integer
set xlObj=createobject("excel.application")
     xlobj.workbooks.open("c:\Raw Material Spreadsheet.xls")
     with xlObj
         for j=1 to .worksheets.count
              .worksheets(j).select
              .range("a:b").columnwidth=12              


         next
         .activeworkbook.save
     end with
     xlobj.quit
0
 
Rey Obrero (Capricorn1)Commented:
here is  a sample code to do formatting
Private Sub FormatSheet(objXLSheet As Object)
With objXLSheet
        .Range("D2", .Range("D2").End(xlToRight).End(xlDown)).NumberFormat = "#,###.00"
        .Range("C2", .Range("C2").End(xlDown)).NumberFormat = "0.00%"
        .Range("A1", .Range("A1").End(xlToRight).End(xlDown)).Columns.AutoFit
        .Range("A1", .Range("A1").End(xlToRight)).Font.Bold = True
        .Range("A1", .Range("A1").End(xlToRight)).HorizontalAlignment = xlCenter
        .Range("A1").CurrentRegion.Select
End With
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
post the codes that you are using to create the excel file
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dsoderstromAuthor Commented:
The code I am using is as follows:

Dim iselecfile
    iselecfile = Dir("c:\Raw Material Spreadsheet.xls")
    If iselecfile <> "" Then
        Kill ("c:\Raw Material Spreadsheet.xls")
    End If
    DoCmd.OpenQuery "Clear Selected Comm Code List"
    DoCmd.OpenQuery "Load Selected Comm Code List-B"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Aluminum Extrusion", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Aluminum Sheet", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Hex Shaft", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Oval", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Angle", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Channel", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Pipe", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Round Tube", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Flat Bar", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Strip", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Expanded Metal", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Plate", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Sheet", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Plow Share-Beveled Bar", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Rectangular Tube", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Square Tube", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Round", "c:\Raw Material Spreadsheet.xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Threaded Rod", "c:\Raw Material Spreadsheet.xls", True
0
 
dsoderstromAuthor Commented:
Still don't know how to do this based on earlier reply.
I need to know what the code is for opening the Excel workbook and then stepping through the worksheets changing the column widths on each one.
0
 
dsoderstromAuthor Commented:
This worked perfectly.  Thanks so much for the help.
0
All Courses

From novice to tech pro — start learning today.