Code won't open excel workbook to write query to, if I mode to shared drive.

[Jhail83]

It says it can't find it. here is my code. It works fine on my local machine.

Option Compare Database
Public Sub btn_Export_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strComputer As String
Dim strProcessKill As String
Dim objWMIService As Object
Dim colProcess As Object
Dim intYear As Integer

'Close all instances of excel to make sure that template we are writing to, or file we are saving to are not open.
strComputer = "."
strProcessKill = "'excel.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
objProcess.Terminate
Next
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit
'End of WMI Example of a Kill Process

'Open Excel Template and write to each tab we need to insert data to.
Set objXL = CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\DEA Budget Template")
'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("Budget Detail")

Set rst = CurrentDb.OpenRecordset("qry_AllDivBudgetOutput")

xlWS.Range("B3").CopyFromRecordset rst

'Sort Column E then B

'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("Chart of Accounts")

Set rst = CurrentDb.OpenRecordset("qry_Accounts")

xlWS.Range("B3").CopyFromRecordset rst

'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("203700 per Store")

Set rst = CurrentDb.OpenRecordset("qry_203700 per Store")

xlWS.Range("B3").CopyFromRecordset rst

'---------------------------------------------------------------------------------------------------------------------

xlWB.RefreshAll

On Error GoTo ErrorHandle
intYear = (Year(Date) + 1)

xlWB.SaveAs CurrentProject.Path & "\" & intYear & " DEA Budget Report.xlsx"

MsgBox "Report saved as " & CurrentProject.Path & "\" & intYear & " DEA Budget Report.xlsx"

xlWB.Application.Visible = True

Set fd = Nothing
rst.Close
Set rst1 = Nothing
Exit Sub

ErrorHandle:
'Close all instances of excel to make sure that template we are writing to, or file we are saving to are not open.
strComputer = "."
strProcessKill = "'excel.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
objProcess.Terminate
Next

Set fd = Nothing
rst.Close
Set rst1 = Nothing

End Sub

Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\DEA Budget Template")
(If I move the folder containing the DB to the shared drive, it says it can't find it, and lists the correct path and everything. Do I need to harcode this in? Kind of a pain if it gets moved or the folder gets renamed on the S drive if that's the case.)

[Jhail83]

changed
Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\DEA Budget Template") to

Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\DEA Budget Template.xlsx")

works now. i tried everything else! sorry for the post

[vincent-leeway]

So it was a excel file, which contained your data.