Hi guys, I would be grateful if you could give me some advice as to the chance of success regarding the following: -
A BO document with multiple tabs is currently exported as txt then saved in a .xls (one .xls for each tab).
A user then copies this data into another excel document in order to produce a chart that cannot be created in BO.
This results is 2 .xls exists for each tab, the .xls created in BO and the one where the user creates the chart.
I have been asked to edit the macro with the desired result being the data is saved to the existing .xls that produces the chart so that only 1 .xls exists for each tab and the manual step of cut an paste is removed (BTW the users do not have Business Query).
My question is how easy is it to do this in one BO macro - I am guessing 3 steps, 1. save as .txt, 2. create ‘step2.xls’ and import data from text file, delete ‘step1.txt’ 3. move sheet from ‘step2.xls’ to existing .xls, delete ‘step2.xls’ - I can’t get step 3 to work in BO?
FYI, in case you haven’t guessed already I am not a VB developer - I only dabble when forced, so any advice is much appreciated!
I am not a VB developer either … just thinking aloud.
I think it might be easier to do from XLS rather then from BO. From XLS (or any other app that supports VBA) you can create an instance of BO, get data /report out of it and (probably) export reports to XLS.
And a macro running inside XLS will have much better API interface for managing or merging worksheets.
Thanks for the reply Shamit, and I agree it makes sense to use excel to move the worksheets between the .xls and the only other thread I have seen in a similar vein suggests this also.
Unfortunately the requirement is for automation and is driven by the refresh of the BO document - as far as the user is concerned they want to refresh the BO document and go to their existing .xls and find the data in there, they do not want to see the .xls created by the save as procedure in BO (it has to be deleted as soon as the data is in the existing) and they do not want to have to do anything in excel to get the data they need in there to update the chart.
Advice from anyone who has been in the same situation is much appreciated…next step will be ‘set the user’s expectation and suggest they keep the status quo’!
One suggestion is to look at the “Get External Data” functionality that is built in to Excel. Within the chart workbook, you can create a “link” to the text file that is automatically refreshed when the workbook is opened. No VBA (other than from the BusObj side to create the txt file) is needed at all.
This new solution would mean instead of 2 excel files we will need to maintain a text file and excel file, but at least it will remove confusion as to what excel file the user needs to access to get the chart.
…producing .xls document whether all tabs are in seperate .xls or in one is the easy bit…moving sheets around between the BO created .xls and the existing .xls and then deleting the BO created .xls is the tricky bit!
This is some Quick and Dirty code…
what it expects :-
A WorkBook (hardcoded to c:\srcXls.xls). This work book should contain the chart that needs data from BO generated workbook (hardcoded to c:\TestXls.xls).
what is does is:-
Saves BO doc as XLS (c:\TestXls.xls)
Copies sheet containing chart from c:\srcXls.xls to c:\TestXls.xls.
Shouts a lot
What it does NOT do :-
Any cleanup. You might have zombie instances of Excel.exe after running this code a few times.
Private Sub Document_AfterRefresh()
MsgBox ("Refreshed the doc, now saving doc as XLS")
ThisDocument.SaveAs ("c:\TestXls.xls")
Dim xlsApp As Excel.Application
Set xlsApp = New Excel.Application
Rem This is the XLS created by BO
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
xlsApp.Workbooks.Open ("c:\TestXls.xls")
Set objXLBook = xlsApp.Workbooks(1)
Set objXLSheet = objXLBook.Sheets(objXLBook.Sheets.Count)
Rem This is the XLS that contains the chart
Dim objXLBookSrc As Excel.Workbook
Dim objXLSheetSrc As Excel.Worksheet
MsgBox ("Opening ths XLS that contains the chart")
xlsApp.Workbooks.Open Filename:="c:\SrcXls.xls", UpdateLinks:=0
Set objXLBookSrc = xlsApp.Workbooks(2)
Set objXLSheetSrc = objXLBookSrc.Worksheets(1)
MsgBox ("Copying the chart to XLS created by BO")
objXLSheetSrc.Copy After:=objXLSheet
xlsApp.Workbooks.Item(1).Save
xlsApp.Workbooks.Item(2).Save
xlsApp.Quit
MsgBox ("Done")
End Sub
I can upload BO doc (based on island resort univ) and sample XLS on my website. Let me know if you need that.
What it is doing right now that it is creating a new sheet/tab in the existing excel sheet and paste the data there. For e.g It is creating a new sheet called “RawData” after the sheet 1 and before the sheet 2.
Is it possible if I want to overwrite the data on the existing sheet. My existing excel sheet also has the tab name RawData and I want to overwrite the data on that sheet everytime BO report refreshes and the code runs.
I am not surprised by that. Your code launches a session of Excel, uses the Excel VBA object model, reads / saves to C: drive, etc. When running from InfoView, all of those activities take place on the server. I highly doubt Excel is even installed there (few system administrators allow that), it’s going to look at the wrong C: drive (where I doubt the right privileges, exist), etc.
… just thinking aloud.
(BOB member since 2002-09-19)