Error Message -- Path not found.

[alansidman]

Here is my code. Using Office 2007 on this project

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

[pbaldy]

Try putting

DoEvents

between the export and that line to make sure the export gets completed.

[alansidman]

Thanks for the reply Paul. Did not make a difference. This has me really confused as I used same concept last week and it worked just perfectly in another db.

[pbaldy]

And the spreadsheet is getting created in the desired folder?

[alansidman]

Yes. I just solved it. Simple oversight and then bang! it hit me!
Added this to the fileName:

fileName = me.cmbQuery & ".xlsx"

In previous db, had given file name a specific name in the Transferspreadsheet instead of variable. Big Sigh!

Thanks Paul.

Alan

[pbaldy]

Glad you got it sorted out.