refresh excell

J

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.
 
J

John

Ben,

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

Ben said:
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
Click to expand...
 
E

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
 
Top