Coding in suport of these excelfox Threads and posts:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11089&viewfull=1#post11089
https://www.excelforum.com/excel-pro...-the-file.html
Here last routine in form to allow user selection of folder to search for filesCode:Sub DirOrder() ' http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11092&viewfull=1#post11092 Dim strWB As String Rem 1 get the full string, strWB, for a Folder to use in the Dir(Fullpath&FileName, __ ) ( strWB=Fullpath&FileName - FileName ) '1a) use the asking pop up thing, File dialogue folder picker ' With Application.FileDialog(msoFileDialogFolderPicker) ' .Title = "Folder Select" ' .AllowMultiSelect = False ' If .Show <> -1 Then ' Exit Sub ' Else ' End If ' Let strWB = .SelectedItems(1) ' & "\" ' End With ' '1b) Using a test Folder, named Folder in the same Folder as the workbook in which this code is Let strWB = ThisWorkbook.Path & "\Folder" '1c) Hard code instead 'Let strWB = "F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder" Debug.Print "Folder used is" & vbCrLf & strWB & vbCrLf & "" & Right(strWB, (Len(strWB) - InStrRev(strWB, "\", -1, vbTextCompare))) Debug.Print Let strWB = strWB & "\" Rem 2 add last file bit for use in the Dir(Fullpath&FileName, __ ) , but include wild cards... http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11089&viewfull=1#post11089 : _(i) You can use wild cards in the full path and file name string that you give it, so it will look for a file matching your given string. ( So typically you might give a string like “C:\myFolder\*.xl*”, which would look for any excel file: In this bit *.xl* , the first * allows for any file name, and the second * will allow for extensions such as .xls , .xlsm, .xlsx, .. etc… ) _(ii) After you use like Dir(Fullpath&FileName, __ ) once, then any use after of just _ Dir __ without any arguments, will give the next file it finds based on the string you gave in the first use with arguments '2a) Excel files Let strWB = strWB & "*.xls*" Dim File As String: Let File = Dir(strWB) Debug.Print "First got by Dir(" & strWB & ")" & vbCrLf & "is " & File Debug.Print Do ' '_- I want to keep going in a Loop while I still get a file name returned by Dir Dim Cnt As Long: Let Cnt = Cnt + 1 Let File = Dir: Debug.Print "Use " & Cnt & " in loop of unargumented Dir gives """ & File & """" Loop While File <> "" '_- I want to keep going in a Loop while I still get a file name returned by Dir Debug.Print Debug.Print End Sub
Code:Sub DirOrder() ' http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11093&viewfull=1#post11093 Dim strWB As String Rem 1 get the full string, strWB, for a Folder to use in the Dir(Fullpath&FileName, __ ) ( strWB=Fullpath&FileName - FileName ) '1a) use the asking pop up thing, File dialogue folder picker With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Folder Select" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub Else End If Let strWB = .SelectedItems(1) ' & "\" End With '1b) Using a test Folder, named Folder in the same Folder as the workbook in which this code is 'Let strWB = ThisWorkbook.Path & "\Folder" '1c) Hard code instead 'Let strWB = "F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder" Debug.Print "Folder used is" & vbCrLf & strWB & vbCrLf & "" & Right(strWB, (Len(strWB) - InStrRev(strWB, "\", -1, vbTextCompare))) Debug.Print Let strWB = strWB & "\" Rem 2 add last file bit for use in the Dir(Fullpath&FileName, __ ) , but include wild cards... http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11089&viewfull=1#post11089 : _(i) You can use wild cards in the full path and file name string that you give it, so it will look for a file matching your given string. ( So typically you might give a string like “C:\myFolder\*.xl*”, which would look for any excel file: In this bit *.xl* , the first * allows for any file name, and the second * will allow for extensions such as .xls , .xlsm, .xlsx, .. etc… ) _(ii) After you use like Dir(Fullpath&FileName, __ ) once, then any use after of just _ Dir __ without any arguments, will give the next file it finds based on the string you gave in the first use with arguments '2a) Excel files Let strWB = strWB & "*" Dim File As String: Let File = Dir(strWB) Debug.Print "First got by Dir(" & strWB & ")" & vbCrLf & "is " & File Debug.Print Do ' '_- I want to keep going in a Loop while I still get a file name returned by Dir Dim Cnt As Long: Let Cnt = Cnt + 1 Let File = Dir: Debug.Print "Use " & Cnt & " in loop of unargumented Dir gives """ & File & """" Loop While File <> "" '_- I want to keep going in a Loop while I still get a file name returned by Dir Debug.Print Debug.Print End Sub
Reply With Quote
Bookmarks