| re: Export Access table to Excel and format wksht using macro
Instead of putting the macro in Excel, I do this in Access. Try this
(air code, pulled from
some code I have used that works with a few changes):
Public Sub SendDataToExcelAndFormat()
Dim strFile as String
'Your code begins here
strFile = [Forms]![frm_Regional ELC]![txtItemDescription]
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_delete FCL"
DoCmd.OpenQuery "qry_temp FCL"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, , _
"tbl_temp FCL", _
FileName, True
'Your code ends here
Call FormatDataInExcel strFile
End Sub
Private Sub FormatDataInExcel(strExcelWorkbook As String)
On Error Goto Err_FormatDataInExcel
Dim exl as Object
'Attempt to assign an Excel application that already
'exists to object variable
Set exl = GetObject(,"Excel.Application")
'Open the workbook to format
exl.Workbooks.Open Filename:=strExcelWorkbook
'Your recorded macro code begins here
'Your recorded macro code code ends here
'Save changes
exl.ActiveWorkbook.Save
'Show the Excel application
exl.Application.Visible = True
Exit_FormatDataInExcel:
On Error Resume Next
Set exl = Nothing
Exit Sub
Err_FormatDataInExcel:
With err
Select Case .Number
Case 429
'Excel object does not exist, create it
Set exl = CreateObject("Excel.Application")
Resume Next
Case Else
Msgbox "An unhandled exception has occurred." & vbCrLf _
"Error Number: " & .Number & vbCrLf _
"Error Description: " & .Description, _
vbCritical
End Select
End With
Resume Exit_FormatDataInExcel
End Sub
Post back if you need clarification or get an error.
Johnny
| 
