Page 1 of 1
[Solved] VBA to LibreOffice question
Posted: Mon Mar 16, 2020 1:19 pm
by comandos
I need a little help editing my existing VBA code to work with libre office.
I would like:
1) to copy errors in column C if the date in the same row in yellow,
2) sort them all by date oldest to newest
3) (if possible) if the button (that triggers the macro is pressed it clears any previous entries in “STATUS” tab).
https://imgur.com/a/GQVmEPv ← example
code
Code: Select all
Option Compare Text
Sub getAllErr()
Dim ws As Worksheet
Dim lastRow As Integer
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "status" Then
For i = ws.UsedRange.Rows.Count To 2 Step -1
If ws.Cells(i, 1).Interior.Color = 65535 Then
lastRow = ThisWorkbook.Sheets("status").Cells(Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Sheets("status").Cells(lastRow + 1, 1) = Format(ws.Cells(i, 1), "Short Date")
ThisWorkbook.Sheets("status").Cells(lastRow + 1, 2) = ws.Name
ThisWorkbook.Sheets("status").Cells(lastRow + 1, 3) = ws.Cells(i, 3)
End If
Next i
End If
Next ws
End Sub
MsgBox "Kopirano"
BR
Re: VBA to libreoffice question
Posted: Mon Mar 16, 2020 7:26 pm
by F3K Total
Re: VBA to libreoffice question
Posted: Tue Mar 17, 2020 8:14 am
by comandos
It is I'm just looking for help, Should I delete the second post?
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 8:46 am
by robleyd
It is courteous to advise when cross posting and provide a link so people don't duplicate suggestions that have already been made elsewhere.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 8:49 am
by comandos
robleyd wrote:It is courteous to advise when cross posting and provide a link so people don't duplicate suggestions that have already been made elsewhere.
My bad, sorry.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 9:42 am
by Bidouille
No need macro, use a conditional formatting.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 9:47 am
by comandos
Bidouille wrote:No need macro, use a conditional formatting.
Please elaborate.
If I need to pull date from different sheets and the data changes. Sure I could apply conditional formatting after I have runed the macro but I would have to do this every time the macro is runed.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 10:19 am
by Bidouille
comandos wrote:Please elaborate.
Please provide a sample document.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 10:24 am
by comandos
Bidouille wrote:comandos wrote:Please elaborate.
Please provide a sample document.
Attached the sample file
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 2:09 pm
by mikele
Hello,
is there a rule for the yellow date?
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 2:36 pm
by comandos
Yellow data marks the date of the error, the error is then written in column C for each device (devices are listed in tabs)
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 2:56 pm
by mikele
Hello,
but obviously not every error leads to a yellow date. Why?
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 2:59 pm
by comandos
I decide which error is important and mark them manually with yellow, I just need a macro that gathers all the yellow errors and presents them under »status«
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 4:05 pm
by Zizi64
I decide which error is important and mark them manually with yellow, I just need a macro that gathers all the yellow errors and presents them under »status«
Mark the errors by numbers in an independent column. Then you can sum them easily. Never use the formatting propeties as some "data" what you need handle in the future.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 5:26 pm
by comandos
All I need is how to edit the existing macro to COPPY text from the designated field.
If anyone here can help that would be appreciated
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 6:29 pm
by Zizi64
All I need is how to edit the existing macro to COPPY text from the designated field.
- Use ODF file types in the AOO/LO instead of the MS fileformats
- Rewrite all of your macros based on the StarBasic and the API functions of the AOO/LO,
because the VBA macros will nor run in AOO, and you can not store the Starbasic+API macros in an xlsm document (they will not run in Excel).
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 11:24 pm
by comandos
I can't code.
Re: VBA to LibreOffice question
Posted: Tue Mar 17, 2020 11:26 pm
by RoryOF
None of us could - we had to learn. Needing to do something is a great incentive.
Re: VBA to LibreOffice question
Posted: Wed Mar 18, 2020 7:43 am
by Zizi64
I can't code.
Then do not use macros in AOO/LO
Re: VBA to LibreOffice question
Posted: Wed Mar 18, 2020 12:13 pm
by mikele
Hi,
if you change one code line:
Code: Select all
ThisWorkbook.Sheets("status").Cells(lastRow + 1, 3) = ws.Cells(i, 3).Text
the macro works under LibreOffice.
Re: VBA to LibreOffice question
Posted: Thu Mar 19, 2020 9:54 am
by comandos
mikele wrote:Hi,
if you change one code line:
Code: Select all
ThisWorkbook.Sheets("status").Cells(lastRow + 1, 3) = ws.Cells(i, 3).Text
the macro works under LibreOffice.
THANK YOU!