dear all, i want to copy a sheet in a closed workbook to one of the sheet in
an active workbook. i have the following scripts but i have the problem
1) cannot copy over
2) the vba unable to perform automatically which means there is a dialog box
asking to input the workbook name though already mentioned in the script.
pls help to modify so that can automatically copy to sheet2. button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Hi
if you really want to copy a complete sheet you have to open the other file
(you8 can hide this operation though with application.screenupdating=false).

"mango" wrote:

dear all, i want to copy a sheet in a closed workbook to one of the sheet in
an active workbook. i have the following scripts but i have the problem
1) cannot copy over
2) the vba unable to perform automatically which means there is a dialog box
asking to input the workbook name though already mentioned in the script.
pls help to modify so that can automatically copy to sheet2. button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

dear frank,
i do not get you.
may i know if you can modify my script below? let say just copy a range
a1:ff100
thanks alot


"Frank Kabel" wrote:

Hi
if you really want to copy a complete sheet you have to open the other file
(you8 can hide this operation though with application.screenupdating=false).

"mango" wrote:

dear all, i want to copy a sheet in a closed workbook to one of the sheet in
an active workbook. i have the following scripts but i have the problem
1) cannot copy over
2) the vba unable to perform automatically which means there is a dialog box
asking to input the workbook name though already mentioned in the script.
pls help to modify so that can automatically copy to sheet2. button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Hi
question remains: Dou you want to copy formats as well or are you only
interested in the values.
Also: why can't you open the other workbook: would be faster and easier
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank,
i do not get you.
may i know if you can modify my script below? let say just copy a
range
a1:ff100
thanks alot


"Frank Kabel" wrote:

Hi
if you really want to copy a complete sheet you have to open the
other file
(you8 can hide this operation though with
application.screenupdating=false).

"mango" wrote:

dear all, i want to copy a sheet in a closed workbook to one of
the sheet in
an active workbook. i have the following scripts but i have the
problem
1) cannot copy over
2) the vba unable to perform automatically which means there is a
dialog box
asking to input the workbook name though already mentioned in the
script.
pls help to modify so that can automatically copy to sheet2.
button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

dear frank,
infact its for presentation purposes.
i create another presentation workbook with buttons on sheet1. each sales
person's sales record will be shown on sheet2 when press the button in
sheet1. the sales records worksheet is maintained in other workbook. there
are total 3 different sheets sitting in different workbook for each sales
person. i have total 15 sales persons so there are total of 15 sales records
worksheets for each of 3 different workbook.
the sheet2 in presentation workbook will clear once go to next sales person.

pls help. the presentation is around. or any other way? user do not want to
open and close the workbook. they want a single workbook to link all the
sheets during the presentation.






"Frank Kabel" wrote:

Hi
question remains: Dou you want to copy formats as well or are you only
interested in the values.
Also: why can't you open the other workbook: would be faster and easier
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank,
i do not get you.
may i know if you can modify my script below? let say just copy a
range
a1:ff100
thanks alot


"Frank Kabel" wrote:

Hi
if you really want to copy a complete sheet you have to open the
other file
(you8 can hide this operation though with
application.screenupdating=false).

"mango" wrote:

dear all, i want to copy a sheet in a closed workbook to one of
the sheet in
an active workbook. i have the following scripts but i have the
problem
1) cannot copy over
2) the vba unable to perform automatically which means there is a
dialog box
asking to input the workbook name though already mentioned in the
script.
pls help to modify so that can automatically copy to sheet2.
button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Hi
still you could try something else (as working with closed workbooks is
always difficult)
- open the other workbook and simply hide it (no one will see it but
you can use for example the function INDIRECT)
- copy the data in hidden sheets of your main workbook first and
reference this sheets

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank,
infact its for presentation purposes.
i create another presentation workbook with buttons on sheet1. each
sales
person's sales record will be shown on sheet2 when press the button
in
sheet1. the sales records worksheet is maintained in other workbook.
there
are total 3 different sheets sitting in different workbook for each
sales
person. i have total 15 sales persons so there are total of 15 sales
records
worksheets for each of 3 different workbook.
the sheet2 in presentation workbook will clear once go to next sales
person.

pls help. the presentation is around. or any other way? user do not
want to
open and close the workbook. they want a single workbook to link all
the
sheets during the presentation.






"Frank Kabel" wrote:

Hi
question remains: Dou you want to copy formats as well or are you
only
interested in the values.
Also: why can't you open the other workbook: would be faster and
easier
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank,
i do not get you.
may i know if you can modify my script below? let say just copy a
range
a1:ff100
thanks alot


"Frank Kabel" wrote:

Hi
if you really want to copy a complete sheet you have to open
the
other file
(you8 can hide this operation though with
application.screenupdating=false).

"mango" wrote:

dear all, i want to copy a sheet in a closed workbook to one
of
the sheet in
an active workbook. i have the following scripts but i have
the
problem
1) cannot copy over
2) the vba unable to perform automatically which means there
is a
dialog box
asking to input the workbook name though already mentioned in
the
script.
pls help to modify so that can automatically copy to sheet2.
button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function