Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from closed wb
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from closed wb
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from closed wb
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments into function to bring data array from close
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" 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array function with more than 29 arguments | Excel Discussion (Misc queries) | |||
Array function with more than 29 arguments | Excel Discussion (Misc queries) | |||
Passing Arguments in Excell function | Excel Discussion (Misc queries) | |||
passing reference arguments to VBA function | Excel Programming | |||
Passing array to a function | Excel Programming |