Bob,

With your and Dave's help, I finally got it working! :-)

Thanks again

Pete

Sub GetArrayFromUserSelectedFiles()
Range("Target").ClearContents
sfilename = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls),*.xls", MultiSelect:=True)
If IsArray(sfilename) = True Then
FilesSelected = UBound(sfilename) - LBound(sfilename) + 1
'MsgBox ("Files selected: " & FilesSelected)
For Ndx = LBound(sfilename) To UBound(sfilename)
'MsgBox (User selected: " & sfilename(Ndx))
GetArrayFromUSF CStr(sfilename(Ndx)), "Sheet1", "A1:A10"
Selection.Offset(0, 1).Select
Next Ndx
Else
MsgBox ("No file selected.")
End If
End Sub

Function GetArrayFromUSF(FileName As String, SheetName, CellReference)
With ActiveSheet.Range(CellReference)
.FormulaArray = "='" & GetPath(FileName) & "\" & "[" &
GetFileName(FileName) & _
"]" & SheetName & "'!" & CellReference
'.Value = .Value 'Convert array formulae to values
End With
End Function

Function GetValuesFromUSF(FileName As String, SheetName, CellReference)
Selection
With ActiveSheet.Range(CellReference)
.Formula = "='" & GetPath(FileName) & "\[" & GetFileName(FileName) & _
"]" & SheetName & "'!" & CellReference
'.Value = .Value 'Convert array formulae to values
End With
End Function

Function GetPath(sfilename As String) As String
Dim iPosn As Integer
iPosn = InStrRev(sfilename, "\")
GetPath = Mid(sfilename, 1, iPosn - 1)
'MsgBox ("Path: " & vbCrLf & vbCrLf & GetPath)
End Function

Function GetFileName(sfilename As String) As String
Dim iPosn As Integer
iPosn = InStrRev(sfilename, "\")
GetFileName = Mid(sfilename, iPosn + 1)
'MsgBox ("Filename: " & vbCrLf & vbCrLf & GetFileName)
End Function


"Bob Phillips" wrote:

It sounds as though the declaration of the variable sFilename, and how the
argument is defined in GetWorkbookName (which you don't show) are not of the
same type.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter Rooney" wrote in message
...
Sorry, I realised that I didn't show how I obtained the path and filename
to
paste into the array function.

Here it all is again:

Sub GetArrayFromASelectedWorkbook()
Range("Target").ClearContents


'---------------------------------------------------------------------------
--------
sfilename = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls),*.xls", MultiSelect:=False)
If sfilename = False Then 'User DIDN'T select a file
MsgBox ("No file selected.")
Else 'User DID select a file
GetArrayFromAUserSelectedWorkbook sfilename, "Sheet1", "A1:A10"
End If
End Sub

Function GetArrayFromAUserSelectedWorkbook(sfilename As String, _
SheetName, _
CellRange As String)
With ActiveSheet.Range(CellRange)
.FormulaArray = "='" & GetPath(sfilename) & _
"[" & GetFileName(sfilename) & "]" & SheetName & "'!" &
CellRange
'.Value = .Value 'Convert array formulae to values
End With
End Function

Function GetPath(sfilename As String) As String
Dim iPosn As Integer
iPosn = InStrRev(sfilename, "\")
GetPath = Mid(sfilename, 1, iPosn - 1)
MsgBox ("Path: " & vbCrLf & vbCrLf & GetPath)
End Function

Function GetFileName(sfilename As String) As String
Dim iPosn As Integer
iPosn = InStrRev(sfilename, "\")
GetFileName = Mid(sfilename, iPosn + 1)
MsgBox ("Filename: " & vbCrLf & vbCrLf & GetFileName)
End Function

Thanks once again for your time.

Pete







"Peter Rooney" wrote:

OK, I admit defeat...

I'm trying to allow a user to select a workbook, then bring in an array
of
data from a (currently) hard coded sheetname and range.

Here's my code:

Sub GetArrayFromASelectedWorkbook()
Range("Target").ClearContents
sfilename = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls),*.xls", MultiSelect:=False)
If sfilename = False Then 'User DIDN'T select a file
MsgBox ("No file selected.")
Else 'User DID select a file
GetArrayFromAUserSelectedWorkbook GetWorkbookName(sfilename,
"Sheet1", "A1:A10")
End If
End Sub

Function GetArrayFromAUserSelectedWorkbook(sfilename As Variant, _
SheetName, _
CellRange As String)
With ActiveSheet.Range(CellRange)
.FormulaArray = "='" & GetPath(MyFileName) & _
"[" & GetFileName(MyFileName) & "]" & SheetName & "'!" &
CellRange
'.Value = .Value 'Convert array formulae to values
End With

End Function

However, when I run it, I'm getting "ByRef Argument Type Mismatch" with
"sfilename" highlighted in the row of the sub where the Function is
called.
The variable "sfilename" is declared as public variant at the beginning
of
the module.

I've banged my head against a brick wall so much that my employers are
suing
me for damage to company property.

Can anyone help, please?

Thanks in advance..

Pete