Solved

Macro to Run Macro in Network WB

Posted on 2014-02-04
49
223 Views
Last Modified: 2014-02-05
Hi Experts,

Is there any way to activate macro of network WB by Clicking on Local machine WB.

Thanks
0
Comment
Question by:itjockey
  • 29
  • 20
49 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Say this again ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You need to be more explicit.
I guess what you want (probably) is having a workbook on your local machine that have a macro and you wonder if

there is a way that you can have that macro open an other workbook on network drive that contain a macro and have it run that macro from within your workbook.

If this is what you want then the answer is YES but you should be careful as to
1) having access to the network drive
2) that the macro that will run on the network from within your local workbook if not well written can then mess boss your workbook and the network workbook.

It should be clear as to how to make it work. Nothing comes just packaged like this to work.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ok on my Network there is one WB - Macro Copy Range & past special - value to other cells - macro assign key is "ctrl+r".

On local machine I had vlookup these values. so need one button in local machine WB to activate network WB macro "ctrl+r".

Network machine wB is Open all the time.


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well this is different now, what you need is to have a certain file that have functionalities be available all the time on your machine.

2 ways
1) Have that workbook in your xlstart menu so when you start excel it started it automatically and its functions will become available to your workbook. (have not tried it but presume it should be fine)
2) HAve that macro saved as ADDIN and you activating this addin therefore these functions will be available each and every time you open a workbbok on your machine. Also this has to be designed accordingly.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
your call Sir, it is up to you. both ways very new for me.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok pls post the CTR r workbook so I look at it and will take it from there
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
here it is Network WB
Test.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok will attend lateron
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
okie

Thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
One thing to mention as Network WB - Macro - populate data in desire cell perfectly. But if I open that WB in my machine - data doesn't change. I have to save network WB then only it accurate data appear in my local machine WB (Look up Values).


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok I checked this file Text.xlsm
it has Macro1 that basically do a selection of M13 to M26 and do a copy paste values in K28

how do activate this and how does it emulate CTRL 'r' ???

pls tell step by step what you do. as what I see here has nothing that sets CTRL 'r' to paste values.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Keyboard short Cut ctrl+r.

it copy values (which is continuous changing) from range M13 to M26 & past to K28.
this is shared WB. i have different WB in different Computer which has vlookup cell reference from K28:M41 in this WB.

Difficulties - i have to run ctrl+r in shared WB & have to save & then have to open from my machine to get refreshed values for vlookup reference.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no no need to save.
I still don't understand what triggers the CTRL 'r' to be linked to Macro1 where did you set this ??
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok GOT IT !!!
Always room to learn from you guys !!!

Try this file and put it on the server and tell me if it does the job !
gowflow
Test-V01.xlsm
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ok GOT IT !!!
Always room to learn from you guys !!!

Dint get you. actually i am disciple of yours....   :)
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Wrong !!! we always learn this is how we move ... the minute you stop learning you decline. At least this is my moto.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ok see you add this line
ActiveWorkbook.Save

Open in new window

but how this macro trigger from my machine - from my different WB other than this.


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I thought you said
When I save the workbook and close then ... its ok !!! and this is what I did ...
So what do you want now ? you want to do CTRL 'r' from your machine and have it do what ? you want to paste this data in your own workbook I presume right not just where it is
am I correct ? you need to be clear on what you want I cannot guess the question and the answer ...
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ok let clear 1st
2 WB
1 Test.xlsm which have Macro keyboard shortcut "ctrl+r". this WB is shared on network.
2 Data.xlsm which have formula of VLOOKUP reference to Test.xlsm.My Machine (PC)
  i had created formula by opening both WB on my  machine.

i want in Data.xlsm have Macro button which activate Test.xlsm WB Keyboard shortcut macro "ctrl+r".

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok let me get this correctly.

when you activate CTRL 'r' it copies from range M13 to M26 & past to K28
I need to know the copy will be from the workbook that has the macro that is on the server and the paste K28 on your machine ?

or ...
you need to tell me.
Also this VLOOKUP I don't see it in the file you posted where is it ?
still don't get the idea

pls explain in English with values in cell (make it simple) referring to WBserver and WBmine so I know what is going on.
gowlfow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
i had attached both WB. you can see formulas in WB Data.xlsm
Data.xlsm
Test.xlsm
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Test.xlsm web server
Dat.xlsm myWB

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok thank you I see you have a vlookup that get data from this test file then SO WHAT ???

what do oyu want to achieve ?? why you want to active CTRL 'r' and what it is supposed todo ??
How you do it now and what you want the macro or automation to do ?

I don't understand pls explain it is getting frustrating
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ctrl+r copy data which is continuously changing sec by sec & past special value to other cell range in same WB so it is now static in WB Test.xlsm

 in WB Data.xlsm get new values from WB Test.xlsm(VLOOKUP values).

currently i am got to that server WB Test.xlsm run macro & then come to my machine Open Test.xlsm in my machine & my values get changed in WB Data.xlsm.

So any way out to run that ctrl+r macro from my machine?

Thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sir apology for my language as English is not my 1st language. i had done my main education in my native language. so i find difficulties to express what i want.


Thank You
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sir you are still in this question.....i am not in a hurry ...just asking...


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes just saw your post.

So let me recap now.
To get the new values you need to run the macro but the macro only copy a range to an other range, if that data keeps changing why then you don't link your VLOOKUP to the range that keep changing this way you always have it updated and no need to run the macro like can't you have at the source like K13:M26 instead of K28:M31 ? or this data will keep changing and what you want is to get the figure when you activate the macro like take a snapshot and the instant t ?

gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
open Test.xlsm file from my computer then range K13:M26 & Range K28:M31 look like this.Test.xlsm File When open from different Computer
i understand live updated data is much accurate then static. where you find #Name#? all are formulas. i had intentionally removed formula in that ranges as there is no use on your computer & it may create more confusion.


Thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sir may i take your leave for the day? i am in night shift. it is too late here (03:00 AM).Need to  go back to my home.


Thanks You
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Good Night Sir.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Well I think I got it !!! :)

If this is the path and file name on your server then this file should do it
"\\mak3\Sharing\Test.xlsm"

First You need to either copy this file Test.xlsm that I am attaching or if it is not the same then you will need to do the following:

1) Make sure macros are not activated (like first de-activate macros EXcel options and disable macros)
2) Open your real file that is on the server (like the one you posted Test.xlsm
3) Locate the Macro1 select the whole code it is now in a Module do Cut then doubleclick on ThisWorkbook it will open a blank page and paste the code there. We want to move Macro1 from the Module1 to Thisworkbook.
4) Change Sub Macro1
to
Public Sub Macro1
5) Save this file keep it opened.
6) Goto to the Developper tab and press on Macro and you will see Thisworkbook!Macro1 click on Options and put in the box r near CTRL as a shortcut.
7) Press ok and save and Exit this workbook.

Load the second file Data mine.xlsm put in on your pc and make sure macros are running.
Now you need to have on the server or at the above location this exact file which I took from the link in the Excel you posted. If it is this one then simply press on the Button Get New Values and see the changes in the fields. If your data keeps on changing then each time you press on the button it is like you are activating CTLR r on the server.

If the file name on the server is not
"\\mak3\Sharing\Test.xlsm"
then change it in the macro in this file easy to find. You will also for sure will need to change the refrences to the fields you have in the Excel as they are all pointing to
\\mak3\Sharing\[Test.xlsm]Sheet1'!K$28:M$41

Let me know
gowflow
Test.xlsm
Data-mine.xlsm
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sure I will do it when I back to work on tomorrow.i am on my way back to home.posting this comment via cell phone.

Thank you very much
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any chance ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sir do u want my remote? while clicking Get Values Button all formulas get changed. I don't know why?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Or you want me to close this question by accepting. as I my mind diverted from my core issue on which I am working on to side line issues.


Thanks
0
 
LVL 8

Author Closing Comment

by:itjockey
Comment Utility
Thank You
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
For sure the value changes when you click on the button as it do A
1) CTRL r on the server which brings new values
2) Save the file on the server
3) As your values in your file are linked to the server then they get updated automatically this is why you see data changing.
4) if you click again it is as if you went back to the server and opened that file and ran the macro by clicking CTRL r the same the file then back on your workstation to get the new values  !!!

Isn't it this what you were after ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ohh my mistake I dint change path.... extremely sorry

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok so it is working ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
on which I am working on 1 set of question is ready may post now?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes for sure, but pls is this one working or not ? I am not after point but after solutions
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
as I had change path & testing now.
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
One thing for all my WB password is "gowflow" & this is the link for next question Scoring.


Thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
if you are busy in something else then just post comment "I will look in to this later" for my next question.


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
pls change the password it is not ethical visavis other Experts that may participate in this question.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
I cant test fully now as Live trading is on I can do only after US market close. Sure I will let you know regarding this question.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
ok this regarding this question. I had saved your files instead of mine original one. Test.xlsm in server & Data-Mine.xlsm in local machine. path changed but while clicking button Get Values nothing happens. Neither macro run & nor even retrieve Vlookup values which I had changed manually in server machine - Test.xlsm


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Weired I tested them here and it worked just fine.
You need to make sure of the following:

1) The path in the macro of the file should be the correct one.
2) In the vlookup formulas also the path should be the right one maybe you have them mixed up like the first time,

The fact that you first commented that the values changes this indicate that it is picking the correct file as I recall you said that the data on the server keeps changing so if in the lookup you had seen it changing then it is correct.

don't know what you did now you need to check path for both macro in Data mine and vlookup formula in Data mine then should point to Test
AND most important is that in test or the file you should do like I said put the macro1 (CTRL r in thisworkbook you should move it from Module1 to Thisworkbook) or else it will not work !!!

gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sure I ll check tomorrow.

Thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now