Format Excel columns with VB code.

  • Thread starter Thread starter Proko
  • Start date Start date
P

Proko

I am exporting the results of an Access query to Excel using:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qry_dataoutput", FilePath

When I open the resulting spreadsheet the columns that had the format
"dd/mm/yy hh:nn" in Access have the format "dd/mm/yy" in the spreadsheet. Is
there any way to force the original format in the resulting spreadsheet? Or
is it possible to format the spreadsheet's columns using code within Access?
Any help would be greatly appreciated. Thankyou, Proko
 
Not in the TransferSpreadsheet action, no. You could open the EXCEL file
with Automation after it's exported, and use VBA code to format the columns
in the EXCEL worksheet.
 
For the benefit of others, this is how I did it:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qry_dataoutput", strSaveFileName
'transferSpreadsheet method does not transfer the date/time format
successfully
'so lets have a go at formatting the colums using automation. Remember
that Microsoft
'Excel object Library 11.0 needs to be made an available reference.

FormatExcel (strSaveFileName)

Private Sub FormatExcel(FileName As String)

On Error GoTo FormatExcel_Error

Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open(FileName)
Set XLSheet = XLBook.Worksheets(1)
With XLSheet
.Activate
.Range("G:L,N:N").Select
XLApp.Selection.NumberFormat = "d/m/yyyy h:mm"
.Range("A2").Select
XLBook.Save
End With

Exit_this_sub:
Set XLSheet = Nothing
XLBook.Close
Set XLBook = Nothing
XLApp.Quit
Set XLApp = Nothing
Exit Sub

FormatExcel_Error:
MsgBox "Error " & Err.Number & " :" & Err.Description
Resume Exit_this_sub
End Sub
 
Back
Top