2

I believe my problem is rather simple: I have a workbook and I'm using it to get some data from another software (SAP). When I export the data from the software, it automatically opens a .xlsx file, then what I'd need to do is copy some of the data from this file, paste on my original workbook, and then close the file. The section of my code which is giving me an error is this one:

fileName = "temp1.xlsx"
Set wbBasis = Workbooks(fileName)

This happens because the "temp1.xlsx" file that was opened by the SAP software is in another instance of excel, so my code isn't able to find it.

What I need to know is basically this. How to properly reference this "temp1.xlsx" workbook on my original code so that I'm able to edit it, copy stuff from it, close it, etc.

I've found some similar topics to my problem, like the two I'm listing down here, but couldn't adapt the solutions to my situation, and that's why I'm posting this new one.

Having multiple excel instances launched, how can I get the Application Object for all of them?

Finding a workbook in one of multiple Excel instances

Thank you in advance.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Weyker
  • 67
  • 5
  • 2
    Why are you running Excel in separate instances ? – ashleedawg Jul 16 '18 at 16:24
  • 1
    You're over-complicating this big-time... (This is what's called an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem/66378#66378).) – ashleedawg Jul 16 '18 at 16:32

1 Answers1

3

You don't need multiple instances of Excel, and you don't need the Excel file to be open in order to get information from it, either with VBA, or even with regular "linked formulas".

For example:

=SUM([C:\yourPath\Filename.xlsx]SheetName!A1:C25)

...returns the sum of cells A1:C25 on a worksheet named Sheetname in an Excel file located at C:\yourPath\Filename.xlsx.

Or you can import all the data directly with Get External Data.

See:

...for more information and examples, search Google for "get data from another Excel workbook".

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • 1
    Hey, hitting 10K today? Congrats! ;-) – Mathieu Guindon Jul 16 '18 at 16:49
  • @MathieuGuindon I've been "so close yet so far" for days, I've been trying though! The last bit is the toughest! Not gonna lie, it's driving me a little batty... but thanks! – ashleedawg Jul 16 '18 at 16:52
  • That's great to know, I'm still learning VBA so everything is kind of new to me. – Weyker Jul 16 '18 at 16:56
  • The only problem with this is that I'll need to run this code several times (about 20000 times) and every single time do this process of copying and pasting. The software that I'm getting data from (SAP) opens the .xlsx workbook automatically everytime I run it, and opens it in another instance. So I need some way to close this workbooks as well, in order not to have 20000 different open workbooks at the end of the code loop. – Weyker Jul 16 '18 at 17:00
  • 1
    Right - hence the "**Get External Data**" option as outlined in the [attached link](https://support.office.com/en-us/article/connect-to-another-workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5). You can get all the data at once, either as a copy, or as a permanent link to whatever the file currently holds. – ashleedawg Jul 16 '18 at 17:03