Get values from a closed workbook

[kev_06]

I got this code from the website:

Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:A30"
End Sub

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

Can someone explain how it works? It looks like the path/file name is already configured, so why (when I run the code) does an "Update Records" dialog box pop up?

[halim]

Dear KEV06,

Let me try to explain you that :
(the explanation is after " '-->" and " _" mark & ended by dot.)

Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:A30"
' --> call _
GetValuesFromAClosedWorkbook sub event to raised up the event.
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, _
sName, cellRange As String)

With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & fName & "]" & sName &
"'!" & cellRange ' --> excel _
standard way of linked cells to another Workbook that
currently not open.
.Value = .Value ' --> return formularray to value.
End With
End Sub


Or you can contact me directly to [email protected]
thats all,

halim



kev_06 menuliskan:
> I got this code from the website:
>
> Sub test()
> GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:A30"
> End Sub
>
> 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
>
> Can someone explain how it works? It looks like the path/file name is
> already configured, so why (when I run the code) does an "Update
> Records" dialog box pop up?
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=559565

[Ron de Bruin]

See
http://www.rondebruin.nl/copy7.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"kev_06" <[email protected]> wrote in message
news:[email protected]...
>
> I got this code from the website:
>
> Sub test()
> GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:A30"
> End Sub
>
> 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
>
> Can someone explain how it works? It looks like the path/file name is
> already configured, so why (when I run the code) does an "Update
> Records" dialog box pop up?
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=559565
>

[Nigelba]

I have been successfully using this method for a few months but...
1) when I first started using it a strange problem occurred but it went away
2) it has just come back

The problem is that when the array formula is inserted sometimes it adds an extra backslash after the path so the formula breaks.
If I go and open the target file and close it again the formula works.

For example: ='C:\WeekRep\\[my_report.xlsm]Risk Log'!E10:AA10 instead of:
='C:\WeekRep\[my_report.xlsm.xlsm]Risk Log'!E10:AA10

Anyone come across this before: