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

 

[ErezM via AccessMonster.com]

hi
if the proccess is always the same (you'll always be looking for an excel
file with yesterday's date as it's name extension), then you can chnage the
line:

appExcel.Workbooks.Open "D:\UPSDATA\SF_Report\ServiceSummary.xls"

to

Dim Date1 As Date
Date1=DateAdd("D',-1,Date())
appExcel.Workbooks.Open "D:\UPSDATA\SF_Report\ServiceSummary_" & Format(Month
(Date1),"00") & Format(Day(Date1),"00") & Right(Year(Date1),2) & ".xls"

(change the day/month/year sequence to your needs!)

Erez