Ask Your Question
0

[SOLVED] How Can I Delete Data with Macro [closed]

asked 2019-05-07 10:34:35 +0200

libremacrouser gravatar image

updated 2019-05-08 14:59:31 +0200

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-05-08 22:37:02.455510

2 Answers

Sort by » oldest newest most voted
0

answered 2019-05-07 16:01:29 +0200

karolus gravatar image

updated 2019-05-07 16:21:13 +0200

Maybe youre about something like:

Sub CopyPasteworking



    range_address = CreateUnoStruct("com.sun.star.table.CellRangeAddress") 

    cell_address = CreateUnoStruct("com.sun.star.table.CellAddress")


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

    identifiers = sheet1.getCellRangeByName("A1:A7").getDataArray()
    used_entrys = sheet2.getCellRangeByName("A1:A7").getDataArray()

    c = 0
    for i = 0 to 6
        unused = true
        for n = 0 to 6
            if identifiers(i)(0) = used_entrys(n)(0) then
                goto label              
            end if
        next n
        if unused then                  

            source = range_address
            source.StartRow = i
            source.EndRow = i
            source.EndColumn = 2

            target = cell_address
            target.Sheet = 2
            target.Row = c

            sheet1.copyRange(target, source)
            c = c+1
        end if
        label:
    next i  

End Sub
edit flag offensive delete link more

Comments

@karolus You are great! Thank you. It run. Ask for improvement, It can arrange otomatically its rows and colums? For example my data can be 1000 so I need to write 1000 instead of 6 in my for loop and I should write A1:A1000 instead of A1:A7. So is there any ways to do for it? In addition, I want to add sheet in front of these sheet, but I couldn't. How can I do it?

libremacrouser gravatar imagelibremacrouser ( 2019-05-08 10:27:30 +0200 )edit

Yes, of course, change for Example:

identifiers = sheet1.getCellRangeByName("A1:A1000").getDataArray()
used_entrys = sheet2.getCellRangeByName("A1:A500").getDataArray()

c = 0
for i = 0 to 999  'accord to **identifiers** Range'
    unused = true
    for n = 0 to 499  'accord to **used_entrys** Range'
karolus gravatar imagekarolus ( 2019-05-08 11:06:56 +0200 )edit

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 couldn't. How can I do it?

libremacrouser gravatar imagelibremacrouser ( 2019-05-08 12:07:33 +0200 )edit

please dont ask that stupid! its obviously either in menu > Sheet > Insert Sheets or in the Contextmenu of the Sheettabs.

and its also obvious that you have to change the respective Indeces in the Makro in these lines:

sheet1 = document.sheets(0)  'points in any case to the very first sheet'
sheet2 = document.sheets(1)  'etcpp'
sheet3 = document.sheets(2)

if you want to avoid those sideeffects on changing your sheetstructure, you may decide for:

sheet1 = document.sheets().getByName( "ANA TABLO" )

but now you need to change the Sheetname in Makro if you rename the Sheet itself.

karolus gravatar imagekarolus ( 2019-05-08 15:19:09 +0200 )edit

I changed my code like you said before you said me like this:

    sheet1 = document.sheets(1)
    sheet2 = document.sheets(2)
    sheet3 = document.sheets(3)

    target = cell_address
    target.Sheet = 3
    target.Row = c

But It I couldn't run it. And I began to think is there any places to change there or not. target.Sheet was 2 before and I changed it with 3 because of I added a new sheet.

libremacrouser gravatar imagelibremacrouser ( 2019-05-08 15:29:40 +0200 )edit

Ok sorry…

youre need also to insert a additional line :

        source.Sheet = 1
karolus gravatar imagekarolus ( 2019-05-08 15:44:13 +0200 )edit

Thank you! I solved my problem completely

libremacrouser gravatar imagelibremacrouser ( 2019-05-08 15:59:18 +0200 )edit
0

answered 2019-05-07 12:10:07 +0200

Lupp gravatar image

updated 2019-05-07 12:38:38 +0200

I didn't study your code thoroughly. Roughly, it seems much too complicated to me. A clear description of the intentions in ordinary words might help.
Basically you can clear (make blank again / force to Type=0) a cell in a macro by assigning the empty string to either its .String or to its .Formula property. You can do the same for ranges using .setDataArray() or setFormulaArray()with arrays filled with Variant/Empty (like freshly dim'd) or with empty strings.
In addition there is the .clearContents(Flags) method callable for any SheetCellRange or SheetCellRanges object as described here. It allows for using flags to specify the ways of working more precisely.

===Edit1 2019-05-07 10:35 UTC regaring the first comment below===

Sub demo
a1 = ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, 0, 0) 
REM Change this according to your testing
REM Ways to clear a cell/range: Make it BLANK again without touching attributes

REM Way 1 REM Only single cell
a1.String = ""

REM Way 2 REM Only single cell
a1.Formula = ""

REM Way 3 Also applicable if a1 actually is a cell RANGE of more than one cell
Dim cleanA(a1.Columns.Count-1, a1.Rows.Count-1) REM All the elements are Variant/Empty mow.
a1.setFormulaArray(cleanA)

REM Way 4 With Flags set to 7 it will clear text, ordinary numbers, dates(recognized by format). 
REM No attributes or styles
a1.clearContents(7) 

REM Variant 4a Should clear everything, including attributes, styles, objects.
a1.clearContents(1023)
End Sub

===End Edit1===

edit flag offensive delete link more

Comments

Hi @Lupp Thanks for your reply. Can you write me a simple clearContents code. I researched about it in a detail way but I couldn't understand what I will implement it in my code. For example I will delete PasteCell but its cell to be described as a (x,y) so how will I write it? Can you help me about it? Thanks a lot

libremacrouser gravatar imagelibremacrouser ( 2019-05-07 12:16:03 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-05-07 10:34:35 +0200

Seen: 207 times

Last updated: May 08