afzalw
07-04-2012, 12:24 AM
I found this code which open specified multi excel files from specified folders and extract data from them. How can I change it so I don`t have to specify the name of sub folder and excel files, instead it ran code on all excel files in subfolders.
Thanks
Sub CopySourceValuesToDestinationEdited3()
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sDestPath As String
Dim sSourcePath As String
Dim shDest As Worksheet
Dim rDest As Range
Dim vaFolder As Variant
Dim vaFiles As Variant
Dim i As Long
'array of folder names under sDestPath
vaFolder = Array("ABC", "DEF", "GHI", "JKL")
'array of file names under the respective folders in vaFolder
vaFiles = Array("ABCFile.xls", "DEFFile.xls", "GHIFile.xls", "JKLFile.xls")
sDestPath = "D:\A\"
sSourcePath = "D:\A\"
'Open the destination workbook at put the destination sheet in a variable
Set wbDest = Workbooks.Open(sDestPath & "a.xlsx")
Set shDest = wbDest.Sheets(1)
'loop through the folders
For i = LBound(vaFolder) To UBound(vaFolder)
'open the source
Set wbSource = Workbooks.Open(sSourcePath & vaFolder(i) & "\" & vaFiles(i))
' Rest of the code for extracting data
Thanks
Sub CopySourceValuesToDestinationEdited3()
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sDestPath As String
Dim sSourcePath As String
Dim shDest As Worksheet
Dim rDest As Range
Dim vaFolder As Variant
Dim vaFiles As Variant
Dim i As Long
'array of folder names under sDestPath
vaFolder = Array("ABC", "DEF", "GHI", "JKL")
'array of file names under the respective folders in vaFolder
vaFiles = Array("ABCFile.xls", "DEFFile.xls", "GHIFile.xls", "JKLFile.xls")
sDestPath = "D:\A\"
sSourcePath = "D:\A\"
'Open the destination workbook at put the destination sheet in a variable
Set wbDest = Workbooks.Open(sDestPath & "a.xlsx")
Set shDest = wbDest.Sheets(1)
'loop through the folders
For i = LBound(vaFolder) To UBound(vaFolder)
'open the source
Set wbSource = Workbooks.Open(sSourcePath & vaFolder(i) & "\" & vaFiles(i))
' Rest of the code for extracting data