Using DIR

[schnabs]

Hey everyone,
I am using some code to combine two workbooks in Excel into one. I am using the DIR command to run through directories of the original files. My problem is, the first Dir seems to work, but the second one doesn't. My combined file has each of the first directories file, but only the first of the second directory.

Here is my code:

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

I swear it worked before, but I haven't run it in a while. I cannot figure out why path2 is not moving on to the next file.
Thanks in advance.

 

[jebenson]

Dir() is an VB 6 old function. If possible, I recommend using the .NET functions for this:

system.io.directory
system.io.directoryinfo
system.io.file
system.io.fileinfo

I think something like this may do it. I haven't tested this, but it should give you an idea of where to start:

        Dim d As System.IO.DirectoryInfo
        Dim d2 As System.IO.DirectoryInfo
        Dim f As System.IO.FileInfo()
        Dim f2 As System.IO.FileInfo()
        Dim path As String
        Dim path2 As String
        path = "Z:\2009 Combined Delinquencies\March\"
        path2 = "Z:\2009 Combined Delinquencies\Detailed\"

        d = New DirectoryInfo(path)
        d2 = New DirectoryInfo(path2)

        f = d.GetFiles
        f2 = d2.GetFiles

        Dim DetailName As String

        DetailName = f2(0).Name

        oxl = New Microsoft.Office.Interop.Excel.Application

        For Each SourceFile As FileInfo In f
            newName = "Z:\2009 Combined Delinquencies\" & SourceFile.Name


            oxlBook1 = oxl.Workbooks.Open(path + SourceFile.Name)

            oxlBook2 = oxl.Workbooks.Open(path2 & DetailName)
            oxlBook2.Sheets().Copy(After:=oxlBook1.Sheets(oxlBook1.Sheets.Count))

            oxlBook2.Close(False)

            oxlBook2 = Nothing
            oxlBook1.SaveAs(newName)
            oxlBook1.Close()
            oxlBook1 = Nothing

        Next

        oxl = Nothing

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!