MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!!!!