There is a second argument to Dir. It is not used much. https://docs.microsoft.com/en-us/off...ction#settings
One option will make it return folder names as well. For our example we can change Dir(strWB) to any if these: Dir(strWB, vbDirectory) ; Dir(PathName:=strWB, Attributes:=vbDirectory) ; Dir(PathName:=strWB, Attributes:=16) ; Dir(strWB, 16)
Running the routine with the previous example, seems to slip the folder names in the appropriate place to once again have everything in alphabetical order
Here is the folder used for the last two tests: wbFolder.JPG : https://imgur.com/MMydq7nCode:Folder used is F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery wbSheetMakerClsdWbADOMsQueery First got by Dir(F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\*) is . Use 1 in loop of unargumented Dir gives ".." Use 2 in loop of unargumented Dir gives "83DB8900" Use 3 in loop of unargumented Dir gives "aaa.xlsm" Use 4 in loop of unargumented Dir gives "ACDC" Use 5 in loop of unargumented Dir gives "Amar321.xls.xlsx" Use 6 in loop of unargumented Dir gives "ApparantlyApparantIs_Change.JPG" Use 7 in loop of unargumented Dir gives "Aufzeichnen.JPG" Use 8 in loop of unargumented Dir gives "Bad Files" Use 9 in loop of unargumented Dir gives "Book1.xls" Use 10 in loop of unargumented Dir gives "Book1.xlsm.zip" Use 11 in loop of unargumented Dir gives "CA930CD8.tmp" Use 12 in loop of unargumented Dir gives "ClosedWorkbook.xlsm" Use 13 in loop of unargumented Dir gives "ClsdWbs" Use 14 in loop of unargumented Dir gives "CopyASheet.JPG" Use 15 in loop of unargumented Dir gives "CressieFolder" Use 16 in loop of unargumented Dir gives "CresssieFiles.JPG" Use 17 in loop of unargumented Dir gives "DB2IssJfürELProAbDec2014.xlsm" Use 18 in loop of unargumented Dir gives "EFFldr.xlsm" Use 19 in loop of unargumented Dir gives "EFldr1_1" Use 20 in loop of unargumented Dir gives "EFldr1_1 Download.JPG" Use 21 in loop of unargumented Dir gives "Eileens Fldr.zip" Use 22 in loop of unargumented Dir gives "EileensFldr" Use 23 in loop of unargumented Dir gives "EileensFldr Contents Copy.JPG" Use 24 in loop of unargumented Dir gives "EileensFldr Contents Paste.JPG" Use 25 in loop of unargumented Dir gives "EileensFldr Make Empty Folder.JPG" Use 26 in loop of unargumented Dir gives "EileensFldr zip Download.JPG" Use 27 in loop of unargumented Dir gives "EileensFldr.zip" Use 28 in loop of unargumented Dir gives "EileensFolderExplainedOutput.JPG" Use 29 in loop of unargumented Dir gives "Example Folder and Macro File in same Folder.JPG" Use 30 in loop of unargumented Dir gives "FBandData.xlsm" Use 31 in loop of unargumented Dir gives "FBandDataNorie.xlsm" Use 32 in loop of unargumented Dir gives "FBandDataNorie.xlsx" Use 33 in loop of unargumented Dir gives "FormulaBarClosedWB.JPG" Use 34 in loop of unargumented Dir gives "GetData_ClosedBook+LINKS.xlsx" Use 35 in loop of unargumented Dir gives "GetData_ClosedBook.xls" Use 36 in loop of unargumented Dir gives "HimanshuktwCode.JPG" Use 37 in loop of unargumented Dir gives "Kill Stuff" Use 38 in loop of unargumented Dir gives "KissMyClosedWB.JPG" Use 39 in loop of unargumented Dir gives "MacroRecording" Use 40 in loop of unargumented Dir gives "Mappe2.xlsm" Use 41 in loop of unargumented Dir gives "MazanDikCollectionWonk.xlsm" Use 42 in loop of unargumented Dir gives "mellowtangSummarySheets.xlsm" Use 43 in loop of unargumented Dir gives "MsQueerOptions.JPG" Use 44 in loop of unargumented Dir gives "MsQueeryADO" Use 45 in loop of unargumented Dir gives "myFileToClose.xlsm" Use 46 in loop of unargumented Dir gives "MyNewWorkbook.xlsx" Use 47 in loop of unargumented Dir gives "MySameFolder.JPG" Use 48 in loop of unargumented Dir gives "Neuer Ordner" Use 49 in loop of unargumented Dir gives "NeuProAktuelleMakros.xlsm" Use 50 in loop of unargumented Dir gives "NormalThisWorkbookCodeModule.JPG" Use 51 in loop of unargumented Dir gives "NutritionalValues2016.xlsx" Use 52 in loop of unargumented Dir gives "OnlyGets8810RowsInAQuerrListObjectTableThingyAnyways.JPG" Use 53 in loop of unargumented Dir gives "Plop.xlsm" Use 54 in loop of unargumented Dir gives "poo.xlsm" Use 55 in loop of unargumented Dir gives "RudyMSRAllSubFldrsFndRep.xlsm" Use 56 in loop of unargumented Dir gives "Sample.zip" Use 57 in loop of unargumented Dir gives "SchemaIniErrorPipe.JPG" Use 58 in loop of unargumented Dir gives "SrangeThisWorkbookCodeModule.JPG" Use 59 in loop of unargumented Dir gives "StopClosing.xlsm" Use 60 in loop of unargumented Dir gives "Summary sheet.xlsm" Use 61 in loop of unargumented Dir gives "template test.xlsm" Use 62 in loop of unargumented Dir gives "Top100MsQuery.JPG" Use 63 in loop of unargumented Dir gives "ViskasVerticalsMaster dataMjoza.xlsm" Use 64 in loop of unargumented Dir gives "wb2.csv" Use 65 in loop of unargumented Dir gives "wb2.xlsm" Use 66 in loop of unargumented Dir gives "wb2.xlsx" Use 67 in loop of unargumented Dir gives "WBAccessTimeTestData.xlsx" Use 68 in loop of unargumented Dir gives "WBAccestTimeTest.xlsm" Use 69 in loop of unargumented Dir gives "wbCodes.xls" Use 70 in loop of unargumented Dir gives "wbCodes.xlsb" Use 71 in loop of unargumented Dir gives "wbCodes.xlsm" Use 72 in loop of unargumented Dir gives "WBOpenRenameKlaredog.xls" Use 73 in loop of unargumented Dir gives "WBOpenRenameKlaredog.xlsm" Use 74 in loop of unargumented Dir gives "Wb_with_5Sheets_4Worksheets.xlsm" Use 75 in loop of unargumented Dir gives "WillyWonks.JPG" Use 76 in loop of unargumented Dir gives "WonkBook" Use 77 in loop of unargumented Dir gives "workbook2.xlsm" Use 78 in loop of unargumented Dir gives "WorkbookOpenMsgBox.JPG" Use 79 in loop of unargumented Dir gives "WorksheetNames.JPG" Use 80 in loop of unargumented Dir gives "Worksheet_Change.JPG" Use 81 in loop of unargumented Dir gives "XYT" Use 82 in loop of unargumented Dir gives ""
Reply With Quote
Bookmarks