How to target instances of Excel

[danacton]

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.

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

 

[danacton]

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

 

[Jaafar Tribak]

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]

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.

 

[Jaafar Tribak]

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]

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]

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.