[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
bala kumaran
Asked:
bala kumaran
  • 5
1 Solution
 
ProfessorJimJamCommented:
what is your complete code? if possible can you please post your complete sub routine?
0
 
bala kumaranAuthor Commented:
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").Activate
    ActiveCell.Offset(0, -2).Select
    ActiveCell = Sheet1.TextBox6.Text
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R[1]C,1),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!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).Select
    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").Activate
    ActiveCell.Offset(0, -2).Select
    ActiveCell = Sheet1.TextBox6.Text
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(CHOOSE(WEEKDAY(R[1]C,1),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!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").Activate
    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"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!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").Activate
    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"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri""),"""")"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC4,Priority!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").Activate
    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)).Select
    Selection.TextToColumns DataType:=xlDelimited
   
    Range("f1").Select
    Selection.Copy
    Range("f5:f" & ActiveSheet.UsedRange.Rows.Count).Select
    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).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
   
    Rows("4:4").Select
    Cells.Find(What:="low").Activate
    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).Select
    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).Select
    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).AutoFilter Field:=7, Criteria1:=Array( _
        "Cleared", "Required", "Required 2", "="), Operator:=xlFilterValues
    Range("G1").Select
    Selection.Copy
    Range("g5:g" & ActiveSheet.UsedRange.Rows.Count).Select
    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).Select
    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
0
 
bala kumaranAuthor Commented:
others work good,check   after  If Sheet1.OptionButton3.Value = True Then
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bala kumaranAuthor Commented:
any updation on this friends
0
 
GrahamSkanRetiredCommented:
Only one Dir() in a project can be active at a time.
It isn't clear why you are looking for similar extensions in a nested search. Perhaps you could look for ".xls*" in this case
Otherwise make a list for the outer loop and then work through the list

Sub Dirlistt()
    Dim strTemplates() As String
    Dim i As Integer
    Dim j As Integer
    
    MyFile = Dir(ippath & "*.xlsm")
    Do While MyFile <> ""
        ReDim Preserve strTemplates(i)
        strTemplates(i) = MyFile
        i = i + 1
        MyFile = Dir()
    Loop
    
    For j = 0 To i - 1
        MyFile = strTemplates(j)
        MyFile1 = Dir(ippath1 & "*.xlsx")
        Do While MyFile1 <> ""
            ''' process myfile1 in relation to myfile
            MyFile1 = Dir()
        Loop
    Next j
End Sub

Open in new window

0
 
bala kumaranAuthor Commented:
great thanks
0
 
bala kumaranAuthor Commented:
Dear Experts,

could please let me know how to use For, Next for getting files


regards
Bala A
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now