I have an accdb that was working fine for years and we somehow messed something up. We have a form. It has a button. It calls a function of vba code. The code dispays messages, run queries that create a table and finally CALL ANOTHER function that open an excel file from a shared drive location, refreshes the pivots and saves the file to another share location.

This has worked flawless for 15 years. Suddenly it fails on the call to refresh sheets. REFRESHSHEETS is passed two paramters: TemplateBook and NewBook. It does not seem to know the values for these parameters.

The module opens with this:

Option Compare Database
Public TemplateBook As String
Public NewBook As String
Option Explicit

The form button calls this:

Private Sub Command178_Click()
Dim db As Database
Set db = CurrentDb
DoCmd.SetWarnings False
Dim STYLE As Variant
Dim response As Integer
STYLE = vbYesNoCancel + vbDefaultButton2
MsgBox "Refreshing Report and wrinting to X drive..."
TemplateBook = "G:ReportTemplatesTeleDOMdata.xls"
NewBook = "X:ReportsMISCTeleDOMdata.xls"
RefreshSheets
MsgBox "Done! "
DoCmd.SetWarnings False
End Sub

The REFRESHSHEETS has this code:
Function RefreshSheets()
Dim xcelapp As Object
Dim pc As Object
Dim wks As Object
Dim qt As Object
‘Delete Existing Report
If Dir(NewBook) = “” Then
‘Do Nothing
Else
Kill NewBook
Call Timr(10) ‘waits 10 secs
End If

 Set xcelapp = CreateObject("Excel.Application")
 xcelapp.Workbooks.Open TemplateBook
xcelapp.DisplayAlerts = False
'Refresh All Workbook Sheets
 xcelapp.activeworkbook.refreshall
 'Force a pause here
 Call Timr(10)   'waits 10 secs
'Save to appropriate location
 xcelapp.activeworkbook.SaveAs FileName:=NewBook
 Call Timr(10)   'waits 10 secs
xcelapp.activeworkbook.Close
Set xcelapp = Nothing      'nessecary to prevent hanging Excelprocesses on your machine
End Function

This worked fine for years. We have numerous form buttons similar that no longer work. We have another db that performs similar tasks—it works fine. Something got messed up in this db.

Here is the error:

Run-Time error ’53’ File not found:

Click Debug: Kill (NewBook) is highlighted.

If I comment out:
If Dir(NewBook) = “” Then
‘Do Nothing
Else
Kill NewBook
Call Timr(10) ‘waits 10 secs
End If

then it simply fails on the first occurence of where templatebook is refrenced.

IF I COPY:
TemplateBook = “G:ReportTemplatesTeleDOMdata.xls”
NewBook = “X:ReportsMISCTeleDOMdata.xls”

INTO the REFRESHEETS function..it works fine. It seems that somehow the parameter values for NewBook and TemplateBook are not being passed.

Ideas?

not sure what to do…