Hi
The code works on an unopened wbook only if Sheets(1).Name = Sheet1. How
can I change the calling string and sql string to make it work for Sheet 1
with an unknown name (because the wbook is closed)?

T.I.A.

''call sub using:
GetData fName(fNum), "Sheet1", "A1:IU1", destrange

Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange
As String, TargetRange As Range)

Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String

'''set sourcefile
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If

On Error GoTo ErrorHandler

szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

'''copy extract to xla sheet1
If Not rsData.EOF Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
MsgBox "No records returned from : " & SourceFile
End If

'''close connection
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing

Exit Sub

ErrorHandler:

On Error GoTo 0

End Sub

Sorted :)
It seems as if the parameter SourceSheet As String is removed then the query
reads Sheet1 by default regardless of name. As that is exactly what I need
then it's sorted.

The call is now:
GetData fName(fNum), "A1:IU1", destrange

and the query is:
szSQL = "SELECT * FROM [" & SourceRange$ & "];"

Thank goodness for that.

Geoff

The query is now

"Geoff" wrote:

Hi
The code works on an unopened wbook only if Sheets(1).Name = Sheet1. How
can I change the calling string and sql string to make it work for Sheet 1
with an unknown name (because the wbook is closed)?

T.I.A.

''call sub using:
GetData fName(fNum), "Sheet1", "A1:IU1", destrange

Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange
As String, TargetRange As Range)

Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String

'''set sourcefile
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If

On Error GoTo ErrorHandler

szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

'''copy extract to xla sheet1
If Not rsData.EOF Then
TargetRange.Cells(1, 1).CopyFromRecordset rsData
Else
MsgBox "No records returned from : " & SourceFile
End If

'''close connection
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing

Exit Sub

ErrorHandler:

On Error GoTo 0

End Sub