Retrieving a range of values from a closed file

[michaelrlanier]

I would like to post a range of values from a closed file on the same drivewithout opening the closed file. I need the values of ranges B10:B20, B30:B40, D1020, and D3040 in the closed file to be posted in the same ranges of my open file. Let's call the open file "Apples" and the closed file "Oranges." I cannot use the usual links because I don't want to be prompted about updating links when I open the "Apples" file. If it is only a matter of preventing the update prompt, that would be very acceptable. Can you offer a solution? Thanks.

 

[Auric__]

I would like to post a range of values from a closed file on the same
drive without opening the closed file. I need the values of ranges
B10:B20, B30:B40, D1020, and D3040 in the closed file to be posted
in the same ranges of my open file. Let's call the open file "Apples"
and the closed file "Oranges." I cannot use the usual links because I
don't want to be prompted about updating links when I open the "Apples"
file. If it is only a matter of preventing the update prompt, that would
be very acceptable. Can you offer a solution? Thanks.

In order to get the information out of the workbook, *something* has to open
it *somehow* (unless you want to get *really* low-level and read the data
directly off the hard drive -- a non-trivial task). The easiest way is to
just open it in Excel and not update the links:
Set foo = Workbooks.Open("Oranges", 2)
....or *always* update the links:
Set foo = Workbooks.Open("Oranges", 3)

Look up Worbooks.Open in the help file for more info.

(Assigning to a variable makes it easier close the workbook when you're done
with it:
foo.Close False
"False" here prevents the workbook from saving when it's closed.)

 

[GS]

I would like to post a range of values from a closed file on the same drive
without opening the closed file. I need the values of ranges B10:B20,
B30:B40, D1020, and D3040 in the closed file to be posted in the same
ranges of my open file. Let's call the open file "Apples" and the closed file
"Oranges." I cannot use the usual links because I don't want to be prompted
about updating links when I open the "Apples" file. If it is only a matter of
preventing the update prompt, that would be very acceptable. Can you offer a
solution? Thanks.

You can do this with ADODB. Here's where to start...

http://www.appspro.com/conference/DatabaseProgramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[michaelrlanier]

Thanks Auric and Gary. I'll check out both your suggestions.

Michael

 

[bart.smissaert]

I would like to post a range of values from a closed file on the same drive without opening the closed file. I need the values of ranges B10:B20, B30:B40, D1020, and D3040 in the closed file to be posted in the same ranges of my open file. Let's call the open file "Apples" and the closed file "Oranges." I cannot use the usual links because I don't want to be promptedabout updating links when I open the "Apples" file. If it is only a matterof preventing the update prompt, that would be very acceptable. Can you offer a solution? Thanks.

You could also try using the old ExecuteExcel4Macro, with code like
this:

Function GetValuesFromWB(vPath, vFile, vSheet, vRef) As Variant

Dim c As Long
Dim r As Long
Dim vArr As Variant
Dim strArg As String
Dim lRows As Long
Dim lCols As Long

If Right$(vPath, 1) <> "\" Then
vPath = vPath & "\"
End If

If bFileExistsVBA(vPath & vFile) = False Then
GetValuesFromWB = "File Not Found"
Exit Function
End If

strArg = "'" & vPath & "[" & vFile & "]" & vSheet & "'!" & _
Range(vRef).Range("A1").Address(, , xlR1C1)

lRows = Range(vRef).Rows.Count
lCols = Range(vRef).Columns.Count

If lRows = 1 And lCols = 1 Then
GetValuesFromWB = ExecuteExcel4Macro(strArg)
Else
ReDim vArr(1 To lRows, 1 To lCols) As Variant
For r = 1 To lRows
For c = 1 To lCols
vArr(r, c) = ExecuteExcel4Macro("'" & vPath & "[" & vFile &
"]" & _
vSheet & "'!" & _
Range(vRef).Cells(r,
c).Address(, , xlR1C1))
Next c
Next r
GetValuesFromWB = vArr
End If

End Function

Function bFileExistsVBA(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExistsVBA = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function

Sub Test()

Dim v As Variant

v = GetValuesFromWB("C:\testing\", "GetValuesTest.xls", "Sheet1",
"B2:C3")

Cells.Clear
Range(Cells(2), Cells(2, 3)) = v

End Sub


RBS

 

[michaelrlanier]

Thanks Bart. You've obviously put some time into your response. This is much appreciated.

 

[bart.smissaert]

Thanks Bart. You've obviously put some time into your response. This is much appreciated.

No trouble, see if it suits your needs.
One thing is that empty cells will produce a zero and not sure that
can be avoided.

RBS