The Excel Workbook that I use to consolidate multiple workbooks into one for reporting has stopped working because some of the 2003 Excel methods are not available in MS Excel 2007. The code reads through any workbook in a given directory and pulls the data out of the specified cell.
I originally thought the problem was due to macro security or trusted paths but eliminate that by testing it in 2003 - it worked fine there.
Can one of you please rewrite the code below to work in MS Excel 2007? The one method that I know does not work for sure is FIleSearch.
I've recreated sample files for test purposes:
File MSEx2007 has the code that should that should load the data from Summary TestData.
Dim wksCopyTo As Worksheet
Dim wkbCopyFrom As Workbook
Dim copyToHere As Range
Set wksCopyTo = ThisWorkbook.Sheets(1)
Set copyToHere = wksCopyTo.Range("a1")
n = 0
On Error Resume Next
kIn = ThisWorkbook.Path
eType = msoFileTypeExcelWorkbooks
ers = False
For i = 1 To Application.FileSearch.Fou
= ThisWorkbook.FullName Then GoTo NotMe
Set wkbCopyFrom = Workbooks.Open(Application
n = n + 1
copyToHere.Offset(0, 1) = .Range("D4").Value 'Project Name
copyToHere.Offset(0, 2) = .Range("D5").Value 'Requested By
'copyToHere.Offset(0, 6) = .Range("J7").Value
copyToHere.Offset(0, 30) = .Range("C60").Value
Set copyToHere = copyToHere.Offset(1)
Thank you for taking a look at this.
MSEx2007-Summary.xls SummaryTestData.xlsx MSEx2007-Summary.xls SummaryTestData.xlsx