Solved

Macro to Run Macro in Network WB

Posted on 2014-02-04
49
226 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
ID: 39832330
Say this again ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832365
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
ID: 39832409
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 29

Expert Comment

by:gowflow
ID: 39832424
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
ID: 39832439
your call Sir, it is up to you. both ways very new for me.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832441
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
ID: 39832459
here it is Network WB
Test.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832483
ok will attend lateron
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39832523
okie

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39832542
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
ID: 39832898
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
ID: 39832952
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
ID: 39832973
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
ID: 39833002
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
ID: 39833021
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
ID: 39833031
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
ID: 39833056
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
ID: 39833208
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
ID: 39833294
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
ID: 39833321
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
ID: 39833359
i had attached both WB. you can see formulas in WB Data.xlsm
Data.xlsm
Test.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39833365
Test.xlsm web server
Dat.xlsm myWB

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39833434
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
ID: 39833457
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
 
LVL 8

Author Comment

by:itjockey
ID: 39833474
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
ID: 39833621
Sir you are still in this question.....i am not in a hurry ...just asking...


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39833787
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
ID: 39833849
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
ID: 39833954
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
ID: 39834047
Good Night Sir.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39834114
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
ID: 39834147
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
ID: 39835007
Any chance ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39835501
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
ID: 39835537
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
ID: 39836241
Thank You
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39836259
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
ID: 39836408
ohh my mistake I dint change path.... extremely sorry

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39836417
ok so it is working ?
gowflow
0
 
LVL 8

Author Comment

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

Expert Comment

by:gowflow
ID: 39836422
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
ID: 39836432
as I had change path & testing now.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39836447
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
ID: 39836458
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
ID: 39836471
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
ID: 39836741
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
ID: 39837211
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
ID: 39837335
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
ID: 39837341
Sure I ll check tomorrow.

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 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