I'm trying to use the following script to copy a variable named range
in an unopened workbook into the active workbook that the code is
running in.
I have a couple of problems.
I got a good part of the script from the MS KB article
herehttp://support.microsoft.com/kb/257819)

I have two problems with the below code:
I get a "Run time error 424; Object required" error for the line "Set
Proj_Conn = CurrentProject.Connection"
while I can reference the named range in the 2nd file, I can't seem to
get it to copy the range into the active sheet.
Any ideas would be greately appreciated
Code follows:

Sub copy_3()
'Declare variables
Dim strQuery As String
Dim Proj_Conn As ADODB.Connection
Set Proj_Conn = CurrentProject.Connection
Dim userInput As String
Dim rst As New ADODB.Recordset
rst.ActiveConnection = Proj_Conn
' Set variable "file_name" to data source file name
'
file_name = "Excel ('Excel (*.xls*), *.xls*')"
'
' Prompt user for file name and path for data source
'
fileToOpen = Application.GetOpenFilename(file_name)
'
' Get Variable "Current_Month" for use in selecting active sheet in
opened file
'
userInput = Application.InputBox(prompt:="Enter Month", Type:=2)
'current_month = Application.InputBox(prompt:="Enter Month",
Type:=2)
current_month = userInput

With Proj_Conn
..Provider = "Microsoft.Jet.OLEDB.4.0"
..ConnectionString = "Data Source='" & fileToOpen & " '; Extended
Properties=HTML Import;"
..Open
End With

'copy named range into clipboard

strQuery = "SELECT * FROM '" & current_month & "';"

' select active workbook and reference worksheet
Windows("PM Schedule 2009.xlsm").Activate
Sheets("reference").Select

Range("a1").Activate

Range("a1").CopyFromRecordset rst
Range("a1").Select

' close connection
rst.Close
conn.Close

End Sub