importing data from closed workbook

[lieven]

Hi,

I'm using below function to import data from a closed workbook :

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub

I am now trying to assign the result directly to a variable, but the
following fails :

myvar=application.evaluate("''c:\Myworkbook[Mysheet]'!A1:Q250")

Anyone got any ideas why , or a solution to the problem ?

 

[Dave Peterson]

You need to have the workbook open for application.evaluate() to work.

Hi,

I'm using below function to import data from a closed workbook :

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub

I am now trying to assign the result directly to a variable, but the
following fails :

myvar=application.evaluate("''c:\Myworkbook[Mysheet]'!A1:Q250")

Anyone got any ideas why , or a solution to the problem ?