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
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