edM
04-01-2008, 08:11 AM
Hi guys,
This should be simple but i cant figure how to do it!
I have a module which runs which finds 4 excel files (or more) in a specficied directories. It then prints the absolute file names onto sheet1 of the excel file where i run the macro from. OK that works fine
The sub prodcedure below copies cell 1,1 from the excel files and prints each result onto '1 A' on a new sheet in the work book each time called "Sheet_File_x".
So at the minute, if it finds 4 excel files, then 4 extra sheets are created in the workbook.
How could i modify it to just copy all the results to one sheet, one below the other?
I actually dont see where the a new sheet gets created each time it goes around the loop?
Public Sub openFiles()
Set IndexSheet = ThisWorkbook.ActiveSheet
For i = 1 To excelFiles.Count
Dim xl As New Excel.Application
Dim xlw As Excel.Workbook
Dim Filename As String
Filename = excelFiles.Item(i)
Set xlw = xl.Workbooks.Open(Filename)
xlw.Sheets("Sheet1").Activate
Dim copyValue As String
copyValue = xlw.Application.Cells(1, 1).Value
Sheets.Add.Name = "Sheet_File_" & i
xlw.Sheets("Sheet1").Activate
ActiveCell.Activate
ActiveCell.Value = copyValue
Next i
End Sub
thanks for any help!
This should be simple but i cant figure how to do it!
I have a module which runs which finds 4 excel files (or more) in a specficied directories. It then prints the absolute file names onto sheet1 of the excel file where i run the macro from. OK that works fine
The sub prodcedure below copies cell 1,1 from the excel files and prints each result onto '1 A' on a new sheet in the work book each time called "Sheet_File_x".
So at the minute, if it finds 4 excel files, then 4 extra sheets are created in the workbook.
How could i modify it to just copy all the results to one sheet, one below the other?
I actually dont see where the a new sheet gets created each time it goes around the loop?
Public Sub openFiles()
Set IndexSheet = ThisWorkbook.ActiveSheet
For i = 1 To excelFiles.Count
Dim xl As New Excel.Application
Dim xlw As Excel.Workbook
Dim Filename As String
Filename = excelFiles.Item(i)
Set xlw = xl.Workbooks.Open(Filename)
xlw.Sheets("Sheet1").Activate
Dim copyValue As String
copyValue = xlw.Application.Cells(1, 1).Value
Sheets.Add.Name = "Sheet_File_" & i
xlw.Sheets("Sheet1").Activate
ActiveCell.Activate
ActiveCell.Value = copyValue
Next i
End Sub
thanks for any help!