Using VB 08 to combine excel files

[heroe]

Hey everyone,
I am trying to use VB 08 to combine multiple excel files. Here is an example. Lets say I have File 1A and 1B, 2A and 2B and so on. I want to combine the files based on number. So add file 1B to file 1A and save as a new file. There is only one sheet per workbook. My problem is using 2 DIR() commands. My code is as follow.

Public Sub button1_click() Handles Button1.Click
Dim oxl As microsoft.Office.Interop.Excel.Application
Dim oxlBook1 As Microsoft.Office.Interop.Excel.Workbook
Dim oxlBook2 As Microsoft.Office.Interop.Excel.Workbook
Dim newName As String
'Dim summary As String
'Dim detailed As String
Dim path As String
Dim path2 As String
path = Dir("Z:\2009 Combined Delinquencies\March\")
path2 = Dir("Z:\2009 Combined Delinquencies\Detailed\")

Do While path <> ""
newName = "Z:\2009 Combined Delinquencies\" + path

oxl = New Microsoft.Office.Interop.Excel.Application
oxlBook1 = oxl.Workbooks.Open("Z:\2009 Combined Delinquencies\March\" + path)
'For i = 2
oxlBook2 = oxl.Workbooks.Open("Z:\2009 Combined Delinquencies\Detailed\" + path2)
oxlBook2.Sheets().Copy(After:=oxlBook1.Sheets(oxlBook1.Sheets.Count))

oxlBook2.Close(False)
'Next
oxlBook2 = Nothing
oxlBook1.SaveAs(newName)
oxlBook1.Close()
oxlBook1 = Nothing

oxl = Nothing
path = Dir()
Loop
oxl.Quit()

End Sub

Now I know I need to set path2 to dir() also, but when I do that, it skips over files. I need to know how to set up path2. When I run the above code, it only takes the first file in path2 and adds it to each of the files in path, like directory should. Like I said, when I add the path2 = dir(), it skips over files, I'm assuming because I have both path and path2 set to dir at the end. I would even be open to setting up each of the B files by declaring their paths, but I'm not sure how I would go about setting that up.
Thanks in advance!

[Jan Karel Pieterse]

You'll have to do both paths in turn, storing their results in an array. Then work with the arrays to do the consolidating.

[Kenneth Hobs]

I would recommend the approach that Jan explained. However, I am not sure that a copy of each file's sheet1 in one folder to another folder's file and saved to the parent folder makes sense. I would think that some matching criteria would be needed.

In any case, this might help to show you how to create the array of filenames. The first Sub is a bit more than you needed but I wanted to show you how you can use the 2nd. Be sure to set the reference to the scripting runtime library for the Sub MyFiles as commented.

This is vba code so you will need a few tweaks for .NET.
Sub GetMyFiles()
Dim myFolder As String, wcFiles As String, s() As Variant
'Input parameters
myFolder = "x:\MsWord" 'No trailing backslash
wcFiles = "*.xls"
s = MyFiles(myFolder, wcFiles)
'Check to see if s() has any filenames
If s(0) = "NA" Then Exit Sub

'Put the contents of s() into column A
Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(s) + 1).Value _
= WorksheetFunction.Transpose(s)
Range("A1").EntireColumn.AutoFit
End Sub

Function MyFiles(myFolder As String, Optional wcFiles As String = "*.*") As Variant
'Requires reference to Microsoft Scripting Runtime
Dim cFiles As New Scripting.Dictionary
Dim FileName As String, a() As Variant

'Add trailing backslash if needed
If Right(myFolder, 1) <> "\" Then myFolder = myFolder & "\"

'Put filenames into dictionary
FileName = myFolder & Dir(myFolder & wcFiles)
Do While FileName <> myFolder
cFiles.Add FileName, Nothing
FileName = myFolder & Dir
Loop

'Return keys or items as an array
If cFiles.Count > 0 Then
a = cFiles.Keys
MyFiles = a
Else
ReDim a(1) As Variant
a(0) = "NA"
MyFiles = a
End If
Set cFiles = Nothing
End Function