Error in Do While strF <> vbNullString Loop

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>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,214,983
Messages
6,122,582
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top