How to determine How many files in specified directory

[BEEJAY]

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

 

[Kevin B]

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")

 

[Allan P. London, CPA]

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]

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]

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

 

[BEEJAY]

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