Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine How many files in specified directory
The MASTER (Destination) WB will have only ONE WS.
Each Source WB has only ONE WS Each Source WS in each WB has the same number of Columns Each Source WS in each WB has Different number of rows. Each Source WB will be named by number, in order, from 1.xls thru 12.xls - EXPECT that usually will have 4 or 5 Source WB's All (ONLY) WB's for this process will be in C:\DATA How can the number of WB's be determined automatically, and then how can the required processes be activated for only that number of WB'sfiles. Sub Combine_M2M_Extracts() ' M2M-Master.xlt file has been opened ' Shows as M2M-Master1.xls - Save M2M-Master1.xls as MASTER.xls ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ' Determine how many WBs are available in C:\Data\ ' Wb's must be appended into MASTER in Order - Low File Number to High ' If only ONE file in Directory, Then exit Sub ' If only TWO or More Files in Directory, ' Run AddHeading, then Run Combine_ALL ' If THREE or MORE, Run AddHeading, then Run Combine_ALL ' AND Loop Combine_ALL, as required End Sub Sub ADDHEADING() ' Open/Activate # 1 File and Select Row 1 ONLY (Header Row) Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select End Sub Sub Combine_ALL() ' Open/Activate # 1 File and Select Used Range ' Start at Row 2 - The header row is not needed again Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select Selection.Insert Shift:=xlDown ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub ' At this point it should loop thru all available Source Files |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine How many files in specified directory
You can use the following function to return the number of Excel files in a
specified directory: Function CountWB(FullPath As String) As Integer Dim strFile As String Dim strXLFile Dim intFileCount strFile = FullPath & "\" & "*.xls" strXLFile = Dir(strFile) Do Until strXLFile = "" intFileCount = intFileCount + 1 strXLFile = Dir Loop CountWB = intFileCount End Function To count all the workbooks in C:\Excel the function is entered as CountWB("C:\Excel") -- Kevin Backmann "BEEJAY" wrote: The MASTER (Destination) WB will have only ONE WS. Each Source WB has only ONE WS Each Source WS in each WB has the same number of Columns Each Source WS in each WB has Different number of rows. Each Source WB will be named by number, in order, from 1.xls thru 12.xls - EXPECT that usually will have 4 or 5 Source WB's All (ONLY) WB's for this process will be in C:\DATA How can the number of WB's be determined automatically, and then how can the required processes be activated for only that number of WB'sfiles. Sub Combine_M2M_Extracts() ' M2M-Master.xlt file has been opened ' Shows as M2M-Master1.xls - Save M2M-Master1.xls as MASTER.xls ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ' Determine how many WBs are available in C:\Data\ ' Wb's must be appended into MASTER in Order - Low File Number to High ' If only ONE file in Directory, Then exit Sub ' If only TWO or More Files in Directory, ' Run AddHeading, then Run Combine_ALL ' If THREE or MORE, Run AddHeading, then Run Combine_ALL ' AND Loop Combine_ALL, as required End Sub Sub ADDHEADING() ' Open/Activate # 1 File and Select Row 1 ONLY (Header Row) Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select End Sub Sub Combine_ALL() ' Open/Activate # 1 File and Select Used Range ' Start at Row 2 - The header row is not needed again Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select Selection.Insert Shift:=xlDown ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub ' At this point it should loop thru all available Source Files |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine How many files in specified directory
The code below has your solution buried within. This code loads a ComboBox
with specified file names but alco counts the files. The files specified are .xls but if you want ALL files use *.*. Private Sub UserForm_Initialize() Dim WhereAmI As String Dim fn As String 'Initialize WhereAmI = CurDir Count = 0 'Locate File Spec ChDir DataLocation fn = Dir("*.xls") 'Locate files Do While fn < "" ComboBox1.AddItem fn Count = Count + 1 fn = Dir() Loop 'Exit if DataLocation file has no .xls files If Count = 0 Then Unload UserForm1 Sheets("SplashScreen").Select Exit Sub End If 'Cleanup ChDir WhereAmI ComboBox1.Value = "Regional_Entry_Template.xls" End Sub Hope this helps, APL Allan P. London, CPA "BEEJAY" wrote in message ... The MASTER (Destination) WB will have only ONE WS. Each Source WB has only ONE WS Each Source WS in each WB has the same number of Columns Each Source WS in each WB has Different number of rows. Each Source WB will be named by number, in order, from 1.xls thru 12.xls - EXPECT that usually will have 4 or 5 Source WB's All (ONLY) WB's for this process will be in C:\DATA How can the number of WB's be determined automatically, and then how can the required processes be activated for only that number of WB'sfiles. Sub Combine_M2M_Extracts() ' M2M-Master.xlt file has been opened ' Shows as M2M-Master1.xls - Save M2M-Master1.xls as MASTER.xls ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ' Determine how many WBs are available in C:\Data\ ' Wb's must be appended into MASTER in Order - Low File Number to High ' If only ONE file in Directory, Then exit Sub ' If only TWO or More Files in Directory, ' Run AddHeading, then Run Combine_ALL ' If THREE or MORE, Run AddHeading, then Run Combine_ALL ' AND Loop Combine_ALL, as required End Sub Sub ADDHEADING() ' Open/Activate # 1 File and Select Row 1 ONLY (Header Row) Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select End Sub Sub Combine_ALL() ' Open/Activate # 1 File and Select Used Range ' Start at Row 2 - The header row is not needed again Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select Selection.Insert Shift:=xlDown ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub ' At this point it should loop thru all available Source Files |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine How many files in specified directory
Kevin:
I put the following in a sub, so I could watch it step thru (F8) The Function line kicks it over into a different module, then shows error about invalid outside procedure What am I doing wrong here? Sub CountWorkBooks() Function CountWB("C:\Data") As Integer Dim strFile As String Dim strXLFile Dim intFileCount strFile = FullPath & "\" & "*.xls" strXLFile = Dir(strFile) Do Until strXLFile = "" intFileCount = intFileCount + 1 strXLFile = Dir Loop CountWB = intFileCount End Sub "Kevin B" wrote: You can use the following function to return the number of Excel files in a specified directory: Function CountWB(FullPath As String) As Integer Dim strFile As String Dim strXLFile Dim intFileCount strFile = FullPath & "\" & "*.xls" strXLFile = Dir(strFile) Do Until strXLFile = "" intFileCount = intFileCount + 1 strXLFile = Dir Loop CountWB = intFileCount End Function To count all the workbooks in C:\Excel the function is entered as CountWB("C:\Excel") -- Kevin Backmann "BEEJAY" wrote: The MASTER (Destination) WB will have only ONE WS. Each Source WB has only ONE WS Each Source WS in each WB has the same number of Columns Each Source WS in each WB has Different number of rows. Each Source WB will be named by number, in order, from 1.xls thru 12.xls - EXPECT that usually will have 4 or 5 Source WB's All (ONLY) WB's for this process will be in C:\DATA How can the number of WB's be determined automatically, and then how can the required processes be activated for only that number of WB'sfiles. Sub Combine_M2M_Extracts() ' M2M-Master.xlt file has been opened ' Shows as M2M-Master1.xls - Save M2M-Master1.xls as MASTER.xls ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ' Determine how many WBs are available in C:\Data\ ' Wb's must be appended into MASTER in Order - Low File Number to High ' If only ONE file in Directory, Then exit Sub ' If only TWO or More Files in Directory, ' Run AddHeading, then Run Combine_ALL ' If THREE or MORE, Run AddHeading, then Run Combine_ALL ' AND Loop Combine_ALL, as required End Sub Sub ADDHEADING() ' Open/Activate # 1 File and Select Row 1 ONLY (Header Row) Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select End Sub Sub Combine_ALL() ' Open/Activate # 1 File and Select Used Range ' Start at Row 2 - The header row is not needed again Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select Selection.Insert Shift:=xlDown ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub ' At this point it should loop thru all available Source Files |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine How many files in specified directory
The doesn't need to be placed within a sub routine, the sub routine calls the
function to return the count. The function can reside with the same module as the sub routine, but not within the sub itself Sub CountWorkBooks() Dim i as Integer Dim intCounter as Integer i = CountWB("C:\Data") For intCounter = 1 to i 'The file process you need to perform for each file Next intcounter End Sub -- Kevin Backmann "BEEJAY" wrote: Kevin: I put the following in a sub, so I could watch it step thru (F8) The Function line kicks it over into a different module, then shows error about invalid outside procedure What am I doing wrong here? Sub CountWorkBooks() Function CountWB("C:\Data") As Integer Dim strFile As String Dim strXLFile Dim intFileCount strFile = FullPath & "\" & "*.xls" strXLFile = Dir(strFile) Do Until strXLFile = "" intFileCount = intFileCount + 1 strXLFile = Dir Loop CountWB = intFileCount End Sub "Kevin B" wrote: You can use the following function to return the number of Excel files in a specified directory: Function CountWB(FullPath As String) As Integer Dim strFile As String Dim strXLFile Dim intFileCount strFile = FullPath & "\" & "*.xls" strXLFile = Dir(strFile) Do Until strXLFile = "" intFileCount = intFileCount + 1 strXLFile = Dir Loop CountWB = intFileCount End Function To count all the workbooks in C:\Excel the function is entered as CountWB("C:\Excel") -- Kevin Backmann "BEEJAY" wrote: The MASTER (Destination) WB will have only ONE WS. Each Source WB has only ONE WS Each Source WS in each WB has the same number of Columns Each Source WS in each WB has Different number of rows. Each Source WB will be named by number, in order, from 1.xls thru 12.xls - EXPECT that usually will have 4 or 5 Source WB's All (ONLY) WB's for this process will be in C:\DATA How can the number of WB's be determined automatically, and then how can the required processes be activated for only that number of WB'sfiles. Sub Combine_M2M_Extracts() ' M2M-Master.xlt file has been opened ' Shows as M2M-Master1.xls - Save M2M-Master1.xls as MASTER.xls ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ' Determine how many WBs are available in C:\Data\ ' Wb's must be appended into MASTER in Order - Low File Number to High ' If only ONE file in Directory, Then exit Sub ' If only TWO or More Files in Directory, ' Run AddHeading, then Run Combine_ALL ' If THREE or MORE, Run AddHeading, then Run Combine_ALL ' AND Loop Combine_ALL, as required End Sub Sub ADDHEADING() ' Open/Activate # 1 File and Select Row 1 ONLY (Header Row) Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select End Sub Sub Combine_ALL() ' Open/Activate # 1 File and Select Used Range ' Start at Row 2 - The header row is not needed again Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select Selection.Insert Shift:=xlDown ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub ' At this point it should loop thru all available Source Files |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine How many files in specified directory
Greetings:
I finally got the first part of my code working and am now trying to "include" your sample. If I'm 'reading' the code correctly, I think your code will work best for my application. I'm getting an error message: 'Compile Error - Variable not defined.' I know this should be simple, but I don't understand this stuff yet. Would you kindly help out? The error Hi-lites 'Count =' 'Initialize WhereAmI = CurDir Count = 0 "Allan P. London, CPA" wrote: The code below has your solution buried within. This code loads a ComboBox with specified file names but alco counts the files. The files specified are .xls but if you want ALL files use *.*. Private Sub UserForm_Initialize() Dim WhereAmI As String Dim fn As String 'Initialize WhereAmI = CurDir Count = 0 'Locate File Spec ChDir DataLocation fn = Dir("*.xls") 'Locate files Do While fn < "" ComboBox1.AddItem fn Count = Count + 1 fn = Dir() Loop 'Exit if DataLocation file has no .xls files If Count = 0 Then Unload UserForm1 Sheets("SplashScreen").Select Exit Sub End If 'Cleanup ChDir WhereAmI ComboBox1.Value = "Regional_Entry_Template.xls" End Sub Hope this helps, APL Allan P. London, CPA "BEEJAY" wrote in message ... The MASTER (Destination) WB will have only ONE WS. Each Source WB has only ONE WS Each Source WS in each WB has the same number of Columns Each Source WS in each WB has Different number of rows. Each Source WB will be named by number, in order, from 1.xls thru 12.xls - EXPECT that usually will have 4 or 5 Source WB's All (ONLY) WB's for this process will be in C:\DATA How can the number of WB's be determined automatically, and then how can the required processes be activated for only that number of WB'sfiles. Sub Combine_M2M_Extracts() ' M2M-Master.xlt file has been opened ' Shows as M2M-Master1.xls - Save M2M-Master1.xls as MASTER.xls ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ' Determine how many WBs are available in C:\Data\ ' Wb's must be appended into MASTER in Order - Low File Number to High ' If only ONE file in Directory, Then exit Sub ' If only TWO or More Files in Directory, ' Run AddHeading, then Run Combine_ALL ' If THREE or MORE, Run AddHeading, then Run Combine_ALL ' AND Loop Combine_ALL, as required End Sub Sub ADDHEADING() ' Open/Activate # 1 File and Select Row 1 ONLY (Header Row) Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select End Sub Sub Combine_ALL() ' Open/Activate # 1 File and Select Used Range ' Start at Row 2 - The header row is not needed again Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Range("A1").Select Selection.End(xlDown).Offset(1, 0).Select Selection.Insert Shift:=xlDown ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub ' At this point it should loop thru all available Source Files |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with files in directory... | Excel Programming | |||
Files in a directory? | Excel Discussion (Misc queries) | |||
How to determine the parent directory | Excel Programming | |||
Check if directory empty OR no of files in directory. | Excel Programming | |||
How does Excel determine the TEMP directory? | Excel Programming |