copy worksheet from closed workbook to active workbook using vba

G

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
 
G

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

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


Frank Kabel said:
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 said:
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
 
F

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

mango said:
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 said:
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 said:
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
 
G

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.






Frank Kabel said:
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 said:
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 said:
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
 
F

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

mango said:
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 said:
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 said:
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
 
G

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.


Frank Kabel said:
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 said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top