Macro to Open Daily Report

[KurtB]

Every day a report is posted online that I need to open; the report is
titled 080906_rpts_open.csv. Each morning I need to open the previous
days report or reports from over the weekend. I'm looking to write a
macro that would automatically open the report or reports based on how
many days I need to go back.
Thanks!

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim dCtr As Long
Dim StartDate As Long
Dim MaxDates As Long
Dim myPath As String
Dim myStr As String
Dim UseThisFile As String
Dim TestStr As String
Dim CSVWks As Worksheet

'start with yesterday?
StartDate = Date - 1

'don't look for more than this number days
MaxDates = 15

'change the path for the CSV files here
myPath = "C:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

UseThisFile = ""
For dCtr = StartDate To StartDate - MaxDates Step -1
myStr = myPath & Format(dCtr, "yymmdd") & "_rpts_open.csv"
TestStr = ""
On Error Resume Next
TestStr = Dir(myStr)
On Error GoTo 0

If TestStr = "" Then
'keep looking
Else
UseThisFile = myStr
Exit For
End If
Next dCtr

If UseThisFile = "" Then
MsgBox "No files found"
Else
Set CSVWks = Workbooks.Open(Filename:=myStr).Worksheets(1)
With CSVWks
'do lots of formatting???
End With
MsgBox "It's open!"
End If

End Sub

 

[KurtB]

Thanks... And if I want to pull this off a website What do I need to
change?