VBA Lesson 07 - export a file to Excel

englishspacedog YouTube tutorial from Canada
http://www.herostratus.ca/PC.htm

My new VBA videos are in this playlist:
   • My New VBA Lessons  

Microsoft Access VBA - export a file to Excel
===========================================

Here's the VBA code ...


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''' BUTTON 3 '''''''''''''''''''''''''''''''
''' EXPORT THE LIST TO EXCEL AS XXXXXXXXXX.XLS ''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim outputFileName As String

Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String


outputFileName = CurrentProject.Path & "\XXXXXXXX.xls"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
"XXXXXXXX", _
outputFileName, _
True ' HasFieldNames

MsgBox "Data has been exported to " & outputFileName


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = outputFileName


' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit