A macro which can paste a value into a different workbook (not worksheet)?

[pagination1]

Hi all,

I was wondering:

Is it possible to write a macro code, which will paste a predetermined value into a different workbook?

For example, assume I have 3 open workbooks, ie, 3 separate instances of Excel running.

When I run the macro in Workbook 1, I want the value “1” to be pasted into cell A1 of workbook 3. (C:\workbook1.xlsx)
When I run the macro in Workbook 2, I want the value “2” to be pasted into cell A1 of workbook 3. (C:\workbook2.xlsx)

Thus:

The value of Cell A1 in Workbook 3 changes as I specified above (C:\workbook3.xlsx).

Is it possible to write a code that does this?

Many thanks in advance.

ps, I hope I have used correct terminology re: workbooks/worksheets. I am talking about 3 completely separate instances of excel running, and not using tabs within one running excel program.

[Norie]

Why 3 separate instances?

[Andy Pope]

Do you really have 3 separate instances of Excel running? is so why.

the Excel application (not 2013) can have multiple workbooks open.
Using the ribbon View > Windows > Switch windows, Do you see all 3 workbooks in the list with all 3 workbooks are open?

[pagination1]

test test test

[pagination1]

I am having difficulties posting my response - I have no idea what is going on - I can only post brief sentences.

Am trying to figure this out.

[pagination1]

Hi Norie/Andy,

A good question!

I am running a complicated Excel set up, whereby (in fact) 8 completely different running instances of Excel change the values within the 9th and final separate running spreadsheet.

It sounds strange, I know, but the 8 Excel programmes each act as a separate "control panel" (if you like), which each change the value in the 9th spreadsheet, which is the "main" spreadsheet. (Each of the "control panel" spreadsheets are not full screen, they are reduced in size, so that they are only about 10cm by 5cm on the monitor, but this is sufficient space to input the required data).

[pagination1]

The project I am working on is actually based on a Game Theory study. Putting it simply, imagine you have 8 card tables open and each card table corresponds to one of the 8 "control panel" spreadsheets...

[pagination1]

As the mathematical model changes for, eg "card table 1", I run the macro from worksheet 1 and it will change the values in the "main" spreadsheet and spit out the answer. And so on.
Is there a solution as per my post 1? Many thanks!

[pagination1]

Whilst the above sounds complicated, all I actually need to figure out is how to create a macro as per the above, and this would do all I require.

Apologies for lots of posts, the website kept crashing when i put this all in one post.

[Norie]

How are you controlling all these instances of Excel?

[pagination1]

Via mouse and keyboard - or is that not what you meant?

[pagination1]

Although I am fearful the thread is going on a bit of a tangent! Is there a code for the scenario I set out in my very first post?

I would be very grateful for someone's help!

[pagination1]

Although I am fearful the thread is going on a bit of a tangent! Is there a code for the scenario I set out in my very first post?

I would be very grateful for someone's help!

[Norie]

Not quite seeing how it's going off in a tangent.

I'm trying to ask relevant questions to try and establish how you have things set up.

If you were control each instance of Excel then this would be pretty straightforward as you could refer to the instances directly in code.

If they have all been opened manually it makes things harder.

[pagination1]

Hi Norie,

I meant that I was worried I was sending things off tangent with my explanations - not your questions!

So, I have 8 different instances of excel running. Lets say each of these instances are named file named between 1-8. These are the "control panel" excel spreadsheets, which are each minimized to around 10cmx5cm.

I then have a 9th and final excel spreadsheet, which is the main data sheet. I would like each of the 8 "control panel" spreadsheets to change the value of cell A1, in the "main data sheet".

Keeping it simple, each of these "control panel" spreadsheets has a single macro code (which I am needing from kind people such as yourself).

When it is run, say, from Excel Spreadsheet "1", I would like the number "1" to appear in cell A1 of the "main spreadsheet".

When it is run, say, from Excel Spreadsheet "2", I would like the number "2" to appear in cell A1 of the "main spreadsheet".

When it is run, say, from Excel Spreadsheet "3", I would like the number "3" to appear in cell A1 of the "main spreadsheet".

And so on. I assume that I would only need one code - I could then amend the code as required (eg, if you give me a code that only puts the number "1" within cell A1 of the "main spreadsheet", I could amend this code, to use within another macro, so that, eg, the number "2" or "1,000" (or whatever I choose) would be input into cell A1 of the "main spreadsheet").

I have included the file paths in my above post, because I think you would need to refer to these paths within the macro?

All 9 of these instances of Excel are open at the same time. They do not need to be opened by the macro. I will open all 9 of the spreadsheet manually, before I start running macros.

I wonder if that a bit clearer?

Many thanks,

[pagination1]

Do you really have 3 separate instances of Excel running? is so why.

the Excel application (not 2013) can have multiple workbooks open.
Using the ribbon View > Windows > Switch windows, Do you see all 3 workbooks in the list with all 3 workbooks are open?

Hi Andy - no, I do not see all of the workbooks here. I think this is because I completely open a new instance of Excel for each of my spreadsheets (which I have to do for my project), and not open different worksheets within the one running instance of Excel. I am running Excel 2007.

Does that help with a solution?

[romperstomper]

Assuming you know the path of the target workbook, you should be able to use GetObject to obtain a reference to it, then you can do whatever you need to it.

[pagination1]

Assuming you know the path of the target workbook, you should be able to use GetObject to obtain a reference to it, then you can do whatever you need to it.

Thanks Romper, I've done some googling on this, but can't quite get this to work. I tried the below code, but it didn't work.

The idea behind this code I'm trying to to write using GetObject is that, in workbook 1 (C:\nwheeler\book1.xlsx), I can run a macro which then copies the value within A21 of workbook 2, into C28 of workbook 2. (C:\nwheeler\Book2.xlsx)

However, this doesn't work - (it just copies the value of A21 from workbook 1 into C28 of workbook 1). Would you be kind enough to tweak this code for me?

Please Login or Register  to view this content.

[romperstomper]

I've just replied in your MrExcel thread as it happens.

[pagination1]

I've just replied in your MrExcel thread as it happens.

Thanks! Excel fans seem to sign up to every forum there is! I have replied to you at Mr Excel, so as not to cross thread, cross forum!

[pagination1]

This thread has been solved by using the following code. This code I can input into each of my 8 "control panel" workbooks, and amend as required, so that it will put whatever value I wish into the 9th "main" workbook.

Thank you to all for your comments and help, it was an interesting process, and to romperstomper who solved this.

Please Login or Register  to view this content.

[arlu1201]

2 pointers -

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Also, please mark this thread as solved.

[pagination1]

2 pointers -

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Also, please mark this thread as solved.

Noted and understood for future reference.

Many thanks.