copy data from a closed workbook

[Christy]

I found this code on Ron's site and it is exactly what I need. I was able to
download and run his example just fine but when I put the exact code in my
workbook I get 'Subscript out of range'. I pasted the functions in one
module and the macros in another just like in the example.

I have not even tried to modify the from and to ranges yet. Any help will be
greatly appreciated.

Sub GetData_Example2()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If FName = False Then
'do nothing
Else
GetData FName, "Sheet1", "A1:C5", Sheets("Sheet1").Range("A1"), False
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub

Christy

 

[Ron de Bruin]

Hi Christy

You must set a reference
See website
http://www.rondebruin.nl/ado.htm

 

[Ron de Bruin]

Hi Christy

See also this page if you want to create formula links
http://www.rondebruin.nl/summary2.htm

 

[Christy]

Hi Ron,

THNAKS for all the help you give to everyone on this forum.

I guess I don't know what you mean. I checked "Microsoft ActiveX Data
Objects 2.5 Library". Your sample runs but not my workbook??

 

[Ron de Bruin]

I have already set the reference in the sample workbook on my site.

If you copy the code in your own workbook you must do it in this workbook also.
In the VBA editor select your file first in the project explorer and then set the reference.
Save the file then.

 

[Christy]

It is set in both the workbook that I am running the code from and the closed
workbook I want to get the data from and I still get the same error???

 

[Ron de Bruin]

It is set in both the workbook that I am running the code from
Send me your test file private and I look at it