Error in Opening File while trying to loop

[zaska]

Hi,

I got Error 1004 while trying to execute the following loop. Actually the Input file Name is in the format "foDDMMMYYYYbhav.csv" i am unable to refer the same format in VBA

Sample code of the loop

Sub test()

 Dim strPath As String, startPath As String, Endpath As String, i As Long, buffer As String, startDate As Date
    
    startPath = "E:\Macros\Input\" 'current directory
    Endpath = "E:\Macros\Output\"   'output path
   [COLOR=Red] startDate = "01-JUL-2011" 'DDMMMYYYY
    [/COLOR]
    Do While buffer <> "2011-07-02"
        buffer = Format$(startDate, "DDMMYYYY")
        [COLOR=Red]strPath = startPath & "fo" & buffer & "bhav.csv"  [/COLOR] 'full path of the file you want to open
        startDate = startDate + 1 'add one day
       
Workbooks.Open Filename:=(strPath)

Kindly help me how do i define the full path of the file and also to refer the Start Date and Buffer Date in Sheet1 of NSE Converter.xls

Thank you

 

[Ruddles]

Hi,

I got Error 1004 while trying to execute the following loop. Actually the Input file Name is in the format "foDDMMMYYYYbhav.csv" i am unable to refer the same format in VBA

    Do While buffer <> "2011-07-02"
        buffer = Format$(startDate, "DD[COLOR=blue][B][U]MM[/U][/B][/COLOR]YYYY")
        [COLOR=red]strPath = startPath & "fo" & buffer & "bhav.csv"  [/COLOR] 'full path of the file you want to open
        startDate = startDate + 1 'add one day
 
Workbooks.Open Filename:=(strPath)

Is that it?

 

[zaska]

OOPS thank you..that was a silly mistake..

After working with VBA i figured out the following code for looping it. However i have the following Errors.

The first opened file i.e fo01JUN2011 is not closing

I also want to know whether i can choose the Start date and End Date from Cells in NSE Converter.xls ( Calendar Popup)

Kindly pointout and help me to rectify the errors.

Sub test()

 Dim strPath As String, startPath As String, Endpath As String, i As Long, buffer As String, startDate As Date, LR As Long
     
    startPath = "E:\Macros\Input\" 'current directory
    Endpath = "E:\Macros\Output\"   'output path
    startDate = "01-JUN-2011" 'DDMMMYYYY
    
    Do While buffer <> "02-JUL-2011"
        buffer = Format$(startDate, "DDMMMYYYY")
        strPath = startPath & "fo" & buffer & "bhav.csv"   'full path of the file you want to open
        otrpath = Endpath & "fo" & buffer & "bhav.txt"
        startDate = startDate + 1 'add one day
         
If Len(Dir(strPath)) > 0 Then
Workbooks.Open Filename:=(strPath)
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C").Insert
    Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)"
For i = 2 To LR
Range("G" & i).Value = Range("I" & i).Value
Next i
Columns("I").Delete
Rows("1").Delete
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=(otrpath), CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
Loop

 End Sub

Thanks