Can i use VBA toretrive data from a workbook that is currently closed and
enter the cell values specified into a range in an open workbook.worksheet
that collates information fromt he ranges in numerous closed workbooks?

To read from or write to a workbook, you must open it. You need not show it,
it can remain invisible to users, but you must open it, and close it when
done.

"Alan M" <Alan wrote in message
...
Can i use VBA toretrive data from a workbook that is currently closed and
enter the cell values specified into a range in an open workbook.worksheet
that collates information fromt he ranges in numerous closed workbooks?

You can open, read and close workbooks programmatically, however, and do
virtually anything that can be done manually with VBA.

"Bob Kilmer" wrote in message
...
To read from or write to a workbook, you must open it. You need not show
it,
it can remain invisible to users, but you must open it, and close it when
done.

"Alan M" <Alan wrote in message
...
Can i use VBA toretrive data from a workbook that is currently closed
and
enter the cell values specified into a range in an open
workbook.worksheet
that collates information fromt he ranges in numerous closed workbooks?

Alan,
It's been sort of too late though...but i was trying to find something else
and came across this question.
VBA does not include a method to retrieve a value from a closed workbook
file. You can, however, take advantage of Excel's ability to work with linked
files by using GetValue VBA function that retrieves value from closed
workbook. It does so by calling XML Macro.

Below is the code for finding the value of A1 cell in closed workbook :
abc.xls

Sub TestGetValue()
p = "C:\Documents and Settings\Desktop\ABC"
f = "abc.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)

End Sub


Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

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

GetValue = ExecuteExcel4Macro(arg)
End Function

Source : John Walkenbach book : Excel 2002 Power Programming with VBA

"Bob Kilmer" wrote:

You can open, read and close workbooks programmatically, however, and do
virtually anything that can be done manually with VBA.

"Bob Kilmer" wrote in message
...
To read from or write to a workbook, you must open it. You need not show
it,
it can remain invisible to users, but you must open it, and close it when
done.

"Alan M" <Alan wrote in message
...
Can i use VBA toretrive data from a workbook that is currently closed
and
enter the cell values specified into a range in an open
workbook.worksheet
that collates information fromt he ranges in numerous closed workbooks?

Does anyone know whether the workbook needs to be linked in order to use this
code, or can you just specify the path etc?

"Ajit" wrote:

Alan,
It's been sort of too late though...but i was trying to find something else
and came across this question.
VBA does not include a method to retrieve a value from a closed workbook
file. You can, however, take advantage of Excel's ability to work with linked
files by using GetValue VBA function that retrieves value from closed
workbook. It does so by calling XML Macro.

Below is the code for finding the value of A1 cell in closed workbook :
abc.xls

Sub TestGetValue()
p = "C:\Documents and Settings\Desktop\ABC"
f = "abc.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)

End Sub


Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

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

GetValue = ExecuteExcel4Macro(arg)
End Function

Source : John Walkenbach book : Excel 2002 Power Programming with VBA

"Bob Kilmer" wrote:

You can open, read and close workbooks programmatically, however, and do
virtually anything that can be done manually with VBA.

"Bob Kilmer" wrote in message
...
To read from or write to a workbook, you must open it. You need not show
it,
it can remain invisible to users, but you must open it, and close it when
done.

"Alan M" <Alan wrote in message
...
Can i use VBA toretrive data from a workbook that is currently closed
and
enter the cell values specified into a range in an open
workbook.worksheet
that collates information fromt he ranges in numerous closed workbooks?

The workbook does not need to be linked.

the variable p holds the path
--
Regards,
Tom Ogilvy

"Delboy" wrote in message
...
Does anyone know whether the workbook needs to be linked in order to use
this
code, or can you just specify the path etc?

"Ajit" wrote:

Alan,
It's been sort of too late though...but i was trying to find something
else
and came across this question.
VBA does not include a method to retrieve a value from a closed workbook
file. You can, however, take advantage of Excel's ability to work with
linked
files by using GetValue VBA function that retrieves value from closed
workbook. It does so by calling XML Macro.

Below is the code for finding the value of A1 cell in closed workbook :
abc.xls

Sub TestGetValue()
p = "C:\Documents and Settings\Desktop\ABC"
f = "abc.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)

End Sub


Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

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

GetValue = ExecuteExcel4Macro(arg)
End Function

Source : John Walkenbach book : Excel 2002 Power Programming with VBA

"Bob Kilmer" wrote:

You can open, read and close workbooks programmatically, however, and
do
virtually anything that can be done manually with VBA.

"Bob Kilmer" wrote in message
...
To read from or write to a workbook, you must open it. You need not
show
it,
it can remain invisible to users, but you must open it, and close it
when
done.

"Alan M" <Alan wrote in message
...
Can i use VBA toretrive data from a workbook that is currently
closed
and
enter the cell values specified into a range in an open
workbook.worksheet
that collates information fromt he ranges in numerous closed
workbooks?