Get Values from Closed Excel

[Guest]

I have two excel spreadsheets: test.xls and test2.xls

test.xls has information i want to automatically pull into test2.xls, and I
would like keep test.xls closed during this process. test2.xls will be open
when the command is given.

test.xls has the following characteristics
path: K:\Data Directories\
name: test.xls
sheet: mastedb Query
cells needed: A1V126

test2.xls has the follwing characteristics
path: K:\Data Directories\
name: test2.xls
sheet: test
can put data starting at cell: A1

It would be great, because I am an intern and under the gun to figure this
out, if someone could help me write the whole VBA command using the
information that I've placed above. I just can't see where to put
everything, and I keep getting errors. Thank you in advance.

 

[Ron de Bruin]

Why not open it ?

Maybe you can use ADO
http://www.rondebruin.nl/ado.htm

 

[RB Smissaert]

Try this code:

Function GetValueFromWB(path, file, sheet, ref)

'Retrieves a value from a closed workbook
'----------------------------------------
Dim strSep As String
Dim arg As String

strSep = "\"

'Make sure the file exists
'-------------------------
If Right$(path, 1) <> strSep Then path = path & strSep
If bFileExists(path & file) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If

'Create the argument
'-------------------
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(arg)

End Function

Function bFileExists(strFile As String) As Boolean
bFileExists = (Len(Dir(strFile)) > 0)
End Function


RBS

 

[Guest]

can you help me write this code? the test.xls will remain closed during this
process -- that's what my boss wants, so i have to figure out how to do this
with test.xls closed.

so, i need to ask again. can you show me how to write this VBA with the
characteristics that i outline below?

test.xls has information i want to automatically pull into test2.xls, and I
would like keep test.xls closed during this process. test2.xls will be open
when the command is given.

test.xls has the following characteristics
path: K:\Data Directories\
name: test.xls
sheet: mastedb Query
cells needed: A1V126

test2.xls has the follwing characteristics
path: K:\Data Directories\
name: test2.xls
sheet: test
can put data starting at cell: A1

 

[Ron de Bruin]

You can download the Example zip on the site
The first macro show you how to get information out of the closed file

You can use this code in your test2.xls to get the information

 

[Guest]

I've seen that code before (or something that looks very similiar)...for the
last three days, I've gone through almost every thread that says "closed
excel" (or the like) -- and my problem is that i have no idea what this stuff
means b/c there is never any good live examples...

it would probably take you or someone else minutes to show me an example
using the information that i have given (see start of thread). it's
frustrating not being able to get something to work, and i'm posting this b/c
i've already been to all the links and have read all the chat rooms...and i'm
at a lost to get it working.

can you help me out with my specific example? it would be greatly
appreciated.

 

[RB Smissaert]

OK, if I take you exact example (apart from the path):

Sub testing()

Dim arr

arr = GetValueFromWB("C:\", _
"test.xls", _
"mastedb Query", _
Range(Cells(1), Cells(126, 126)).Address)

Range(Cells(1), Cells(126, 126)) = arr

End Sub

I have tested this and it work fine.

RBS

 

[RB Smissaert]

To deal with your sheet test as well in the open workbook:

Sub testing()

Dim arr

arr = GetValueFromWB("C:\", _
"test.xls", _
"mastedb Query", _
Range(Cells(1), Cells(126, 126)).Address)

With Sheets("test")
Range(.Cells(1), .Cells(126, 126)) = arr
End With

End Sub


RBS