Looping Help needed for a Herculus Task

[zaska]

Hi all,

I recorded and tweaked the below macro to process a .csv file which is named as " 2000-06-14_BSECM ". I have about 5000 .csv files to process the same thing.

I kindly request any one to help me looping the below code so that i could avoid the Hercules task of processing each .csv file.

Except the Date of the .csv the rest of the .csv name remains constant.

Is it possible to have a calender in excel sheet so that i can choose the dates to process the .csv files.

I would also like to know whether this code can be further tweaked so that the loop runs faster.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
    ChDir "C:\Users\pc\Desktop\BSE"
    Workbooks.Open Filename:="C:\Users\pc\Desktop\BSE\2010-06-14_BSECM.csv"
    Columns("B:B").Insert Shift:=xlToRight
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("B:B").Delete Shift:=xlToLeft
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\pc\Desktop\BSE\2010-06-14_BSECM.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveWindow.Close
End Sub

Thank you

 

[kpark91]

Hi, I think you would need to explain more about your filenames.
like from which date to which date are in the folder?
Are they all in the same folder?
What are their names? (like a rule, MM_DD_YYY_Blarg.csv?)

and where should the csv files be saved to? or added to?
Does each csv file represent each worksheet of the same workbook?
or does it just represent each workbook,..

 

[zaska]

Hi,

Let me explain further

File Name Format : "YYYY-MM-DD_BSECM.csv "
All the Files are stored in the Same Directory : NewFolder in D Drive ( I can change the path as required)
Each .Csv file should be saved with the same name after processing in a text format or .csv format

I can change the path of Output folder as required.

Totally I have 5000 .csv files. Starting from date " 2000-01-01 "


Thank you for the reply and any help in this regard will save me a lot of time.

 

[zaska]

Can anyone kindly look at my request

Thanks in advance

 

[kpark91]

Hello zaska,
Here is the snippet of code that changes the files you want to open.
If any of the file names are missing in the directory, I believe the code will error out and stop executing so you must add an error handler.
I have not yet had a chance to incorporate this code into yours.

    Dim strPath As String, startPath As String, i As Long, buffer As String, startDate As Date
    
    startPath = "C:\Users\pc\Desktop\BSE\" 'current directory
    startDate = "1/1/2000" 'DD/MM/YYYY
    
    Do While buffer <> "2011-06-04"
        buffer = Format$(startDate, "YYYY-MM-DD")
        strPath = startPath & buffer & "_BSECM.csv"  'full path of the file you want to open
        startDate = startDate + 1 'add one day
        'Your code
    Loop

 

[zaska]

Sir,

I have made the following changes & i kindly request to correct the code.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'

 Dim strPath As String, startPath As String, i As Long, buffer As String, startDate As Date
    
    startPath = "E:\BSE\CM\" 'current directory
    startDate = "14/06/2010" 'DD/MM/YYYY
    
    Do While buffer <> "2011-06-04"
        buffer = Format$(startDate, "YYYY-MM-DD")
        strPath = startPath & buffer & "_BSECM.csv"  'full path of the file you want to open
        startDate = startDate + 1 'add one day
        'Your code
    Columns("B:B").Insert Shift:=xlToRight
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
[COLOR=Red]    ActiveWorkbook.SaveAs Filename:="E:\BSE\OP\startPath & buffer & "_BSECM.csv", FileFormat:=txt,
        CreateBackup:=False
    ActiveWindow.Close[/COLOR]
      Loop
    
End Sub

Thank you

 

[zaska]

Hi,

Can anyone help me in correcting the syntax errors in the below code

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'

 Dim strPath As String, startPath As String, Endpath As String, i As Long, buffer As String, startDate As Date
    
    startPath = "E:\BSE\CM\" 'current directory
    Endpath = "E:\BSE\OP\"   'output path
    startDate = "14/06/2010" 'DD/MM/YYYY
    
    Do While buffer <> "2011-06-04"
        buffer = Format$(startDate, "YYYY-MM-DD")
        strPath = startPath & buffer & "_BSECM.csv"  'full path of the file you want to open
        startDate = startDate + 1 'add one day
        'Your code
        
    How  to Open the Above file ?
    
    Columns("B:B").Insert Shift:=xlToRight
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= Endpath & ".txt", FileFormat:=txt,
        CreateBackup:=False
    ActiveWindow.Close

    Loop
    
End Sub

thank u

 

[zaska]

Hi,

I thank you for the kind help. Finally i am able to finish the work.

Regards,

Zaska