How to target instances of Excel

danacton

New Member
Joined
Dec 6, 2006
Messages
32
Hello and Merry Christmas;
Thank you ahead for any help on this... (sorry for the longwindedness)
I'm not sure how to attach my working files, but they're pretty easy to recreate.

1. At work daily I generate a report from our ERP and I export a cvs into Excel and it pops on my desktop unsaved (very simple ~ 5000 lines). I use this method because it is so much easier than having to download the full path of the network (formal computer name/users/user/folder on computer - etc.) It is also easier to teach others in my absence to do it this way (I 'think' this is a version of Excel saved on the server).
2. I also open up an attachment from my mail everyday that is attached as an excel - thus opening an entirely new instance of excel (I'm not a genius, guessing this opens with the version saved on my computer which is now office 365 I think excel 2016 - we just migrated).

Summary:

I have two open instances of Excel and filenames that change (one daily via email) and the other each time the report is generated on the ERP (the server instance of Excel).

Progress - (Sort of):
I have been able to figure out how to get these to work in a single instance of excel. (see code and attached image) with a very simple test of some data in column A.

It works! I can launch the subs from one workbook (SubcControlCenter.xls) that I have saved to my desktop - On click, it looks for a filename 'LIKE' "From" (which will always be open) and copies that data and at the end of the first sub the second sub is called which looks for a filename 'LIKE' "To" (which will always be open) to where it pastes the data in (A1). Note: In the real world the first characters of the filenames are predictable so this 'wildcard' should work.

Problem/Help:
Just a slight oversight on my part: My code only will work in a single instance of Excel. I have to figure out how to target the other instances or mainly just the "OTHER" instance. I have looked at several sites and this is way over my head with the code I have already generated versus possible solutions.
Can you help me target the instance(s) so that this code will work.

VBA Code:
Sub FindAndCopy()
  Dim wbCopy As Workbook
 
  For Each wbCopy In Workbooks
    If (wbCopy.Name) Like "From*" Then
      wbCopy.Sheets(1).Range("A:A").Copy
      Exit For
    End If
  Next wbCopy
  Call FindAndPaste
End Sub

Sub FindAndPaste()
Dim wbPaste As Workbook
    For Each wbPaste In Workbooks
    If (wbPaste.Name) Like "To*" Then
      wbPaste.Worksheets("Sheet1").Range("A1").PasteSpecial
      Exit For
    End If
  Next wbPaste
End Sub
 

Attachments

  • 3 workbooks.PNG
    3 workbooks.PNG
    145.9 KB · Views: 10

danacton

New Member
Joined
Dec 6, 2006
Messages
32
Yes, please forgive my ignorance - so here is the requested data from the routine: ListAllBooks:

Current Application PID : 13228
************************************
Workbooks count : 2
Workbook(1): C:\Users\dacton\Desktop\SubCcontrolCenter.xls
Workbook(2): C:\Users\dacton\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\I2HDO2SY\IB862_20200101060321_SUBREQ.CSV
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,335
Office Version
  1. 2016
Platform
  1. Windows
Yes, please forgive my ignorance - so here is the requested data from the routine: ListAllBooks:

Current Application PID : 13228
************************************
Workbooks count : 2
Workbook(1): C:\Users\dacton\Desktop\SubCcontrolCenter.xls
Workbook(2): C:\Users\dacton\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\I2HDO2SY\IB862_20200101060321_SUBREQ.CSV

I don't see the IB862 or ~TM files in the output...
Were the two files opened in their respective excel instances when you run the code ?
If they were open then it means the files did not register in the Running Object Table and therefore the code I provided won't work ... If, on the other hand, the workbooks were not open, have them opened in their seperate excel instances and try re-running the code and see the ouput you get.
 

danacton

New Member
Joined
Dec 6, 2006
Messages
32
Jaafar,

IB862 (partial filename) is listed as (2): IB862_20200101060321_SUBREQ.CSV

And yes, the temp file served from the ERP is the one that does not show up in the processes either.
 

Attachments

  • process-exel.PNG
    process-exel.PNG
    6.1 KB · Views: 3

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,335
Office Version
  1. 2016
Platform
  1. Windows
IB862 (partial filename) is listed as (2): IB862_20200101060321_SUBREQ.CSV

And yes, the temp file served from the ERP is the one that does not show up in the processes either.

The fact that the ERP file doesn't show up in the task manager list is very strange... Have you tried looking under the Task Manager Details tab ? can you confirm that ?

Anyways, If the ERP file doesn't register in the ROT then the code I provided won't work and, for the time being, I don't see any other possible method for getting a pointer to an open file based only on its partial name.
 

danacton

New Member
Joined
Dec 6, 2006
Messages
32
I do not see it in the details tab of task manager. I appreciate all of your efforts in trying to get this solved for me. I will have to come up with a way to extract the data from this file without saving it or manually copying/pasting it to a file that is saved on my machine - in which case I believe we solved that scenario.

Thank you for your help.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,335
Office Version
  1. 2016
Platform
  1. Windows
I do not see it in the details tab of task manager. I appreciate all of your efforts in trying to get this solved for me. I will have to come up with a way to extract the data from this file without saving it or manually copying/pasting it to a file that is saved on my machine - in which case I believe we solved that scenario.

Thank you for your help.

You are welcome .

All the best with you endavour.
 

Forum statistics

Threads
1,144,526
Messages
5,724,849
Members
422,585
Latest member
k3n

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top