Poser votre question

Profil de libremacrouser - activité

2019-07-12 13:46:24 +0100 a commenté une réponse Copy Paste Macro in Libre Office Calc

Yes, thanks you! I want it like this, but I couldn't find its code so I don't work how I will run it.

2019-07-12 10:52:25 +0100 question commentée Copy Paste Macro in Libre Office Calc

Can I do it with libre office macro?

2019-07-11 16:04:52 +0100 médaille reçue  Question remarquable (source)
2019-07-10 20:55:39 +0100 médaille reçue  Question populaire (source)
2019-07-10 11:30:37 +0100 a posé une question Copy Paste Macro in Libre Office Calc

Copy Paste Macro in Libre Office Calc Hello! I want to create copy paste macro with libre office calc. As I add my file

2019-05-13 13:39:14 +0100 médaille reçue  Question célèbre (source)
2019-05-08 17:15:53 +0100 médaille reçue  Question célèbre (source)
2019-05-08 15:59:18 +0100 a commenté une réponse [SOLVED] How Can I Delete Data with Macro

Thank you! I solved my problem completely

2019-05-08 15:29:40 +0100 a commenté une réponse [SOLVED] How Can I Delete Data with Macro

I changed my code like you said before you said me like this: sheet1 = document.sheets(1) sheet2 = document.sh

2019-05-08 14:59:31 +0100 question modifiée [SOLVED] How Can I Delete Data with Macro

How Can I Delete Data with Macro Hello! I try to write macro in Libre Office Macro. I want to delete data by using Libr

2019-05-08 12:07:33 +0100 a commenté une réponse [SOLVED] How Can I Delete Data with Macro

Thanks @karolus , I got it. So I need one more question that is I want to add sheet in front of these sheet, but I could

2019-05-08 10:52:15 +0100 a commenté une réponse [SOLVED] How Can I Delete Data with Macro

@karolus You are great! Thank you. It run. Ask for improvement, It can arrange otomatically its rows and colums? For exa

2019-05-08 10:27:51 +0100 marquée comme meilleure réponse [SOLVED] How Can I Delete Data with Macro

Hello!

I try to write macro in Libre Office Macro. I want to delete data by using Libre Office Macro. How can I write this code? Can you help me?

My code is here:

Sub CopyPasteworking

xA = CreateUnoStruct("com.sun.star.table.CellAddress") 
yA = CreateUnoStruct("com.sun.star.table.CellAddress")
xA1 = CreateUnoStruct("com.sun.star.table.CellAddress")
xA2 = CreateUnoStruct("com.sun.star.table.CellAddress")
yA1 = CreateUnoStruct("com.sun.star.table.CellAddress")
yA2 = CreateUnoStruct("com.sun.star.table.CellAddress")
x2a = CreateUnoStruct("com.sun.star.table.CellAddress") 
y2a = CreateUnoStruct("com.sun.star.table.CellAddress") 

With xA 
  .Sheet       = 0
  .Column      = 0
  .Row         = iCount6
End With
With xA1 
  .Sheet       = 0
  .Column      = 1
  .Row         = iCount6
End With
With xA2 
  .Sheet       = 0
  .Column      = 2
  .Row         = iCount6
End With  

With yA 
  .Sheet       = 2
  .Column      = 0
  .Row         = iCount6
End With
With yA1 
  .Sheet       = 2
  .Column      = 1
  .Row         = iCount6
End With
With yA2 
  .Sheet       = 2
  .Column      = 2
  .Row         = iCount6
End With

With x2a 
  .Sheet       = 0
  .Column      = 0
  .Row         = iCount6
End With
With y2a 
  .Sheet       = 0
  .Column      = 1
  .Row         = iCount6
End With





For iCount6 = 0 to 6 

dim document as object
document = ThisComponent
sheet1 = document.sheets(0)
sheet2 = document.sheets(1)
sheet3 = document.sheets(2)

for iCount7 = 0 to 6
if sheet1.getCellByPosition(0,iCount6).String = sheet2.getCellByPosition(0,iCount7).String then

    xA.Row = iCount6
    yA.Row = iCount6
    xA1.Row = iCount6
    yA1.Row = iCount6
    xA2.Row = iCount6
    yA2.Row = iCount6

    SourceCell = ThisComponent.Sheets(xA.Sheet).GetCellByPosition(xA.Column, xA.Row)
    PasteCell = ThisComponent.Sheets(yA.Sheet).GetCellByPosition(yA.Column, yA.Row)
    PasteCell.DataArray = SourceCell.DataArray

    SourceCell = ThisComponent.Sheets(xA1.Sheet).GetCellByPosition(xA1.Column, xA1.Row)
    PasteCell = ThisComponent.Sheets(yA1.Sheet).GetCellByPosition(yA1.Column, yA1.Row)
    PasteCell.DataArray = SourceCell.DataArray

    SourceCell = ThisComponent.Sheets(xA2.Sheet).GetCellByPosition(xA2.Column, xA2.Row)
    PasteCell = ThisComponent.Sheets(yA2.Sheet).GetCellByPosition(yA2.Column, yA2.Row)
    PasteCell.DataArray = SourceCell.DataArray

else



end if 
Next iCount7
Next iCount6

End Sub

I try to delete data if the --> sheet1.getCellByPosition(0,iCount6).String = sheet2.getCellByPosition(0,iCount7).String

I want to delete instead of copy paste because of I want to copy the data if they are the common.

And I added my libre office calc sheets.

I hope you could help meC:\fakepath\copy paste without common data.ods

2019-05-08 10:27:30 +0100 a commenté une réponse [SOLVED] How Can I Delete Data with Macro

@karolus You are great! Thank you. It run. Ask for improvement, It can arrange otomatically its rows and colums? For exa

2019-05-07 16:19:26 +0100 médaille reçue  Question remarquable (source)
2019-05-07 12:42:06 +0100 médaille reçue  Question populaire (source)
2019-05-07 12:42:04 +0100 a répondu à une question [SOLVED] How Can I Delete Data with Macro

@JohnSUN can you help me if you are available?

2019-05-07 12:30:11 +0100 médaille reçue  Question célèbre (source)
2019-05-07 12:16:03 +0100 a commenté une réponse [SOLVED] How Can I Delete Data with Macro

Hi @lupp Thanks for your reply. Can you write me a simple clearContents code. I researched about it in a detail way but

2019-05-07 10:49:24 +0100 question modifiée [SOLVED] How Can I Delete Data with Macro

How Can I Delete Data with Macro Hello! I try to write macro in Libre Office Macro. I want to delete data by using Libr

2019-05-07 10:48:46 +0100 question modifiée [SOLVED] How Can I Delete Data with Macro

How Can I Delete Data with Macro Hello! I try to write macro in Libre Office Macro. I want to delete data by using Libr

2019-05-07 10:48:46 +0100 médaille reçue  Éditeur associé (source)
2019-05-07 10:34:35 +0100 a posé une question [SOLVED] How Can I Delete Data with Macro

How Can I Delete Data with Macro Hello! I try to write macro in Libre Office Macro. I want to delete data by using Libr

2018-12-25 19:23:09 +0100 médaille reçue  Question remarquable (source)
2018-12-23 18:27:22 +0100 a commenté une réponse [SOLVED] To Get Value as an Integer In a Cell by using LibreOffice Macro

@JohnSUN I solved my this problem thanks to you. Thanks a lot

2018-12-22 09:01:28 +0100 a commenté une réponse [SOLVED] To Get Value as an Integer In a Cell by using LibreOffice Macro

@JohnSUN There isn't wrong. I run it correctly when you said me, after that I need to cross value like 1.7 so I need to

2018-12-22 07:01:32 +0100 marquée comme meilleure réponse [SOLVED] To Get Value as an Integer In a Cell by using LibreOffice Macro

Hello!

I try to write a macro code. I have a problem. I want to get a value of a cell and I want to multiplication it with other cell and paste it.

I tried to multiplication it with many ways and I couldn't it and I searched and couldn't find it. Becauase of this I write here.

My code is here:

Sub CopyPaste

xA = CreateUnoStruct("com.sun.star.table.CellAddress") 
yA = CreateUnoStruct("com.sun.star.table.CellAddress")
tA = CreateUnoStruct("com.sun.star.table.CellAddress") 
zA = CreateUnoStruct("com.sun.star.table.CellAddress")

With xA 
  .Sheet       = 0
  .Column      = 1
  .Row         = iCount6
End With
With yA 
  .Sheet       = 0
  .Column      = 1
  .Row         = iCount6
End With


With tA 'For quantity
  .Sheet       = 0
  .Column      = 2
  .Row         = iCount6
End With
With zA 
  .Sheet       = 0
  .Column      = 2
  .Row         = iCount6
End With



For iCount6 = 1 to 6 'loops through rows 1 to 6
    xA.Row = iCount6
    yA.Row = iCount6
    tA.Row = iCount6
    zA.Row = iCount6
    SourceCell = ThisComponent.Sheets(xA.Sheet).GetCellByPosition(xA.Column, xA.Row)
    SourceCell = ThisComponent.Sheets(tA.Sheet).GetCellByPosition(tA.Column, tA.Row)
    PasteCell = (ThisComponent.Sheets(yA.Sheet).GetCellByPosition(yA.Column+2, yA.Row))*(ThisComponent.Sheets(zA.Sheet).GetCellByPosition(zA.Column+1, zA.Row))
    PasteCell.DataArray = SourceCell.DataArray

Next iCount6

End Sub

In the other way I tried to get value of a cell. I added its code here.

Sub CopyPaste1

xA = CreateUnoStruct("com.sun.star.table.CellAddress") 
yA = CreateUnoStruct("com.sun.star.table.CellAddress")

dim document as object
    document = ThisComponent
    sheet4 = document.Sheets(0)


For iCount6 = 1 to 6
    With xA 
      .Sheet       = 0
      .Column      = 1
      .Row         = iCount6
    End With
    With yA 
      .Sheet       = 0
      .Column      = 1
      .Row         = iCount6
    End With
    MsgBox(sheet4.getCellByPosition(2, iCount6).Integer)

    xA.Row = iCount6
    yA.Row = iCount6
    SourceCell = ThisComponent.Sheets(xA.Sheet).GetCellByPosition(xA.Column, xA.Row)
    PasteCell = ThisComponent.Sheets(yA.Sheet).GetCellByPosition(yA.Column+2, yA.Row)*(sheet4.getCellByPosition(2, iCount6).Integer)
    PasteCell.DataArray = SourceCell.DataArray

Next iCount6

End Sub

But here there is a problem about integer. If I write string, It works but not I want. I want to paste cell with quantity. So I can't muliplication with String data.

I added example about them.

C:\fakepath\Copy-Paste Multiplication with a cell Example.ods

Edit for new example: C:\fakepath\1.ods

Edit for @JohnSUN :

Sub copyMult
Dim oSheet As Variant, oRange As Variant
Dim oData As Variant    ' Data of range, all cells of all rows in range '
Dim i As Long   
    oSheet = ThisComponent.getSheets().getByIndex(0)
Rem Get entire range - this is a bit simpler than describing each cell
    oRange = oSheet.getCellRangeByName("B2:D7") 
Rem Get values of all cells in ramge to array 
    oData = oRange.getDataArray()
Rem Repeat for each row in array
'    For i = LBound(oData) To UBound(oData)
  '  For i = 2 To 7
Rem Take the integer part of values ​​of the first and second cells and multiply it
Rem Function CInt converts any string or numeric expression to an integer
        'Const sText3 = "Tahminleri Kopyalama Tamamlandı"
     '  MsgBox(CInt(oData(2)(2 ...
(more)
2018-12-22 07:01:32 +0100 a commenté une réponse [SOLVED] To Get Value as an Integer In a Cell by using LibreOffice Macro

Hi @JohnSUN in my cell there is value like 1,7 or 1.7. So When I cross it with another value, it doesn't cross what I wa

2018-12-16 04:24:39 +0100 médaille reçue  Question populaire (source)
2018-12-15 22:33:47 +0100 médaille reçue  Question célèbre (source)
2018-12-15 21:36:42 +0100 question commentée How can I create Pivot Table with Macro in LibreOffice Calc

Hi @JohnSUN. Do you have any suggestion for it?

2018-12-15 21:35:45 +0100 question commentée How can I create Pivot Table with Macro in LibreOffice Calc

Hi @JohnSON. Do you have any suggestion for it?

2018-12-15 21:34:47 +0100 question modifiée How can I create Pivot Table with Macro in LibreOffice Calc

How can I create Pivote Table with Macro in LibreOffice Calc Hello! I have work with LibreOffice Macro. I want to cr

2018-12-15 21:28:21 +0100 a posé une question How can I create Pivot Table with Macro in LibreOffice Calc

How can I create Pivote Table with Macro in LibreOffice Calc Hello! I have work with LibreOffice Macro. I want to cr

2018-12-15 21:09:15 +0100 question modifiée [SOLVED] To Run Rapidly LibreOffice Macro

To Run Rapidly LibreOffice Macro Hi! I work in a LibreOffice Macro. My code work so slowly because of my data. I have a

2018-12-15 21:08:30 +0100 a commenté une réponse [SOLVED] To Run Rapidly LibreOffice Macro

Thanks @JohnSUN. Thanks to you I solved my problem. Anymore my macro run great and it works for only 15minutes. It is re

2018-12-15 21:06:43 +0100 marquée comme meilleure réponse [SOLVED] To Run Rapidly LibreOffice Macro

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 ...
(more)
2018-12-13 08:12:56 +0100 question commentée [SOLVED] To Run Rapidly LibreOffice Macro

Thanks for your reply @JohnSUN. I got it what is mean of i7 = (UBound(oData2)+1) * (UBound(oData5)+1). Sorry I think I a