Can Basic macro code of two workbooks be compared by the use of a Basic macro?
fpy,
In my answer to KamilLanda’s reply I included the purpose and basically needed coding I am looking for.
I could not find the correct coding on web or through AI.
Tnkx for your reply
Only in case you program own macro to compare the text, but really it isn’ easy.
Create 2 TXT files, copy your macros to ones and use some program that has the comparison of 2 files. Under Win I use VSCodium or Notepad++.
Thanks KamiLanda and fpy,
I was a bit to short in the question indeed.
I would like to do two things:
- List he difference in modules and macros names in two versions of my workbook. Can be in a new sheet of the workbook where the macro is called from.
- Compare the same macro in the same module within two versions of my workbook by comparing the total code of these macros. By simply comparing the complete text (code) in whole of this macro and compare in total length.
Taking the suggestions of KamiLanda and having a little experience with Basic also in calling uno services as needed. The Basic code I basically am looking for is how to get the modules and macros names and the text of single macro’s.
And saving the different versions of a macro in different text files
Since indeed the suggestion to compare the code in existing tools, i will probably use kDIFF3, will do for me after identifying the macros with differences.
The best I could think of, is to save the modules from both versions and compare their texts using some compare tool like Meld.
Since having more then 20 modules and in those many different macros. This is the reason why I first want to roughly compare on differences so only the differences are loaded in textfiles to compare.
matter of interations
but definitely not the best scope here.
just unzip the Basic/
directory of your .ods
For operations with Basic libraries see LibreOffice documentation - Pitonyak’s macro book chapter 17
' Writes Application library "Tools" to "C:\temp\Tools"
Sub Test
LibToFolder GlobalScope.BasicLibraries, "Tools", "C:\temp\Tools"
End Sub
' lang:en
' Writes the contents of the oLib library to the folder.
' - oLibs library container (BasicLibraries, GlobalScope.BasicLibreries, oDoc.BasicLibreries)
' - libName library name
' - folder destination folder
Sub LibToFolder(ByVal oLibs As Object, Byval libName as String, Byval folder As String)
Dim oLib as Object, module as String
folder=ConvertToUrl(folder)
oLib=oLibs.getByName(libName)
oLibs.loadLibrary libName
For Each module In oLib.getElementNames()
StrToFile oLib.getByName(module), folder & "/" & module & ".txt"
Next module
End Sub
' lang:en
' Writes the string str to the text file filePath.
Sub StrToFile(Byval str As String, Byval filePath As String)
Dim oTextStream As Object, oSFA As Object
filePath=ConvertToUrl(FilePath)
oSFA=CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
If oSFA.exists(filePath) Then oSFA.kill filePath
oTextStream = CreateUnoService("com.sun.star.io.TextOutputStream")
With oTextStream
.setOutputStream oSFA.openFileWrite(filePath)
.writeString str
.closeOutput
End With
End Sub
If we talk about the task as a whole, it seems logical to save the compared libraries in (temporary) folders and then run utilities for comparing text files.
The Python universe offers two great tools for comparing text files and folders (Standard Library): filecmp and difflib.
Since my knowledge of Python language is not much better than the Sumerian language, we will wait for other consultants.
Thanks for this.
It does store the module’s code in a nice way.
I am working on roughly comparing code on module and macro level to save only the macros with differences as well.
Since code comparing on module level in the kDiff3 tool under linux (Ubuntu) gets a bit difficult with many differences.
If I get that macro comparing right hopefully on relative short notice I will add the code here.
in splitting the module code into macro code I struggle in abstracting the macro name and type including parameters.
Since for example the \r\n are not removed.
Using;
`sModuleCode=oLib.getByName(moduleCode)
oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")
oOptions = CreateUnoStruct("com.sun.star.util.SearchOptions")
oOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
oOptions.searchString = "[\r\n]Sub .*[''$\r\n]|[\r\n]Function .*[''$\r\n]"
oTextSearch.setOptions(oOptions)
oFound = oTextSearch.searchForward(sModuleCode, 0, Len(sModuleCode))
J = -1
ReDim macroname() As String
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
'result = SF_String.FindRegex("CabCcdefghHij","^ *C.*H", lStart, CaseSensitive := True)
Dim sPattern As String
sPattern = "^(.*?)['/\\r\\n\\$].*$"
While oFound.subRegExpressions > 0
For I = 1 to oFound.subRegExpressions
oFound.endOffset(I-1) - oFound.startOffset(I-1))
oFound.endOffset(I-1) - oFound.startOffset(I-1))
macroName=mid(sModuleCode, oFound.startOffset(I-1)+2, oFound.endOffset(I-1)-Found.startOffset(I-1) - 1)
macrocode=sMacroCode( oFound.startOffset(I-1)+1,sModuleCode )
If Not (left(trim(macro),1) Like "[a-zA-z]") Then
msgbox left(trim(macroName),1)+":"+macroName
Else
StrToFile macrocode, folder & "/" & module & "_" & trim(macroName) & ".bas"
'debug msgbox module & "_" & trim(macroName) & ".bas"
End If 'left(macrocode,1) ="|"
Next I
nLastFound=oFound.endOffset(0)-5
oFound = oTextSearch.searchForward(sModuleCode,nLastFound , (Len(sModuleCode)))
Wend `
,still results in macroName still including \r \n.`
I hope the code is displayed the right way.
Not sure about the proper character to put in front and behind to make it display as code.
okay, seems using regex \n in basic in Calc doesn’t work is in line with that is not supported in Calc itself in regular expressions.
I found a workaround and will include it in the code to save seperated macros
SaveMacrosToFiles_asklib-lybraries.ods (17.6 KB)
The macros in this workbook are the result of taking the answers given and using www search in combination with ai search.
Module “Module2filesThroughAskLibreoffice” is basically provided by sokol92
Module “Macros2files” was a struggle to find the way to split the code of a single macro from a module.
Using Meld for comparing the modules and/or macro’s files works well under both Ubuntu and Windows.
And for those using the Basic IDE in Libreoffice, like me.
The extracted code after altering can be imported through the icon for importing BASIC.
For both a whole module or a single macro.
Best way to do that seems to be by first cleaning the code of the part to be replaced and then importing the new code.
I hope someone can use this.
And thanks for the replies!