[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

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?
0
dsoderstrom
Asked:
dsoderstrom
  • 3
  • 3
1 Solution
 
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
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
Rey Obrero (Capricorn1)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
 
dsoderstromAuthor Commented:
This worked perfectly.  Thanks so much for the help.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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