Using Access VBA to edit and format excel spreadsheet

Question

Hi. Currently I have an access database outputting to excel and it works. However, the spreadsheet is rather plain and the columns aren't the right width, the fields that have a date\time only show date etc. I want to edit the spreadsheet after I output all the data. I've already figured out how to open file, add a row, add wordwrapping, change column width. However, I can't figure out how to make a column formatted with date\time format ex mm-dd-yyyy hh:mm I've tried various things that don't work. Can anyone help to show how to alter the columns format to show the date\time?

Thanks in advance.

Sub testxl()

Dim xlfilename As String
xlfilename = "D:\Data\filename.xlsx"
Dim xlsheetname As String

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlapp = Excel.Application
'xlapp.Visible False

Set xlbook = xlapp.Workbooks.Open(xlfilename)

Set xlsheet = xlbook.Worksheets(1)

With xlsheet
'.Rows("1").Insert

.Rows("1").RowHeight = 30

.Rows("1").WrapText = True

' Note I tried below various formatting options, and none work.
'.Columns("G").Format "mm/dd/yyyy hh:nn"

.Columns("A").ColumnWidth = 22
.Columns("B").ColumnWidth = 9
.Columns("C").ColumnWidth = 20
.Columns("D").ColumnWidth = 11
.Columns("E").ColumnWidth = 8
.Columns("F").ColumnWidth = 12
.Columns("G").ColumnWidth = 22
.Columns("H").ColumnWidth = 15
.Columns("I").ColumnWidth = 15
.Columns("J").ColumnWidth = 28
.Columns("K").ColumnWidth = 28
.Columns("L").ColumnWidth = 18
.Columns("M").ColumnWidth = 20
.Columns("N").ColumnWidth = 7.5

End With
xlbook.Save

xlbook.Close
Set xlbook = Nothing
Set xlapp = Nothing

End Sub

Answer 1

Set the NumberFormat on the column range, something like:

Dim r As Excel.Range = Me.Columns.Item(7)
r.NumberFormat = "MM/dd/yyyy hh:mm"

That's VSTO so your VBA in Access might just be

.Columns("G").NumberFormat = "MM/dd/yyyy hh:mm"

Note that your date-time pattern had some incorrect characters.