Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mango
 
Posts: n/a
Default copy worksheet from closed workbook to active workbook using vba

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



  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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



  #3   Report Post  
mango
 
Posts: n/a
Default

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



  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

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




  #5   Report Post  
mango
 
Posts: n/a
Default

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







  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

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






  #7   Report Post  
mango
 
Posts: n/a
Default

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" wrote:

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy answer to another worksheet mjohnson0321 Excel Discussion (Misc queries) 3 January 22nd 05 02:11 PM
copy COLUMN from 1 worksheet to another (in a different workbook) DavidB Excel Discussion (Misc queries) 3 January 15th 05 03:47 PM
how do I make a copy of a worksheet and retain formulas but not data FireBrick Setting up and Configuration of Excel 2 December 29th 04 08:33 PM
Need Help to Prevent Copy Function Use in a Read-Only Workbook Nuts4pi Excel Worksheet Functions 2 November 20th 04 04:48 PM
Copy worksheet with Pivot Table and break link to original workshe setter-lover Excel Worksheet Functions 0 November 18th 04 10:29 PM


All times are GMT +1. The time now is 11:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"