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: A1:DV126

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.

Why not open it ?

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


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


"jm" wrote in message ...
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: A1:DV126

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.

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: A1:DV126

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

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

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


"jm" wrote in message ...
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: A1:DV126

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

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


"jm" wrote in message
...
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: A1:DV126

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.

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.

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



"jm" wrote in message
...
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.

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


"jm" wrote in message
...
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.