Here is my code. Using Office 2007 on this project
Code:
Private Sub cmdRunExp_Click()
'Define Variables
Dim q1 As String
Dim thePath As String
Dim sReg As String
Dim fileName As String
Me.Requery
'Check to see if Query has been selected. If not selected, messagebox and opportunity to try again
If IsNull(Me.cmbQuery) Or Me.cmbQuery.Value = "" Then
Beep
MsgBox "Select Query"
Exit Sub
End If
'Set Variables and check to see if a Region has been selected. If not, the report will indicate Company-Wide vs. Region
q1 = Me.cmbQuery
If IsNull(Me.cmbRegion) Then
sReg = "Company-Wide"
Else
sReg = Me.cmbRegion.Column(1)
End If
fileName = Me.cmbQuery
thePath = "G:\FSC\West Southwest\Support\Rebates\2013 P04\Meeting with Alan\Data Output from Access Test\"
'Run Query and Export
Dim DB As Database
Dim XLApp, WB As Object
Set DB = CurrentDb
DoCmd.TransferSpreadsheet acExport, 10, q1, thePath & sReg & fileName
'Open XL file and set Automatic Backup to False
Set XLApp = CreateObject("Excel.Application")
Set WB = XLApp.Workbooks.Open(thePath & sReg & fileName)
XLApp.DisplayAlerts = False
WB.SaveAs fileName:=thePath & sReg & fileName, CreateBackup:=False
WB.Close SaveChanges:=True
XLApp.DisplayAlerts = True
DB.Close
Set DB = Nothing
'Notify User that the file has been exported
Beep
Beep
MsgBox "Your Report for the " & sReg & " Region has been exported"
End Sub
The issue is I am getting an error message on the line highlighted in RED.
The message reads:
'G:\FSC\West Southwest\Support\Rebates\2013 P04\Meeting with Alan\Data Output from Access Test\WestRpt-Null Program Names' cannot be found. Check
your spelling, or try a different path.
In the code above the string thePath is used to export the file and then attempt to open it. This same code worked for me in another db, so I am a bit at a loss here. The file exports perfectly. The issue is trying to open it and Excel says it cannot be found. Your thoughts on this.
Thanks
Alan