same as title
env: libreoffice + Ubuntu 2204
thanks.
What did you already try?
Do you know that LibreOffice doesn’t speak VBA?
There is limited support for VBA code, but new routines should be written in LibreOffice Basic (using the LibO API replacing many VBA constructs).
Are the “two spreadsheet files” two open Calc documents or need they (or one of them) to be opened first?
…
1 Like
Also clarify what you mean by “copying” - you can transfer data (even if in the original document it was the results of formula calculations), you can transfer formulas, formatting, charts or other images … Which of these tasks did you encounter in this case?
1 Like
thank your answer.
vba I said is Libreoffice macro basic,two files are opened already.
thank your answer.
copy value only.
A sheet has 50000+ Rows , every step results need save another file .
In what way did you prepare for having access to both the documents as objects?
Do you want to identify them by their “names” (URLs)?
Do you want to assure that no additional Calc documents are loaded?
In what way do you intend to define and distinguish source (document / cellRange) and target?
Please don’t let it all come out one at a time. Surely you realize that a macro cannot just guess the necessary information.
1 Like
Nor can those who are trying to help you.
I solved this question preliminary.
code as follow:
sub filess
Dim Doc1,doc2 As Object
Dim Dummy()
Dim Url As String
Dim SearchFlags As Long
Dim Sheet1,sheet2 As Object
Dim Cell11,cell2, cell3 As Object
dim i,j
Doc1 = ThisComponent
SearchFlags = com.sun.star.frame.FrameSearchFlag.CREATE + _
com.sun.star.frame.FrameSearchFlag.ALL
Url = "file:///vbatest.ods"
Doc2 = StarDesktop.loadComponentFromURL(Url, "MyFrame", SearchFlags, Dummy)
MsgBox "vbatest.ods document opened."
i=1
j=1
Sheet1 = Doc1.Sheets(0)
Sheet2 = Doc2.Sheets(0)
Cell1 = Sheet1.getCellByPosition(i, j)
Cell2 = Sheet1.getCellByPosition(i+2, j+3)
Cell3 = Sheet2.getCellByPosition(i+4, j+5)
Cell1.value=345
Cell2.value=Cell1.value * 2
Cell3.value=Cell1.value + Cell2.value
end sub
This is my first use LO basic edit macro code.I used MS VBA macro before.
Admittedly, most of these lines make sense, not a bad start.
Do you mean you want to create a copy of one sheet of the current spreadsheet in a separate .ods file?
Another spreadsheet file’s cells are all complex calcultaed cells , not copy directly.
OK, you want to create a copy of values of all non-empty cells of one sheet of the another spreadsheet in a separate .ods file?
One spreadsheet has a lot of data . After caculating ang merging,the results saved to assigned cells of another spreadsheet file.
Because the first file’s size 10M+,so save the results to another file.
Well, try this
Option Explicit
Sub createCopy
Dim inDoc As Variant, outDoc As Variant
Dim oSheets As Variant, oSheet As Variant, sSheetName As String
Dim i As Long
Dim FilterNames(1,2) As String, sResult As String, sPath As String
FilterNames(0,0) = "Calc spreadsheet" : FilterNames(0,1) = "*.ods" : FilterNames(0,2) = "calc8"
FilterNames(1,0) = "Any type" : FilterNames(1,1) = "*.*" : FilterNames(1,2) = ""
GlobalScope.BasicLibraries.isLibraryLoaded("Tools")
inDoc = ThisComponent
inDoc.store()
outDoc = CreateNewDocument("scalc")
oSheets = outDoc.getSheets()
For i = oSheets.getCount()-1 To 1 Step -1
oSheet = oSheets.getByIndex(i)
sSheetName = oSheet.getName()
oSheets.removeByName(sSheetName)
Next i
sSheetName = inDoc.getCurrentController().getActiveSheet().getName()
oSheet = oSheets.getByIndex(0)
oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.VALUE)
oSheet.setLinkSheetName(sSheetName)
oSheet.setLinkUrl(inDoc.getURL())
oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
StoreDocument(outDoc, FilterNames, "", "")
DisposeDocument(outDoc)
End Sub
1 Like
Thanks a lot ，JohnSUN .
l’ll try it tomorrow.