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!

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 wrote:

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

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


On Sep 9, 8:55*am, Dave Peterson wrote:
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 wrote:

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