Format Excel columns with VB code.

[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

 

[Ken Snell MVP]

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.

 

[Proko]

Thanks Ken.

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

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Proko]

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