refresh excell

[John]

I have vba that will open and refresh an excel where the name of the excel
file never changes after the access runs. But I would like to know how to
open a excel file where the file name changes. The file name has yesterdays
date at the end. Example Summary_050509.xls

Here is the code that I am currently using that opens and refreshes the file
where the name does not change:

Function refreshClosed()

Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")

Dim workBook As Object
Dim workSheet As Object

appExcel.Application.Visible = True
appExcel.Workbooks.Open "D:\UPSDATA\SF_Report\ServiceSummary.xls"

Set workSheet = appExcel.Sheets("SUMMARY")

appExcel.Run ("refall")

appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = True
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing

End Function

Thank you for any help I can get.

 

[John]

Ben,

Thanks so much....it works great!!!

John,

To do that, I would do below.

Ben


Function refreshClosed()

Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")

Dim workBook As Object
Dim workSheet As Object

'--------------------------------
dim strFile as string
dim strFullPath as string

strFile = "Summary_" & format(now()-1, "mmddyyyy")
strFullPath = "D:\UPSDATA\SF_Report\" & strFile
'--------------------------------

appExcel.Application.Visible = True

'notice the change
appExcel.Workbooks.Open strFullPath

Set workSheet = appExcel.Sheets("SUMMARY")

appExcel.Run ("refall")

appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = True
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing

End Function