Function mYExport( _
query$, path$, _
fileName$, wksName$, _
colsCurrency$, colsDate$ _
) As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Error GoTo errHandler
Dim xlApp As Object, wkbk As Object, wks As Object
Dim file$
Dim formatCur$, formatDate$, intColor&
Dim arrayCols() As String, col$, n%, i%, w!
Dim cell As Range
Dim msg$
' Worksheet formats
formatCur$ = "$#,##0_);($#,##0)"
formatDate$ = "dd-mm-yyyy"
intColor& = RGB(192, 192, 192)
' Create workbook
file$ = path$ & fileName$
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=query$, _
fileName:=file$, _
HasFieldNames:=True
' Open workbook
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
Set wkbk = .Workbooks.Open(file$)
End With
' Format worksheet
Set wks = wkbk.worksheets(1)
With wks
.Name = wksName$
' Currency columns
arrayCols = Split(colsCurrency$, ",")
For i = LBound(arrayCols) To UBound(arrayCols)
With .Columns(arrayCols(i))
.NumberFormat = "$#,##0_);($#,##0)"
End With
Next i
' Date columns
arrayCols = Split(colsDate$, ",")
For i = LBound(arrayCols) To UBound(arrayCols)
With .Columns(arrayCols(i))
.NumberFormat = formatDate$
End With
Next i
' Filters
' With .Range("A1")
' .Select
' .autofilter
' End With
' Column width adjustments
With .Cells
.Select
.EntireColumn.AutoFit
End With
n% = .Cells(1, 1).End(xlToRight).Column
For i% = 1 To n%
With .Cells(1, i%)
w! = .EntireColumn.ColumnWidth
.EntireColumn.ColumnWidth = w! + 4
.HorizontalAlignment = xlCenter
.Interior.Color = intColor&
.Font.Bold = True
End With
Next i%
End With
' FREEZE PANES
With xlApp.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
msg$ = vbNullString
procDone:
Set wks = Nothing
Set wkbk = Nothing
Set xlApp = Nothing
dmwExport = msg$
Exit Function
errHandler:
msg$ = _
Err.Number & ": " & Err.Description
Resume procDone
End Function
Instead of opening / updating / saving the resulting workbook, why don't you update the source query so only the data you need are exported ?
Or even better, Power BI to the rescue:
Throw this VBA to garbage, Create a new workbook that will query the database.
No more VBA involved, no more troubles.
You can also use VBA/VB script to copy the whole sheet with expected columns inside, and then do the export.