Retrieving a range of values from a closed file

  • Thread starter Thread starter michaelrlanier
  • Start date Start date
M

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, D10:D20, and D30:D40 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.
 
michaelrlanier said:
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, D10:D20, and D30:D40 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.
Click to expand...

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.)
 
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, D10:D20, and D30:D40 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.
Click to expand...

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
 
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, D10:D20, and D30:D40 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.
Click to expand...

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
 
Thanks Bart. You've obviously put some time into your response. This is much appreciated.
 
Thanks Bart. You've obviously put some time into your response. This is much appreciated.
Click to expand...

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
 
Back
Top