copy worksheet from closed workbook to active workbook using vba

[Guest]

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

 

[Guest]

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).

 

[Guest]

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

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).

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

 

[Frank Kabel]

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

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

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).

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

 

[Guest]

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.

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

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

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).

:

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

 

[Frank Kabel]

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

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.

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

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


:

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).

:

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

 

[Guest]

thanks for yr reply. i do not really understand what u mean.
can give me an example how to use the INDIRECT and what u mean by reference
sheets?
I still can use button to do the presentation?
sorry as cause you alot of problem.

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

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.

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

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


:

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).

:

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