Page 1 of 1

VBA Macro (Excel) to BA Macro (OO Calc), How easy would be?

Posted: Fri Apr 16, 2021 8:43 pm
by LSGC
Hi everyone
I´m doing a data management process for which I created a VBA Macros, but recently I discovered that some of the users of the Macros will be using Open Office, I want to know what it takes to make the change from VBA to BA, to see if I should invest the time in doing it or if I should look for a different approach.

The Macro works as follows:
The user will download a data report that has multiple variable number of sheets for each user fill with varaible number of rows as well.
The user will copy all the sheet to the end of the document with the Macro.
The user cliks a button and the macro starts working.
The macro goes into each sheet and copy the data into a "ConsoidatedData" sheet where it consolidates all the data.
Then the Macro updates some pivot tables, then copy the info into formated tables.
Then the formated tables are copied into a new document and the Macro document is closed and the original data sheets erased from it (This is so the user will have the least interaction with the document as possible so it wont alter it, since most of the users will have from none to low technical habilities.

Here is the code:

Code: Select all

Sub dataUpdate()

Application.ScreenUpdating = False
    
'Erase previous data'
    Worksheets("ConsolidatedData").Range("A2:V10000").ClearContents

'Loop that goes through each data sheet copy its content and paste it into the "ConsolidatedData" Sheet '
Dim ws As Worksheet
Dim data As Worksheet
Set data = Worksheets("ConsolidatedData")
    For x = 6 To ThisWorkbook.Worksheets.Count
            Set ws = ThisWorkbook.Worksheets(x)
            'Clean Up of the data sheets so the copying process can be standarize of each one'
            ws.Range("A1:A7").UnMerge
            If InStr(1, ws.Range("A1").End(xlDown), "50 NET STORE PROFIT") = False Then
            ws.Range("A1").End(xlDown).UnMerge
            ws.Range("A1").End(xlDown).ClearContents
            End If
            'Copy/Paste of data from data sheets to consolidateddata sheet'
            ws.Range(ws.Range("U9"), ws.Range("U9").End(xlToLeft).End(xlDown)).Copy _
            Destination:=data.Range("B1").End(xlDown).Offset(1)
            Application.CutCopyMode = False
    Next x

'Updating pivots of the "pivots" sheet and copying data into formated tables in the "tables" sheets'
    ThisWorkbook.RefreshAll
    Dim pivots As Worksheet
    Dim tablas As Worksheet
    Set tables = Worksheets("Tablas")
    Set pivots = Worksheets("Pivots")
    'Updating title for each table(the title changes according to the moth of the data)'
        pivots.Range("P4:AC4").Copy
        tables.Range("C3").PasteSpecial Paste:=xlPasteValues
        pivots.Range("P20:AC20").Copy
        tables.Range("C20").PasteSpecial Paste:=xlPasteValues
        pivots.Range("P35:AD35").Copy
        tables.Range("C36").PasteSpecial Paste:=xlPasteValues
        pivots.Range("P50:AC50").Copy
        tables.Range("C52").PasteSpecial Paste:=xlPasteValues
    'Copying/pasting the data'
        pivots.Range(pivots.Range("A5"), pivots.Range("A5").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
        tables.Range("D6").PasteSpecial Paste:=xlPasteValues
        pivots.Range(pivots.Range("A21"), pivots.Range("A21").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
        tables.Range("D23").PasteSpecial Paste:=xlPasteValues
        pivots.Range(pivots.Range("A36"), pivots.Range("A36").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
        tables.Range("D39").PasteSpecial Paste:=xlPasteValues
        pivots.Range(pivots.Range("A51"), pivots.Range("A51").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
        tables.Range("D55").PasteSpecial Paste:=xlPasteValues
     'Updating Month'
        tables.Range("A24").Copy
        tables.Range("C38").PasteSpecial Paste:=xlPasteValues
        
'Erase Data Sheets'
Dim y As Integer
    ThisWorkbook.Worksheets(4).Select
        For y = 5 To ThisWorkbook.Worksheets.Count
            Worksheets(y).Select (False)
        Next y

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True


'Copy formated tables into new document'
    'Dim Variables'
Dim wb_inp As Workbook
Dim ws_inp As Worksheet
Dim wb_out As Workbook
Dim ws_out As Worksheet
    'Set Variables'
Set wb_inp = ThisWorkbook
Set ws_inp = wb_inp.Sheets("Tablas")
Set wb_out = Workbooks.Add

With wb_out
    Set ws_out = wb_out.Worksheets(1)
    ws_inp.Range("C3:Q100").Copy
    ws_out.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

End With

wb_out.Activate
wb_inp.Close
MsgBox "Data Updated"

Application.ScreenUpdating = True

End Sub
What do you guys thing? SHould this be easy to convert (I have no expreience with BA tbh)?

Are the functionalities easely transferable like updating pivots, and Ranges for copy pasting? The ranges and end´s are an important part since the size of the data is dynamic, is there something like this in BA?

Should I give a try to change into BA or is it better to look for a different approach?

Thanks in advance for your time and help!

Re: VBA Macro (Excel) to BA Macro (OO Calc), How easy would

Posted: Fri Apr 16, 2021 10:09 pm
by Villeroy
Templates and styles are the essence of an office suite. Learn how to use a template and most of this macro will become obsolete.
There is also a sheet function GETPIVOTDATA which allows you to reference pivot table cells instead of copying them.

Re: VBA Macro (Excel) to BA Macro (OO Calc), How easy would

Posted: Sat Apr 17, 2021 7:01 am
by Zizi64
BA Macro (OO Calc)
The name is the embedded programming environment of the Apache OpenOffice and the Libreoffice is StarBasic (or simply: "Basic").
The MS-VBA is a mixed stuff: a programming language and a Library for contorolling the actual Application (the Excel) in one.

The API (Application Programming Interface) of the AOO/LO is a standalone thing. You can call all fo iits functions from every supported programming langueges and IDE-s.
Therefore the VBA and the StarBasic+API are not compatible. You must rewrite all of macros if you want to work with the AOO/LO efficiently.


But - as Villeroy wrote it - not needed macro for all tasks in the AOO/LO, which tasks need macro in the Excel.
Why you want to create same structure again and again in a spreadsheet, what you can store in a Template for the future working?

Re: VBA Macro (Excel) to BA Macro (OO Calc), How easy would

Posted: Sat Apr 17, 2021 7:42 am
by eeigor
Your code is not complicated, but not very clear due to the use of range references instead of their names.
I suggest you familiarize yourself with StarBasic capabilities on your own. For some things (Paste Special) the DispatchHelper is used.
Read this and this also.
If your pivot tables looks acceptable in Calc... I don't suggest optimizing the file structure (I don't know it).

Perhaps you should keep this functionality in mind too.

Re: VBA Macro (Excel) to BA Macro (OO Calc), How easy would

Posted: Sat Apr 17, 2021 8:20 am
by eeigor
Off topic
Zizi64 wrote:But - as Villeroy wrote it - not needed macro for all tasks in the AOO/LO, which tasks need macro in the Excel.
I would like to know what such tasks Excel does not solve? For some reason I ran into the opposite. In Excel, instead of the REGEX function, an external library is used and, accordingly, a macro is needed, but everything is quite acceptable. And what else?

For example, I left MS Office because I changed the OS (Windows to Linux). And it's good that what I'm doing in LibreOffice works on both Windows and MacOS (by the way, it works worse on MacOS, but better on Linux and Windows)...

Re: VBA Macro (Excel) to BA Macro (OO Calc), How easy would

Posted: Sat Apr 17, 2021 2:43 pm
by Villeroy
Excel can do a lot more things without VBA, paricularly it includes templates, styles and a similar function GETPIVOTDATA. However, today's Excel users are not willing to master the software. Nobody reads any books on software. Excel users ask other people for some magic code. MS Office users hardly use any templates, let alone styles. MS Office users mindlessly copy code they do not really understand. Quite often this code generates preformatted documents from scratch. In this case, it resets an existing document to an initial state as far as I understand the macro.