Robb or anyone
Posted by RoB on October 04, 2001 12:50 PM
This is the code you gave me to extract data from all excel files in a directory:
Sub Dtry()
Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
myFile = Dir("E:\Whatever Path\*.xls")
Do Until myFile = ""
Workbooks.Open "E:\Whatever Path\" & myFile
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1,0) = Workbooks(myFile).Worksheets("Application").Range("J8")
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop
End Sub
I put the code in, but there is an error with this line:
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1,0) = Workbooks(myFile).Worksheets("Application").Range("J8")
Its something to do with the .End(xlDown).Offset(1,0)part of the line.
I've tried playing with it, but cant figure out whats wrong.
Thanks for any help :)
Posted by Juan Pablo on October 04, 2001 12:53 PM
What's the error message ? (NT)
Posted by Barrie Davidson on October 04, 2001 12:59 PM
Try changing your code to:
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1,0).Value = Workbooks(myFile).Worksheets("Application").Range("J8").Value
Hope this helps you out.
Barrie
Barrie Davidson
Posted by RoB on October 04, 2001 2:55 PM
Still wont work :( more help please!
The error I'm getting is the same. :
Run-Time error '1004' :
Application-defined or object-defined error
any ideas?
thanks
Posted by RoB on October 04, 2001 3:04 PM
Think I found the problem....how do i fix it? :)
It seems the problem occurs when the sheet im copying to is blank. If i input something into cell A1, it works correctly. How would i go about fixing this?
Posted by Juan Pablo on October 04, 2001 4:34 PM
Re: Think I found the problem....how do i fix it? :)
Change this
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1,0).Value = Workbooks(myFile).Worksheets("Application").Range("J8").Value
to
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1) = Workbooks(myFile).Worksheets("Application").Range("J8")
That way you won't get that problem
Juan Pablo
Posted by RoB on October 04, 2001 5:48 PM
Thanks!!!!
