Small Macro Tweaks and Error Handler in VBA

[zaska]

Hello,

I am having the following problems while executing the code. I kindly request support for rectifying the same.

1. Error Handling : If the specified input file doesn't exist then the VBA code is generating error.

2. I want to speed up this VBA using application.screenupdating = False. But i don't know where exactly i should use it in the code.

3. I want to close the Opened .Csv files before running into the next loop.

I have almost 5000 files to be processed .

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

 Dim strPath As String, otrPath 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
        otrPath = Endpath & buffer & ".txt"
        startDate = startDate + 1 'add one day
        'Your code
 
    Workbooks.Open Filename:=(strPath)
    Columns("I:I").Delete Shift:=xlToLeft
    Rows("1:1").Delete Shift:=xlUp
    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
    Columns("C:F").NumberFormat = "0.00"
    Columns("B:B").NumberFormat = "yyyymmdd"
    ActiveWorkbook.SaveAs Filename:=(otrPath), CreateBackup:=False

    Loop
    
End Sub


Thank you

 

[wigi]

Hello,

1. Error Handling : If the specified input file doesn't exist then the VBA code is generating error.

2. I want to speed up this VBA using application.screenupdating = False. But i don't know where exactly i should use it in the code.

3. I want to close the Opened .Csv files before running into the next loop.

1. If Len(Dir(strPath))>0 Then ...

If this is true, you know the file exists.

2. Just before the first line of code where you will interact with the user interface, like opening files for instance. If you are assigning values to variables, this setting has no use.

3. ActiveWorkbook.Close

 

[zaska]

Sir,

I made the suggested changes. I am getting Close, Save Dialog box everytime . How to avoid this?

Is there anyway to stop this code in between , because it will take lot of time to process 5000 files.

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

 Dim strPath As String, otrPath 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
        otrPath = Endpath & buffer & ".txt"
        startDate = startDate + 1 'add one day
        'Your code
    Application.screenupdating = false    
    If Len(Dir(strPath)) > 0 Then
    Workbooks.Open Filename:=(strPath)
    Columns("I:I").Delete Shift:=xlToLeft
    Rows("1:1").Delete Shift:=xlUp
    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
    Columns("C:F").NumberFormat = "0.00"
    Columns("B:B").NumberFormat = "yyyymmdd"
    ActiveWorkbook.SaveAs Filename:=(otrPath), CreateBackup:=False
    ActiveWorkbook.Close
    End if

    Loop
    
End Sub

Thank you

 

[Hermanito]

Add the lines in red to your code:

    [COLOR="Red"][B]Application.DisplayAlerts = False[/B][/COLOR]
    ActiveWorkbook.SaveAs Filename:=(otrPath), CreateBackup:=False
    ActiveWorkbook.Close
    [COLOR="Red"][B]Application.DisplayAlerts = True[/B][/COLOR]

 

[zaska]

Hi ,


Thank you for the kind help.