heroe
04-08-2009, 03:44 PM
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!
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!