loop not changing values

[smakatura]

I want to look at a list of dated files and open them in order. below is my code.

Dim loopcounter As Integer
Dim rptdate As Date
Dim datafilename As String
Dim datafilelocation As String
Dim fulldatafilename As String
Dim fulldatafilelocation As String
 
   rptdate = Application.InputBox("Enter date.", Title:="Date Entry", Type:=1)
 
    sourcedocument = "JOBS Weekly Template.xlsm"
    datafilelocation = "C:\Users\smakatura\Documents\current project\jackie report\call logs1\"
    datafilename = "JOBS Center Call Log Master "
    fulldatafilename = datafilename & Format(rptdate, "mm-dd-yy") & ".xlsx"
    fulldatafilelocation = datafilelocation & fulldatafilename
 
 
DO
     Workbooks.Open Filename:=fulldatafilelocation
     loopcounter = loopcounter + 1
     rptdate = rptdate + 1
     MsgBox (rptdate)
Loop Until loopcounter = 3

before the loop: the rptdate is 09-26-11(entered value into input box)
first loop: opens the file with the date 09-26-11 and says message box says rptdate is 9-27-11
second loop: file with date 09-26-11 opens instead of 09-27-11 and msgbox says that rpt date is now 9-28-11

basically msgbox is saying correct 'new rptdate' but the file opening is stil the first rptdate...not the the updated rptdate.

 

[rafaelspeixoto]

You are not redefining the fine names in each loop run. Try this change:

Dim loopcounter As Integer
Dim rptdate As Date
Dim datafilename As String
Dim datafilelocation As String
Dim fulldatafilename As String
Dim fulldatafilelocation As String
 
   rptdate = Application.InputBox("Enter date.", Title:="Date Entry", Type:=1)
 
    sourcedocument = "JOBS Weekly Template.xlsm"
    datafilelocation = "C:\Users\smakatura\Documents\current project\jackie report\call logs1\"
    datafilename = "JOBS Center Call Log Master "

 
 
DO
     fulldatafilename = datafilename & Format(rptdate, "mm-dd-yy") & ".xlsx"
     fulldatafilelocation = datafilelocation & fulldatafilename
     Workbooks.Open Filename:=fulldatafilelocation
     loopcounter = loopcounter + 1
     rptdate = rptdate + 1

     MsgBox (rptdate)
Loop Until loopcounter = 3

 

[smakatura]

that worked perfiectly. I just moved the file definition lines like you suggested and it is great. I figured it was something simple like that.