[SOLVED] To Run Rapidly LibreOffice Macro

libremacrouser · December 12, 2018, 12:54pm

Hi!

I work in a LibreOffice Macro. My code work so slowly because of my data. I have around 10,000 data in a sheet and when I write for loop and if statement, my code work is so slowly(It works about 5hours). Is there any methods to run it rapidly?

I added my code that works slowly.

Sub Main

rA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
cA = CreateUnoStruct("com.sun.star.table.CellAddress")
aaA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
abA = CreateUnoStruct("com.sun.star.table.CellAddress")
acA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
adA = CreateUnoStruct("com.sun.star.table.CellAddress")
aeA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
afA = CreateUnoStruct("com.sun.star.table.CellAddress")

	
'Sheet 2 'Ürün Ağaçları(Sheet Name)
Doc = ThisComponent
Sheeti2 = Doc.Sheets.getByIndex(2)
Curs = Sheeti2.createCursor
Curs.gotoEndOfUsedArea(True)
NumRows2 = Curs.Rows.Count


'Sheet 5	'3 Aylık Pivot(Sheet Name)
Doc = ThisComponent
Sheeti5 = Doc.Sheets.getByIndex(5)
Curs = Sheeti5.createCursor
Curs.gotoEndOfUsedArea(True)
NumRows5 = Curs.Rows.Count



dim document as object
	document = ThisComponent
	sheet1 = document.Sheets(5)'3 Aylık Pivot(Sheet Name)
	sheet2 = document.Sheets(2)'Ürün Ağaçları(Sheet Name)
	sheet3 = document.Sheets(6)'3 Aylık Tahmin(Sheet Name)
	sheet4 = document.Sheets(7)'Ağaçla Tahminler(Sheet Name)
    sheet5 = document.Sheets(3)'Takım Ağaçları (Sheet Name)


For iCountTree = 0 to NumRows5 '3 Aylık pivottaki satır sayısı 
For iCountTree1 = 1 to NumRows2'ürün ağacı satır sayısı	

if sheet1.getCellByPosition(0, iCountTree+5).String = sheet2.getCellByPosition(1, iCountTree1).String Then
		'Parça Kodunu copy start
			With aaA
		  	.Sheet       = 2
		  	.StartColumn = 2
		  	.StartRow    = iCountTree1
		  	.EndColumn   = 2
		  	.EndRow      = iCountTree1  	 
			End With
		'Parça Kodunu copy finish
		'Ürün Kodu copy start
			With acA
		  	.Sheet       = 2
		  	.StartColumn = 1
		  	.StartRow    = iCountTree1
		  	.EndColumn   = 1
		  	.EndRow      = iCountTree1  	 
			End With
		'Ürün Kodu copy finish
		'Parça Miktarını copy start
			With aeA
		  	.Sheet       = 2
		  	.StartColumn = 4
		  	.StartRow    = iCountTree1
		  	.EndColumn   = 4
		  	.EndRow      = iCountTree1  	 
			End With
		'Parça Miktarını copy finish

		for iCountForEmpty = 1 to NumRows2*2'Bu Sayıyı Ağaçların listesinden fazla olacak şekilde seçtim
		if sheet4.getCellByPosition(1, iCountForEmpty).String = empty then 
		'Parça Kodunu paste start
		  	With abA
		 	.Sheet       = 7
		  	.Column      = 2
		 	.Row         = iCountForEmpty
			End With
			ThisComponent.Sheets(7).CopyRange(abA, aaA)
		'Parça Kodunu paste finish
		'Ürün Kodu paste start
		  	With adA
		 	.Sheet       = 7
		  	.Column      = 1
		 	.Row         = iCountForEmpty
			End With
			ThisComponent.Sheets(7).CopyRange(adA, acA)
		'Ürün Kodu paste finish
		'Parça Miktarını paste start
		  	With afA
		 	.Sheet       = 7
		  	.Column      = 3
		 	.Row         = iCountForEmpty
			End With
			ThisComponent.Sheets(7).CopyRange(afA, aeA)	
		'Parça Miktarını paste finish
		
		'For to be empty to value of cells start
		'Parça Kodunu copy start
			With aaA
		  	.Sheet       = 2
		  	.StartColumn = 15
		  	.StartRow    = 1
		  	.EndColumn   = 15
		  	.EndRow      = 1  	 
			End With
		'Parça Kodunu copy finish
		'Ürün Kodu copy start
			With acA
		  	.Sheet       = 2
		  	.StartColumn = 15
		  	.StartRow    = 1
		  	.EndColumn   = 15
		  	.EndRow      = 1  	 
			End With
		'Ürün Kodu copy finish
		'Parça Miktarını copy start
			With aeA
		  	.Sheet       = 2
		  	.StartColumn = 15
		  	.StartRow    = 1
		  	.EndColumn   = 15
		  	.EndRow      = 1  	 
			End With
		'Parça Miktarını copy finish
		'For to be empty to value of cells finish

		Exit For	 
		else
		end if
		next iCountForEmpty
	
	Else
		
	end if

	next iCountTree1
	next iCountTree

	
End Sub

Edit for @JohnSUN:

JohnSUN · December 12, 2018, 2:03pm

You do not need to use ThisComponent.Sheets(7) when copying. You have already defined it as “sheet4”, use this variable. Do you copy only data? Or can there be formulas in the cells?

libremacrouser · December 12, 2018, 2:20pm

Yes @JohnSUN, I copy only data. What can I do to copy paste rapidly?

Lupp · December 12, 2018, 5:28pm

@libremacrouser: Can you explain in general words (not arbitrary and probably badly chosen names) what this code is supposed to achieve?
Quoting @libremacrouser: “Yes @JohnSUN, I copy only data. What can I do to copy paste rapidly?”
You are obviously testing conditions. For simply copying ranges there is the very fast copyRange method that every sheet can call. See LibreOffice: XCellRangeMovement Interface Reference

JohnSUN · December 12, 2018, 6:37pm

@Lupp As far as I understood the above code, this is the usual filtering of data from the table on the third sheet for the coincidence of the values in the column B with the values of column A on the sixth sheet. The result of filtering - the second, third and fifth cells are recorded in the first row on the eighth page, where there is no data in column B.

libremacrouser · December 13, 2018, 6:09am

@JohnSUN I editted my question. I added a picture on it. I try to your code in a simple example and it worked great. After that I tried it my real LibreOffice and I got this error which in the picture. I again try to understand what is your code. It is really great and I could see it how is work in my simple example. If I could complete my code, it would be really great. Can you tell me how can I solve my problem?

libremacrouser · December 13, 2018, 6:21am

@JohnSUN and I couldn’t understand what is their mean →
i7 = (UBound(oData2)+1) * (UBound(oData5)+1)
oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7)

JohnSUN · December 13, 2018, 6:50am

It’s almost the same as what you wrote here: for iCountForEmpty = 1 to NumRows2*2'Bu Sayıyı Ağaçların listesinden fazla olacak şekilde seçtim You took just double the number of rows. I thought about the case when all the codes in ‘Ürün Ağaçları’.B:B and in ‘3 Aylık Pivot’.A:A would be the same and asked for a size that was enough for all matches, each with each.

When you substituted real data, it turned out that “each with each one” is more than a million values, but sheet of Calc has only 1048576 lines.

So, just replace

i7 = (UBound(oData2)+1) * (UBound(oData5)+1)

with

i7 = 1048576

libremacrouser · December 13, 2018, 7:12am

Thanks for your reply @JohnSUN. I got it what is mean of i7 = (UBound(oData2)+1) * (UBound(oData5)+1). Sorry I think I ask you much questions. I try to understand its basic to improve my question and myself. Can you tell me about the error that I editted its picture in my question? In addition I couldn’t understand → oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7) I am not sure about (1.1.3.i7). And I don’t know why it is work in an my simple example and doesn’t work in my real file that I write it in a comment it is up two comments.

JohnSUN · December 13, 2018, 7:31am

Error - the number i7 turned out to be very large, we demanded from the macro to select on the sheet ‘Ağaçla Tahminler’ a range of cells larger than there is.

(1,1,3,i7) - mean “columns 1,2 and 3 (B, C and D) from second row (you skip header in your macros) to last needed row (to last row on sheet?)”

JohnSUN · December 12, 2018, 3:06pm

I hope that this variant do same and work something rapidly:

Sub JohnSUNsCopy
Dim oSheets As Variant	' All sheets of ThisComponent '
Dim oSheet As Variant	' Any single sheet '
Dim oData2 As Variant	' All data from Ürün Ağaçları Sheet '
Dim oData5 As Variant	' All data from 3 Aylık Pivot Sheet '
Dim oData7 As Variant	' Data to paste to Ağaçla Tahminler Sheet '
Dim oCursor As Variant	' You know what it is, you used it in your code '
Dim i2 As Long, i5 As Long	' Variables for cycles  '
Dim i7 As Long 			' Count trees '
Dim nEndRow7 As Long	' The number of the last non-empty row on sheet 7 column 1 '
Dim oRange7 As Variant
	oSheets = ThisComponent.getSheets()
	oSheet = oSheets.getByIndex(2)
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oData2 = oCursor.getDataArray()	' All cells of Ürün Ağaçları Sheet in the array '
	
	oSheet = oSheets.getByIndex(5)
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oData5 = oCursor.getDataArray()	' All cells of 3 Aylık Pivot Sheet in the array '

	oSheet = oSheets.getByIndex(7)
	i7 = (UBound(oData2)+1) * (UBound(oData5)+1)
	oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7)
	oData7 = oRange7.getDataArray()
	nEndRow7 = LBound(oData7)
	
	For i2 = LBound(oData2) To UBound(oData2)
		For i5 = LBound(oData5) To UBound(oData5)
			If oData5(i5)(0)  = oData2(i2)(1) Then
				For i7 = nEndRow7 To UBound(oData7)
					If (IsEmpty(oData7(i7)(0)) Or Trim(oData7(i7)(0))="") Then 
						oData7(i7)(0) = oData2(i2)(1)
						oData7(i7)(1) = oData2(i2)(2)
						oData7(i7)(2) = oData2(i2)(4)
						nEndRow7 = i7+1
						Exit For
					EndIf 
				Next i7
			EndIf 
		Next i5
	Next i2
	oRange7.setDataArray(oData7)
End Sub

libremacrouser · December 15, 2018, 8:08pm

Thanks @JohnSUN. Thanks to you I solved my problem. Anymore my macro run great and it works for only 15minutes. It is really great for me because before your solution, it was almost 5 hours.

JohnSUN · December 15, 2018, 8:22pm

Glad I could help. The algorithm could be accelerated even more (perhaps even several times faster). But to do this, you need to optimize the search for matching values that should be copied, and determine in advance all the blank lines on which to write the result. This would make the code so complex that I could not explain all its details.

AlexKemp · July 10, 2020, 7:32pm