Copy a worksheet bewtween two workbooks in different Excel instances

mbailey

Member
Hi,

I have a process that will execute many SAP transactions, some returning large amounts of data, and create an Excel workbook (Output.xlsx) with separate worksheets containing the data from each SAP transaction.

As each SAP transaction is run, we have SAP export the data to Excel. For faster speed and fewer potential Blue Prism memory issues, I want to avoid reading the worksheet data from each SAP transaction into collections and writing the collections to worksheets in Output.xlsx.

I could call MS Excel VBO.Attach to gain access to the workbook from SAP, open Output.xlsx with the same handle, copy the SAP worksheet directly into Output.xlsx, save and close Output.xlsx, close the SAP workbook without saving, and repeat that process for each SAP transaction. However, that requires opening, saving, and closing Output.xlsx for each SAP transaction.

What I want to do is open Output.xlsx once and, for each SAP transaction, export the data to Excel, attach to the SAP worksheet with the same handle that was used to open Output.xlsx, copy the SAP worksheet into Output.xlsx, save Output.xlsx without closing it, and close the SAP workbook without saving. Only when all the SAP transaction are run and all the worksheets are copied to Output.xlsx would Output.xlsx be closed. That would save a great deal of time by not opening and closing Output.xlsx for each SAP transaction.

My problem is that I don't know how to use the same handle that opened Output.xlsx to attach to the exported SAP workbooks or, as an alternative, to copy a worksheet between workbooks with different handles. Is there a way to do this?

Thanks,
Michael Bailey
 

Pete_L

Active Member
Your Output.xlsx file is opened in an Excel instance on, say Handle 1. If I understand your use case correctly, all you need to do is specify the worksheet name for each SAP transaction that you want to output, with each transaction writing to Handle 1 and with the Worksheet name parameter containing the Worksheet (Tab) name for the transaction. This can easily be done with the Excel VBO, but you'd need to load the data to a collection first and you state that you don't want to do that.

There is an SAP VBO, but I have never needed to use it since my company doesn't use SAP. Please check it to see if it contains an action that will allow you to export to Excel, and if so, that the action provides an input for the Worksheet name, which you could name Sheet1, Sheet2, etc. or some other unique identifying name. If the VBO does not provide such an action, and you don't want to use a collection and the Excel VBO, then you will need to develop a code stage to do this.

I recommend using the Excel VBO with a collection. Doing so is straightforward and will not appreciably slow down your automation unless each transaction you're exporting has many thousands of rows. To prevent memory issues, you should clear the collection after writing it to Excel, and then collect garbage.
 
Top