Gobi loganathan
New Member
- Joined
- Jul 18, 2017
- Messages
- 2
When I run a macro , macro has to open work book in specified folder and apply formula then save as text file.once compled goto the next file in the folder but when I run the macro its showing error "Run-time error '5': Invalid procedure call or argument" after first file is processed
error in - strF = Dir()
macro is not looking for the second file in the folder
i am new to vba and not aware where is the issue.
Request you to help..
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Macro4()
Dim n As Integer
Dim filename As String
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet
Dim xWB As Workbook
'Edit this declaration to your folder name
strP = "D:\TEST\INPUT" 'change for the path of your folder
Do While strF <> vbNullString
Set wb = Workbooks.Open(strP & "" & strF)
ActiveSheet.Cells.Select
Selection.Copy
Windows("My file.xlsm").Activate
ActiveSheet.Paste
Columns("Y:Y").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("Y1").Select
ActiveCell.FormulaR1C1 = "HEADER"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "HEADER"
Range("Y3").Select
ActiveCell.FormulaR1C1 = _
"formula"
With Range("Y:Y").SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "my formula"
End With
Range("y3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
n = 1
Do
filename = "D:\GOBI" & "TEST FILE" & Format(IIf(n = 1, "000001", n), "000000") & ".txt"
n = n + 1
Loop Until Dir(filename) = ""
ActiveWorkbook.SaveAs filename, FileFormat:= _
xlText, CreateBackup:=False
ActiveWorkbook.Close False
For Each xWB In Application.Workbooks
If Not (xWB Is Application.ActiveWorkbook) Then
xWB.Close False
End If
Next
Application.ScreenUpdating = True
strF = Dir()
Loop
End Sub</code>
error in - strF = Dir()
macro is not looking for the second file in the folder
i am new to vba and not aware where is the issue.
Request you to help..
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Macro4()
Dim n As Integer
Dim filename As String
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet
Dim xWB As Workbook
'Edit this declaration to your folder name
strP = "D:\TEST\INPUT" 'change for the path of your folder
Do While strF <> vbNullString
Set wb = Workbooks.Open(strP & "" & strF)
ActiveSheet.Cells.Select
Selection.Copy
Windows("My file.xlsm").Activate
ActiveSheet.Paste
Columns("Y:Y").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("Y1").Select
ActiveCell.FormulaR1C1 = "HEADER"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "HEADER"
Range("Y3").Select
ActiveCell.FormulaR1C1 = _
"formula"
With Range("Y:Y").SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "my formula"
End With
Range("y3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
n = 1
Do
filename = "D:\GOBI" & "TEST FILE" & Format(IIf(n = 1, "000001", n), "000000") & ".txt"
n = n + 1
Loop Until Dir(filename) = ""
ActiveWorkbook.SaveAs filename, FileFormat:= _
xlText, CreateBackup:=False
ActiveWorkbook.Close False
For Each xWB In Application.Workbooks
If Not (xWB Is Application.ActiveWorkbook) Then
xWB.Close False
End If
Next
Application.ScreenUpdating = True
strF = Dir()
Loop
End Sub</code>