VBA Save BEx workbook to pure Excel file after removing BEx references

[Former Member]

Here is my need

After the variables screen has been filled and the control has returned to BEx/VBA module, I want to process data into a new sheet and save only the new sheet as xls (or xlsm) file.  Note: The file will also contain macros so I can't save it as .xlsx file.

The way I did that is by deleting all sheets that contain BEx queries and then save the remaining to a local xls file. Then I close the BEx window

The problem is when I re-open the xls file from Explorer using double-click:  BEx icons are still active and VB editor shows that BExAnalyzer.xla is allocated. How do I remove them before saving the file with VBA ? 

[former_member283567]

Hello,

Though this is a very old thread, it might be beneficial for companies/users using SAP BEx.

I recently ran 2 times into this issue and both times it took me almost 2 frustrating hours to figure out what was happening!

In my case the cause of the problem was that I was using an .XLSX type workbook instead of an .XLSM macro-enabled workbook.
Changing the workbook type to .XLSM solved my issue.

I think, when using the SAP default workbook, you will automatically have an .xlsm workbook.
However, be aware when using your own workbooks!

I changed the workbook type in the following way:

1. Save Excel workbook locally in the local BEx work directory (menu: File -> Save as)
   Here you can see Excel wants to save file type .xlsx
   Here you can change the file type to .xlsm (macro-enabled)
2. Then, save the workbook using the BEx menu
3. BEx will ask if you want to overwrite the file: click Yes.

Have fun with old school BEx 🙂

[Former Member]

' Here is the code...

' Purists will want to dimension all variables...

Sub RemoveBExSheetsAndCloseBExMenu()

  Dim saveAs_FileName As String

  Application.EnableEvents = False     ' Remove BEx overhead and speed up process

  Application.DisplayAlerts = False    ' Don't show delete and save confirmation messages

  For i = Sheets.Count To 1 Step -1    ' Must delete from the end otherwise index is broken

    If Sheets(i).Name = "BExRepositorySheet" Or _

       Sheets(i).Name = "SomeBWSheetName1" Or _

       Sheets(i).Name = "SomeBWSheetName2" Then

        Sheets(i).Visible = True

        Sheets(i).Delete

    End If

  Next

  '

  ' Show default filename and filepath and allow changing

  saveAs_FileName = selectFilename("FileWithBExRemoved.xlsm")

  '

  ' Actual saving of file

  If saveAs_FileName <> "" Then

     ActiveWorkbook.SaveAs saveAs_FileName   ' Saves only to the same extension as the active workbook

  End If

  '

  ' Remove command bars

  Call CommandBars("BEx Design Toolbox").Delete

  Call CommandBars("BEx Analysis Toolbox").Delete

  '

  ' Hide menu bar   (delete won't work)

  CommandBars("Worksheet Menu Bar").Controls("&BEx Analyzer").Visible = False

  ' Return to normal BEx environment... but no BEx

  Application.DisplayAlerts = True     ' Return to displaying messages

  Application.EnableEvents = True      ' Reactivate BEx buttons and functions (just for the sake of it)

End Sub

Private Function selectFilename(defaultFileName As String) As String

   Dim varResult As Variant

   If defaultFileName = "" Then defaultFileName = "FileWithBExRemoved.xlsm"

   selectFilename = ""

   varResult = Application.GetSaveAsFilename(Title:="Please choose file name and path", InitialFileName:=defaultFileName)

   If varResult <> False Then

      selectFilename = varResult

   End If

End Function

[Former Member]

Solution - part 1

When I added the BEx query sheets, I don't remember whyand how but I added references to BExAnalyzer.xla.  This reference shows in VBA Editor.  I simply had to remove it and save the BEx file to the BEx repository.  When the BEx workbook is executed from the BEx repository, it seem not to need this reference anymore.  If someone can bring something to the table, feel free.

Here is the reference that keeps the BEx icons active.

Part 2 will contain the actual code to remove the BEx sheets, save the file and close BEx instance.

[Former Member]

I opened another discussion on «Self closing of BEx instance» because I was not able to do an Application.Quit from an active BEx workbook.  It produces an error while quitting Excel/BEx.

For now, the best I was able to do is:

1. Delete sheets that contained BEx queries
2. Delete BEx command icons from the «Complements» tab
3. Hide «BEx Analyzer» menu from the «Complements» tab
4. Save workbook on a local Excel file and leave Excel opened.

See the code on the next reply...

[Former Member]

Dear Denis,

You can refer the below code to achieve this.

    Sheets("Sheet1").Select           

    Sheets("Sheet1").Copy

    ChDir "C:\Users\TEMP\Desktop"

    ActiveWorkbook.SaveAs Filename:="C:\Users\TEST\Desktop\TEST1.xlsx", _

        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Change the Sheet name which you wanted to save it seperately and then specify the path & filename with extension(in the format you want to save).

Thanks

Vijayendra

[Former Member]

Hello Edward and Vijayendra,

I tried to simplify the problem and it seems I got it wrong.

So let me try the complete description...

==================

I had a complex Excel file used for data entry, validation and formating of official report that could end up as evidence in law court.  The file had a lot of buttons, securities and protected macros which were working perfectly.

Then I added BEx queries to pre-fill the data entry part, then hid the BEx query sheets, and saved the results to an  .xlsm file.  Still working perfectly.

Instead of hiding the BEx query sheets, I wanted to remove them and save the file to .xlsm .  Works fine, save is OK.

Here is the problem: When I reopen the saved file from Excel, the BEx icons appears when I don't want them showing at all, since the file is no longer connected to BEx.

Everything else is working fine...

===================

I found the source of the problem and I will post it soon.  

[former_member186696]

Hello Denis,

Basically you want to know how to save a workbook locally without macro.

I found some solutions on internet, related to Excel. This is not trully related to BEx Analyzer

Saving all worksheets as values without formula or macros - Microsoft Community

How to save the Excel sheet without formula and macros? - Super User

You can also consider to save as CSV

Best Regards,
Edward