OK there seems to be two issues. The first for some reason it won't always go to it's path not sure why.
The second is when it does export it opens two excel spreadsheets. One with just the header and the other has header with data. I cannot spot the error.
Sub ExportToExcel(strSQL As String, FilePath As String, sFileName As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ExportDate As String
Dim UserName As String
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
UserName = getOSUserName
ExportDate = Format(Date, "mm_dd_yyyy") '& " " & Format(Time, "HH:MM:SS")
ExportDate = Replace(ExportDate, ":", " ")
sFileName = "MyTable.xls"
' FilePath = "U:" '"\\fltottwnt345\home"
' sFileName = sFileName & "_" & UserName & "_" & ExportDate & ".xls"
FilePath = sFileName
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1) 'Work with the first worksheet
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
'IRec = rs.RecordCount
rs.MoveFirst
For iCols = 0 To rs.Fields.count - 1
oSheet.Cells(5, iCols + 1).value = rs.Fields(iCols).Name
Next
With oSheet.Range(oSheet.Cells(5, 1), oSheet.Cells(5, rs.Fields.count))
.Font.Bold = True
.Font.ColorIndex = 5 '2
.Interior.ColorIndex = 1
.HorizontalAlignment = xlCenter
End With
Dim FieldName As String
Dim row As Integer
row = 6
Do While Not rs.EOF
For iCols = 1 To rs.Fields.count - 1
FieldName = rs.Fields(iCols).Name
oSheet.Cells(row, iCols).value = rs.Fields(iCols - 1)
Next
row = row + 1
rs.MoveNext
Loop
oExcel.Workbooks.Open (FilePath)
oExcel.visible = True
'Set oSheet = Nothing 'disconnect from the Worksheet
'oBook.SaveAs sFileName 'Save (and disconnect from) the Workbook
oBook.Close
Set oSheet = Nothing 'disconnect from the Worksheet
Set oBook = Nothing
oExcel.Quit 'Close (and disconnect from) Excel
Set oExcel = Nothing
rs.Close
Set rs = Nothing
End Sub