bala kumaran
asked on
DIR issue
myfile = Dir(ippath & "*.xlsm")
myfile1 = Dir(ippath1 & "*.xlsx")
Do While myfile <> ""
Do While myfile1 <> ""
loop
loop
end sub
I used 2 DIR, like the above, my issue was, one finish myfile1 DIR working inside the myfile DIR, it will not get myfile DIR another file, wrongly go to myfile1 DIR
myfile1 = Dir(ippath1 & "*.xlsx")
Do While myfile <> ""
Do While myfile1 <> ""
loop
loop
end sub
I used 2 DIR, like the above, my issue was, one finish myfile1 DIR working inside the myfile DIR, it will not get myfile DIR another file, wrongly go to myfile1 DIR
what is your complete code? if possible can you please post your complete sub routine?
ASKER
Public Sub Insert()
Dim myfile As String
Dim myfile1 As Variant
ippath = Sheet1.TextBox1.Text
ippath1 = Sheet1.TextBox2.Text
myfile = Dir(ippath & "*.xlsm")
myfile1 = Dir(ippath1 & "*.xlsx")
Do While myfile <> ""
Workbooks.Open FileName:=ippath & myfile, UpdateLinks:=True
Application.DisplayAlerts = False
Sheets("Minimum Accounts Work Tracker").Select
If Sheet1.OptionButton1.Value = True Then
Rows("4:4").Select
Cells.Find(What:="low").Ac tivate
ActiveCell.Offset(0, -2).Select
ActiveCell = Sheet1.TextBox6.Text
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R [1]C,1),"" Sun"",""Mo n"",""Tue" ",""Wed"", ""Thu"","" Fri""),""" ")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio rity!C1:C4 ,4,0),"""" )"
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows .Count
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
If Sheet1.OptionButton3.Value = True Then
Do While myfile1 <> ""
Workbooks.Open FileName:=ippath1 & myfile1, UpdateLinks:=True
m1 = ActiveWorkbook.Name
Range("a2:l" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.Copy
Windows(myfile).Activate
Sheets("Priority").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A:A").Select
Selection.TextToColumns DataType:=xlDelimited
Range("a1").Select
Sheets("Minimum Accounts Work Tracker").Select
Range("bd4").Select
If ActiveCell.Value = "" Then
Rows("4:4").Select
Cells.Find(What:="low").Ac tivate
ActiveCell.Offset(0, -2).Select
ActiveCell = Sheet1.TextBox6.Text
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R [1]C,1),"" Sun"",""Mo n"",""Tue" ",""Wed"", ""Thu"","" Fri""),""" ")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio rity!C1:C4 ,4,0),"""" )"
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows .Count
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Else
Rows("4:4").Select
Cells.Find(What:="low").Ac tivate
ActiveCell.Offset(0, -1).Select
Columns(ActiveCell.Column) .Insert
ActiveCell.formula = Split(myfile1, ".")(0)
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R [1]C,1),"" Sun"",""Mo n"",""Tue" ",""Wed"", ""Thu"","" Fri""),""" ")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio rity!C1:C4 ,4,0),"""" )"
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows .Count
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Windows(m1).Close
myfile1 = Dir()
Loop
End If
If Sheet1.OptionButton2.Value = True Or Sheet1.OptionButton4.Value = True Then
Rows("4:4").Select
Cells.Find(What:="low").Ac tivate
ActiveCell.Offset(0, -1).Select
Columns(ActiveCell.Column) .Insert
ActiveCell = Sheet1.TextBox6.Text
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R [1]C,1),"" Sun"",""Mo n"",""Tue" ",""Wed"", ""Thu"","" Fri""),""" ")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio rity!C1:C4 ,4,0),"""" )"
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows .Count
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Rows("4:4").Select
Cells.Find(What:="low").Ac tivate
Addrs1 = Split(ActiveCell.Address, "$")(1)
ActiveCell.Offset(0, 8).Select
Addrs2 = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows .Count
ActiveCell.Offset(-3, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, -8)).Select
Selection.Copy
Range(Addrs1 & "5:" & Addrs2 & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 4).Select
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.TextToColumns DataType:=xlDelimited
Range("f1").Select
Selection.Copy
Range("f5:f" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Replace What:="1/0/1900", Replacement:=""
Range("h1").Select
Selection.Copy
Range("h5:h" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("4:4").Select
Cells.Find(What:="low").Ac tivate
ActiveCell.Offset(1, 9).Select
Addrs3 = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows .Count
ActiveCell.FormulaR1C1 = "=IF(R[0]C8=""Not Worked"","""",TODAY()-R[0] C8)"
Selection.Copy
Range(Addrs3 & "5:" & Addrs3 & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
If Sheet1.OptionButton1.Value = True Or Sheet1.OptionButton2.Value = True Then
Range("d1").Select
Selection.ClearContents
Range("G2").Select
Selection.Copy
Range("g5:g" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
End If
If Sheet1.OptionButton3.Value = True Then
Range("d1").Select
ActiveCell = Sheet1.TextBox6.Text
Range("G2").Select
Selection.Copy
Range("g5:g" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
End If
If Sheet1.OptionButton4.Value = True Then
Range("G4").Activate
Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("a4:cz" & ActiveSheet.UsedRange.Rows .Count).Au toFilter Field:=7, Criteria1:=Array( _
"Cleared", "Required", "Required 2", "="), Operator:=xlFilterValues
Range("G1").Select
Selection.Copy
Range("g5:g" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
End If
Sheets("schools").Select
Range("f1:h1").Select
Selection.Copy
Range("f5:h" & ActiveSheet.UsedRange.Rows .Count).Se lect
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("1:1").Select
Selection.EntireRow.Hidden = True
Range("f5").Select
Sheets("Minimum Accounts Work Tracker").Select
Rows("1:1").Select
Selection.EntireRow.Hidden = True
Range("f5").Select
Sheets("Progress Table").Select
Range("A1").Select
Sheets("FSCM RawData").Visible = False
Sheets("2nd BPO").Visible = False
Sheets("Priority").Visible = False
Sheets("LIST").Visible = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("a1").Select
myfile = Dir()
Loop
'Call Binary
End Sub
Dim myfile As String
Dim myfile1 As Variant
ippath = Sheet1.TextBox1.Text
ippath1 = Sheet1.TextBox2.Text
myfile = Dir(ippath & "*.xlsm")
myfile1 = Dir(ippath1 & "*.xlsx")
Do While myfile <> ""
Workbooks.Open FileName:=ippath & myfile, UpdateLinks:=True
Application.DisplayAlerts = False
Sheets("Minimum Accounts Work Tracker").Select
If Sheet1.OptionButton1.Value
Rows("4:4").Select
Cells.Find(What:="low").Ac
ActiveCell.Offset(0, -2).Select
ActiveCell = Sheet1.TextBox6.Text
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
If Sheet1.OptionButton3.Value
Do While myfile1 <> ""
Workbooks.Open FileName:=ippath1 & myfile1, UpdateLinks:=True
m1 = ActiveWorkbook.Name
Range("a2:l" & ActiveSheet.UsedRange.Rows
Selection.Copy
Windows(myfile).Activate
Sheets("Priority").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A:A").Select
Selection.TextToColumns DataType:=xlDelimited
Range("a1").Select
Sheets("Minimum Accounts Work Tracker").Select
Range("bd4").Select
If ActiveCell.Value = "" Then
Rows("4:4").Select
Cells.Find(What:="low").Ac
ActiveCell.Offset(0, -2).Select
ActiveCell = Sheet1.TextBox6.Text
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Else
Rows("4:4").Select
Cells.Find(What:="low").Ac
ActiveCell.Offset(0, -1).Select
Columns(ActiveCell.Column)
ActiveCell.formula = Split(myfile1, ".")(0)
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Windows(m1).Close
myfile1 = Dir()
Loop
End If
If Sheet1.OptionButton2.Value
Rows("4:4").Select
Cells.Find(What:="low").Ac
ActiveCell.Offset(0, -1).Select
Columns(ActiveCell.Column)
ActiveCell = Sheet1.TextBox6.Text
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Prio
Selection.Copy
Addrs = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows
Range(Addrs & "5:" & Addrs & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Rows("4:4").Select
Cells.Find(What:="low").Ac
Addrs1 = Split(ActiveCell.Address, "$")(1)
ActiveCell.Offset(0, 8).Select
Addrs2 = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows
ActiveCell.Offset(-3, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, -8)).Select
Selection.Copy
Range(Addrs1 & "5:" & Addrs2 & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 4).Select
Range(Selection, Selection.End(xlDown)).Sel
Selection.TextToColumns DataType:=xlDelimited
Range("f1").Select
Selection.Copy
Range("f5:f" & ActiveSheet.UsedRange.Rows
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Replace What:="1/0/1900", Replacement:=""
Range("h1").Select
Selection.Copy
Range("h5:h" & ActiveSheet.UsedRange.Rows
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("4:4").Select
Cells.Find(What:="low").Ac
ActiveCell.Offset(1, 9).Select
Addrs3 = Split(ActiveCell.Address, "$")(1)
RcNt = ActiveSheet.UsedRange.Rows
ActiveCell.FormulaR1C1 = "=IF(R[0]C8=""Not Worked"","""",TODAY()-R[0]
Selection.Copy
Range(Addrs3 & "5:" & Addrs3 & RcNt).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
If Sheet1.OptionButton1.Value
Range("d1").Select
Selection.ClearContents
Range("G2").Select
Selection.Copy
Range("g5:g" & ActiveSheet.UsedRange.Rows
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
End If
If Sheet1.OptionButton3.Value
Range("d1").Select
ActiveCell = Sheet1.TextBox6.Text
Range("G2").Select
Selection.Copy
Range("g5:g" & ActiveSheet.UsedRange.Rows
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
End If
If Sheet1.OptionButton4.Value
Range("G4").Activate
Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("a4:cz" & ActiveSheet.UsedRange.Rows
"Cleared", "Required", "Required 2", "="), Operator:=xlFilterValues
Range("G1").Select
Selection.Copy
Range("g5:g" & ActiveSheet.UsedRange.Rows
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("f5").Select
End If
Sheets("schools").Select
Range("f1:h1").Select
Selection.Copy
Range("f5:h" & ActiveSheet.UsedRange.Rows
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("1:1").Select
Selection.EntireRow.Hidden
Range("f5").Select
Sheets("Minimum Accounts Work Tracker").Select
Rows("1:1").Select
Selection.EntireRow.Hidden
Range("f5").Select
Sheets("Progress Table").Select
Range("A1").Select
Sheets("FSCM RawData").Visible = False
Sheets("2nd BPO").Visible = False
Sheets("Priority").Visible
Sheets("LIST").Visible = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("a1").Select
myfile = Dir()
Loop
'Call Binary
End Sub
ASKER
others work good,check after If Sheet1.OptionButton3.Value = True Then
ASKER
any updation on this friends
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great thanks
ASKER
Dear Experts,
could please let me know how to use For, Next for getting files
regards
Bala A
could please let me know how to use For, Next for getting files
regards
Bala A