Passing arguments into function to bring data array from closed wb

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

Since these functions are expecting strings:

Function GetPath(sfilename As String) As String
Function GetFileName(sfilename As String) As String

You'll want to pass it a string:

GetArrayFromAUserSelectedWorkbook cstr(sfilename), "Sheet1", "A1:A10"



Peter Rooney wrote:
>
> 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

--

Dave Peterson

Bob,

Sorry to confuse things - GetWorkbookName isn't used in the second posting.
In the first posting, I hadn't bothered to change it, as the code didn't get
that far anyway! :-) Perhaps I'll try to change the declaration types in
GetPath and GetFileName.

Thanks again

Pete

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Or just pass the string.

Cstr(sfilename)

Peter Rooney wrote:
>
> Bob,
>
> Sorry to confuse things - GetWorkbookName isn't used in the second posting.
> In the first posting, I hadn't bothered to change it, as the code didn't get
> that far anyway! :-) Perhaps I'll try to change the declaration types in
> GetPath and GetFileName.
>
> Thanks again
>
> Pete
>
> "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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

--

Dave Peterson

Dave,

I got it, finally.

Here's the code, in the highly unlikely chance that you'll ever want to use
it for anything! :-)
Thanks for all your help.

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



"Dave Peterson" wrote:

> Or just pass the string.
>
> Cstr(sfilename)
>
> Peter Rooney wrote:
> >
> > Bob,
> >
> > Sorry to confuse things - GetWorkbookName isn't used in the second posting.
> > In the first posting, I hadn't bothered to change it, as the code didn't get
> > that far anyway! :-) Perhaps I'll try to change the declaration types in
> > GetPath and GetFileName.
> >
> > Thanks again
> >
> > Pete
> >
> > "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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Glad it worked for you and google now has a copy for all those who search.

Peter Rooney wrote:
>
> Dave,
>
> I got it, finally.
>
> Here's the code, in the highly unlikely chance that you'll ever want to use
> it for anything! :-)
> Thanks for all your help.
>
> 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
>
> "Dave Peterson" wrote:
>
> > Or just pass the string.
> >
> > Cstr(sfilename)
> >
> > Peter Rooney wrote:
> > >
> > > Bob,
> > >
> > > Sorry to confuse things - GetWorkbookName isn't used in the second posting.
> > > In the first posting, I hadn't bothered to change it, as the code didn't get
> > > that far anyway! :-) Perhaps I'll try to change the declaration types in
> > > GetPath and GetFileName.
> > >
> > > Thanks again
> > >
> > > Pete
> > >
> > > "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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave,

I'm flattered - how did you do it and how would I search for it ?

Was it really that useful..? :-I

Pete



"Dave Peterson" wrote:

> Glad it worked for you and google now has a copy for all those who search.
>
> Peter Rooney wrote:
> >
> > Dave,
> >
> > I got it, finally.
> >
> > Here's the code, in the highly unlikely chance that you'll ever want to use
> > it for anything! :-)
> > Thanks for all your help.
> >
> > 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
> >
> > "Dave Peterson" wrote:
> >
> > > Or just pass the string.
> > >
> > > Cstr(sfilename)
> > >
> > > Peter Rooney wrote:
> > > >
> > > > Bob,
> > > >
> > > > Sorry to confuse things - GetWorkbookName isn't used in the second posting.
> > > > In the first posting, I hadn't bothered to change it, as the code didn't get
> > > > that far anyway! :-) Perhaps I'll try to change the declaration types in
> > > > GetPath and GetFileName.
> > > >
> > > > Thanks again
> > > >
> > > > Pete
> > > >
> > > > "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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

You can use:
http://groups.google.com/advanced_group_search

If you need a question answered, you might be surprised at how many similar
questions have been asked/answered when you search google.

And you'll never know how many people your response could help. Every once in a
while, I get a private thank you from a post a few years old.

Peter Rooney wrote:
>
> Dave,
>
> I'm flattered - how did you do it and how would I search for it ?
>
> Was it really that useful..? :-I
>
> Pete
>
> "Dave Peterson" wrote:
>
> > Glad it worked for you and google now has a copy for all those who search.
> >
> > Peter Rooney wrote:
> > >
> > > Dave,
> > >
> > > I got it, finally.
> > >
> > > Here's the code, in the highly unlikely chance that you'll ever want to use
> > > it for anything! :-)
> > > Thanks for all your help.
> > >
> > > 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
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Or just pass the string.
> > > >
> > > > Cstr(sfilename)
> > > >
> > > > Peter Rooney wrote:
> > > > >
> > > > > Bob,
> > > > >
> > > > > Sorry to confuse things - GetWorkbookName isn't used in the second posting.
> > > > > In the first posting, I hadn't bothered to change it, as the code didn't get
> > > > > that far anyway! :-) Perhaps I'll try to change the declaration types in
> > > > > GetPath and GetFileName.
> > > > >
> > > > > Thanks again
> > > > >
> > > > > Pete
> > > > >
> > > > > "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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks, Dave - I'll take a look! :-)

"Dave Peterson" wrote:

> You can use:
> http://groups.google.com/advanced_group_search
>
> If you need a question answered, you might be surprised at how many similar
> questions have been asked/answered when you search google.
>
> And you'll never know how many people your response could help. Every once in a
> while, I get a private thank you from a post a few years old.
>
> Peter Rooney wrote:
> >
> > Dave,
> >
> > I'm flattered - how did you do it and how would I search for it ?
> >
> > Was it really that useful..? :-I
> >
> > Pete
> >
> > "Dave Peterson" wrote:
> >
> > > Glad it worked for you and google now has a copy for all those who search.
> > >
> > > Peter Rooney wrote:
> > > >
> > > > Dave,
> > > >
> > > > I got it, finally.
> > > >
> > > > Here's the code, in the highly unlikely chance that you'll ever want to use
> > > > it for anything! :-)
> > > > Thanks for all your help.
> > > >
> > > > 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
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Or just pass the string.
> > > > >
> > > > > Cstr(sfilename)
> > > > >
> > > > > Peter Rooney wrote:
> > > > > >
> > > > > > Bob,
> > > > > >
> > > > > > Sorry to confuse things - GetWorkbookName isn't used in the second posting.
> > > > > > In the first posting, I hadn't bothered to change it, as the code didn't get
> > > > > > that far anyway! :-) Perhaps I'll try to change the declaration types in
> > > > > > GetPath and GetFileName.
> > > > > >
> > > > > > Thanks again
> > > > > >
> > > > > > Pete
> > > > > >
> > > > > > "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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>