-2

I am trying to loop all the files in a folder, while trying to loop it is not looping instead it is selecting the same file and if I tried with wild card Dir becomes null character. I need to check all the excel files and if folder exist, I need to check the folder also. If I add wild card, code does not run or the loop is picking up the same file.

Sub Check()
Dim OpenFolder As String
Dim Openfile As String
Dim Res As Workbook
Set Res = Workbooks("Macro for file opener")
Dim Ressh As Worksheet
Set Ressh = Res.Sheets("Result")
Dim WS As Worksheet
Dim WC As Worksheet
Dim DC As Boolean
Dim MP As Boolean
Dim NB As Boolean

    OpenFolder = Application.GetOpenFilename()
    Openfile = Dir(OpenFolder)
    Do While Openfile <> ""
    Workbooks.Open (OpenFolder)
    Set WS = Sheets("Type A")
    'File present
    If WS.Name = "Type A" Then
    WS.Select
    On Error Resume Next
    DC = Cells.Find(What:="code", After:=ActiveCell, LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
     
     
    If DC = True Then
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Openfile
        ActiveCell.Offset(0, 1).Value = "code exist"
    Else
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Openfile
        ActiveCell.Offset(0, 1).Value = "code not exist"
    End If
    'Missing part
    Workbooks.Open (OpenFolder)
    On Error Resume Next
    WS.Select
    MP = Cells.Find(What:="Missing part", After:=ActiveCell, LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
    If MP = True Then
    
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(0, 2).Value = "Missing part exist"
    Else
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Offset(0, 2).Value = "Missing part not exist"
    End If
    
    
    'File not present
    Else
    
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Openfile
        ActiveCell.Offset(0, 1).Value = "file not exist"
    
    End If
    
    
    Workbooks.Open (OpenFolder)
    Set WC = Sheets("Chart")
    'File name exist
    If WC.Name = "Chart" Then
    On Error Resume Next
    WC.Select
    NB = Cells.Find(What:="Non-Buildable Usage", After:=ActiveCell, LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
    If NB = True Then
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(0, 3).Value = "Non break exist"
    
    
    Else
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(0, 3).Value = "Non break not exist"
    
    
    End If
    
    
    
    'File not exist
    Else
    Res.Activate
    Ressh.Select
    Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Offset(0, 3).Value = "file not exist"
    
    End If
    DoEvents
    Openfile = Dir()
    
    
    Loop
    End Sub
Itzme ram
  • 13
  • 3
  • Which line (code statement) exhibits the first deviation from what you expect? What is the expected behavior vs. observed behavior? – Tom Brunberg Oct 17 '22 at 07:39
  • Dir having the same sheet name, loop is not running to next sheet, Folder is having 500 excel files, but it is sticking with same sheet only. – Itzme ram Oct 17 '22 at 07:47
  • Does your macro open different worksheets? Does Dir work as expected? Please remove _On error resume next_ to get error messages. What is the value of _Openfolder_ is this value valid? – Shrotter Oct 17 '22 at 07:52
  • When I added openfile as Dir(OpenFolder & "\*.*"), I am getting error of Bad file or number, next file is not opening, I have selected first file in the folder why it is not selecting next file as per the loop – Itzme ram Oct 17 '22 at 08:00
  • @ItzmeramWhat is the initial value of _OpenFolder_? Is it a folder or a filename? If it is a filename _Dir_ will only return one/this filename and not all filenames of a folder. – Shrotter Oct 17 '22 at 08:04
  • Open folder having path name of first sheet, "C:\Users\SRI8\Downloads\Course\Testing\Part1\firstsheet.xlsx" and Openfile having value of "firstsheet.xlsx" – Itzme ram Oct 17 '22 at 08:18
  • @Shrotter then how to include all the files? while using Application.GetOpenFilename() we can select first excel sheet in a folder right? how does DIR will move to next sheet then? – Itzme ram Oct 17 '22 at 08:23
  • @Itzmeram You could switch to select a folder instead a file (like shown in the answer of milo5m). Be aware _Dir_ gives you the filename not the complete path. – Shrotter Oct 17 '22 at 08:38
  • 2
    So why are you posting the whole `Sub()`, if you could present your question with less than ten lines of code. You are required to show a `mre` where `m` stands for minimal. Please read about [mre]. Also, learn about dividing your programs into relatively small functions writing functions and subs. – Tom Brunberg Oct 17 '22 at 08:43
  • Then we cannot use Application.GetOpenFilename() for looping all the files in a folder? I saw some codes they were using Application.GetOpenFilename() for looping all the files by just selecting the first excel sheet in a folder, then with this code we can work with the first sheet which we are selecting right? – Itzme ram Oct 17 '22 at 08:45
  • I don't get why are you fixated on `Application.GetOpenFilename()`. And hammering not one but 4 `Selection.End(xlDown).Select` in your code. :) – milo5m Oct 17 '22 at 08:52
  • @Itzmeram It is possible to use _GetOpenFilename_ but then you have to separate the path (which is needed for _Dir_) from the returned string. – Shrotter Oct 17 '22 at 09:15
  • @Shrotter In that case `OpenFolder = Left$(Openfile, InStrRev(Openfile, "\"))` gives him the folderPath. – milo5m Oct 17 '22 at 09:25
  • 1
    @milo5m I know, but a want to give only a small hint and not a solution – Shrotter Oct 17 '22 at 09:35
  • I would suggest you to test again the answer you received. I am sure that it will work as you need... – FaneDuru Oct 17 '22 at 09:47
  • I have tried that one also, thing is Open file is not assigning the next sheet name but DIR value shows next sheet name, Open file is having fixed sheet name. – Itzme ram Oct 17 '22 at 11:04
  • Did you use `Workbooks.Open (OpenFolder & Openfile)` as in the answer code, or used your (wrong) code (`Workbooks.Open (OpenFolder)`)? I have some doubts about that... – FaneDuru Oct 17 '22 at 11:20
  • I have used Workbooks.Open (OpenFolder & Openfile) only but when I used OpenFolder = Left$(Openfile, InStrRev(Openfile, "\")) I am getting null string but DIR is having the value of next file, because of Openfile I could not extract datas from next sheet – Itzme ram Oct 17 '22 at 11:45
  • Who said to use `OpenFolder = Left$(Openfile, InStrRev(Openfile, "\"))`. It was **only** a suggestion to correct your (wrong) way of doing and **extracting the folder path from the file full name**. The code in discussion **does not need that**. Please, try using **as it is**. Of course, up to the commented line suggesting to use your code. And, of course, ending the loop with `Openfile = Dir()`... – FaneDuru Oct 17 '22 at 12:15

1 Answers1

2

Do a small test on any folder using this

Sub test()
    Dim OpenFolder As String
    Dim Openfile As String

    OpenFolder = GetDialogueFolderPath()
    If Len(OpenFolder) = 0 Then Exit Sub
    Openfile = Dir(OpenFolder & "*.xls*") 'just to be sure you are getting only excel files
    Do While Len(Openfile) > 1
        Workbooks.Open (OpenFolder & Openfile)
        'the rest of your code
        Openfile = Dir()
    Loop
End Sub

Public Function GetDialogueFolderPath(Optional ByVal defaultFolderPath As String, Optional ByVal Title As String) As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = Title
        .AllowMultiSelect = False
        .InitialFileName = defaultFolderPath
        If .Show = -1 Then
            GetDialogueFolderPath = .SelectedItems(1)
            If GetDialogueFolderPath <> vbNullString Then
                If Right(GetDialogueFolderPath, 1) <> "\" Then: GetDialogueFolderPath = GetDialogueFolderPath & "\"
            End If
        End If
    End With
End Function
milo5m
  • 619
  • 1
  • 3
  • 8
  • Getting error of Sub or Function not defined for GetDialogueFolderPath – Itzme ram Oct 17 '22 at 08:14
  • It is working fine now, thank you so much but Application.GetOpenFilename() not working, I dont know why it is not working ;/ – Itzme ram Oct 17 '22 at 12:03
  • Try to explain to me why would you use Application.GetOpenFilename(), and not the function I suggested? Go a little bit into specifics please. Also check example here https://learn.microsoft.com/en-us/office/vba/api/excel.application.getopenfilename – milo5m Oct 17 '22 at 12:07
  • I have used this code, what is happening first time it is running well next two or three times only 2 excel files are opening, remaining files are not opening – Itzme ram Oct 17 '22 at 13:16
  • The rest of your code is not well put together, produces problems. Try to step through using F8 and see what is going on in Locals panel. Happy debug time :) – milo5m Oct 17 '22 at 13:22