Haz tu Pregunta

perfil de jheath - actividad

2019-06-06 17:20:48 +0200 recibió una medalla  Pregunta Popular (fuente)
2018-11-01 15:15:59 +0200 la mejor respuesta fue marcada Accept cell edits to be able to modify cell

I have some finance journals in LibreOffice Calc, and one requirement is that I should be able to type a partial account name in an account cell, press enter, and it will go look up the full account name and place it in the cell. But if I am currently typing in the cell, there is an insertion point in the text, and I can't actually change the text in that cell.

For several years I have been using the following LibreOffice BASIC code to check to see if we are in Edit Mode, and then (I guess) Accept the edits, and remove the insertion point. See previous discussion at these two links:



    Dim i As Integer
    Dim oAC As Object
    Dim oACToolbar As Object
    Dim oACSumCancel As Object
    Dim oACFunctionAccept As Object

    ' Check to see if we are in edit mode (i.e. with active cursor in the cell)
    ' Lots of things could go wrong, set up an error routine
    On Error Goto SkipFocus
    oAC = oDoc.CurrentController.Frame.ComponentWindow.AccessibleContext
    For i = 0 to oAC.AccessibleChildCount() - 1
        oACToolbar = oAC.getAccessibleChild(i).AccessibleContext
        If oACToolbar.AccessibleName = "Formula Tool Bar" Or _
                oACToolbar.AccessibleName = "Barre d'outils Formule" Then
            Exit For
        End If
    Next i
    If i < oAC.AccessibleChildCount() Then
        oACSumCancel = oACToolbar.getAccessibleChild(3)
        If oACSumCancel.AccessibleName <> "Sum" And oACSumCancel.AccessibleName <> "Somme" Then
            ' We are in edit mode, attempt to get out of it or else we can't modify the cell
            oACFunctionAccept = oACToolbar.getAccessibleChild(4)
            ' Assume that first child is the document, put focus there
        End If
    End If
    On Error Goto 0
    Exit Sub
    On Error Goto 0

But now that I have upgraded to LibreOffice, that code no longer works. If the insertion point is still there, like this:

image description

then when I process an Enter press, I can set the string of the selection, and it seems to "take" (the MsgBox afterwards shows the correct account name that has been looked up):

' Store the full account name back into the cell
' see if it changed...

But when focus moves to the next object, the text reverts to the "Heath" text, not the full account name. I assume that's because the text up in the Formula bar gets "applied" and wipes out the contents of the cell.

So is there an easier/better way to do this in the new LibreOffice? I never did like that code anyway... I need a way to tell Calc (in my Basic macro) that it should "Accept" the edits in the current cell, and allow me to modify the cell.

2018-11-01 15:15:41 +0200 respondió a una pregunta Accept cell edits to be able to modify cell

With a little bit of perseverance with MRI, I was able to answer my own question. I no longer blindly use getAccessibleC

2018-11-01 10:13:47 +0200 editar pregunta Accept cell edits to be able to modify cell

Accept cell edits to be able to modify cell I have some finance journals in LibreOffice Calc, and one requirement is tha

2018-11-01 10:08:55 +0200 realizó una pregunta Accept cell edits to be able to modify cell

Accept cell edits to be able to modify cell I have some finance journals in LibreOffice Calc, and one requirement is tha

2018-11-01 08:43:35 +0200 recibió una medalla  Crítico (fuente)
2018-04-16 15:05:26 +0200 recibió una medalla  Buena Respuesta (fuente)
2016-12-20 16:42:20 +0200 recibió una medalla  Estudiante (fuente)
2016-12-20 16:42:00 +0200 recibió una medalla  Autodidacta (fuente)
2015-11-16 14:20:24 +0200 la mejor respuesta fue marcada Calc macro copy and paste woes

I am banging my head on what should be a simple copy and paste job. I have information on an invoice sheet, and I just want to copy and paste it to a receipt page and print it out. My first attempt used copyRange, which seemed to work sometimes but not others. Specifically, it seemed to work better when I was stepping through the code, but if I ran it at full speed, it always seemed like something went wrong - the data wasn't copied, it printed the source sheet rather than the receipt, etc.

So then I tried shifting to using copy/paste based on the macro recorder. (Frankly I hate the fact that the macro recorder seems to be living in a whole different world. For example, where can I find the arguments to ".uno:InsertContents"? Nowhere...) So this is the code I currently have:

document   = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

shtSource = oDoc.Sheets.getByName(sTransType)
rngPrintArea = shtSource.getCellRangeByName(sTransType & "PrintArea")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

shtReceipt = oDoc.Sheets.getByName("Receipt")
rngDest = shtReceipt.getCellRangeByName("ReceiptOne")
dim args(5) as new com.sun.star.beans.PropertyValue
args(0).Name = "Flags"
args(0).Value = "SVDT"
args(1).Name = "FormulaCommand"
args(1).Value = 0
args(2).Name = "SkipEmptyCells"
args(2).Value = false
args(3).Name = "Transpose"
args(3).Value = false
args(4).Name = "AsLink"
args(4).Value = false
args(5).Name = "MoveMode"
args(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args())
rngDest.CellBackColor = RGB(255,255,255)
' Make sure the columns are the right width
For i = 0 to rngPrintArea.Columns.getCount-1
    rngDest.Columns.getByIndex(i).Width = rngPrintArea.Columns.getByIndex(i).Width
Next i

I left out some of the details... Let me make a couple of comments on the code. I do a ClearContents(1023) - i.e. everything - so that I don't get that annoying pop-up that asks if you want to replace the current contents. (Shouldn't there be an option in .uno:InsertContents that says replace contents without asking?) I turn the cell background color to white so I don't waste ink printing a gray background. And the final loop makes sure that the column widths match the original - that information doesn't appear to ... (more)

2015-11-16 14:17:35 +0200 la mejor respuesta fue marcada Calc macro print specific sheet

I am trying to print a specific Calc sheet with a BASIC macro. The situation is that I am entering finance information on one sheet, then click a button to process the transaction, which (among other things) copies some data to a separate receipt sheet, and I want to print that receipt. All that I have seen on printing refers to a Print method for the entire document (and not a sheet, for example), with no way to specify the sheet. There are some print options, including which "page number", but I believe this just refers to which pages to print from the current sheet. The Print method seems to be pretty good at printing the current sheet, so I would think the following would work:

Dim view As Object
view = oDoc.getCurrentController()

And it turns out that this does work, but only(!) when I am stepping through the code, and allow the receipt sheet to appear physically on the screen. But when I run the code full-speed, it always just prints the source sheet! It's almost like Print will print what is visible on the screen, but the setActiveSheet goes by so quickly that the receipt isn't visible (yet), and it just prints what is currently on the screen.

So what is the solution?

2015-11-14 21:43:27 +0200 la mejor respuesta fue marcada Select cell to remove text insertion point

I have set up calc basic macros to load the contents of a cell (which is a partial account name) when I type Enter, look up the "full" version of the account name from a table, and then save the full version back into the cell. This works great when the cell is selected and there is not an active insertion point in the text of the cell. But if I select the cell, type the partial account name (so that the flashing insertion point bar is still in the cell), then press Enter, the code runs, but when I SetString on the cell, the text appears to be "locked" - since it's being edited, I suppose - and the full version of the account name is NOT written back into the cell.

I tried selecting the cell before processing, but since it is already the active cell, selecting it appears to change nothing. I can select an adjacent cell, then go back to select the original cell, and then the code works fine, but I don't have a reliable way to easily identify a cell on the page that I can select that I know won't be locked. (And that's kind of a kludge anyway.)

What I would really like is a method of a cell that would tell it to "finish" any current editing going on, so that when I SetString back into the cell, it will "take". Does such a method exist? (I'm sure it most...) I have looked in the API documentation, but I find those docs kind of a mystery (and I'm a quite experienced programmer). I assume it would be related to SheetCell, but from that page where would I go to find what I need? (So an answer to my specific problem, but also a little bit of searching advice so that I can find my own answer in the future would both be appreciated!)

Thanks, Jeff

2015-11-10 10:23:36 +0200 la mejor respuesta fue marcada Auto run macro on open - can't assign to macro in document

This seems like it should be an easy question, but it has me stumped. I have an .ods file that I converted from an Excel .xls file which had an Auto_Open procedure, and the .ods file now has a Standard Library (in the document), Modules under that, and Module1 under that which contains a Main procedure. If I organize macros, select Module1 and click on Assign, I see that Standard.Module1.Main is assigned to Open Document. Note that at the bottom of the Events dialog it says "Save In" and gives the name of the .ods file that I'm opening and that contains the macro. And sure enough, when I open the document, it runs the macro.

Now I create a brand new Calc document, organize macros, create a new Module1 under Standard, it creates a deafult Main procedure there for me, and I add one line: [msgbox "In Main"]. I can run the macro, but when I go to the Organizer and click Assign, I cannot assign it to the Open Document event. If I click the Assign: Macro button, it only gives me the choice of macros in My Macros or in LibreOffice Macros. If you open the organizer from the IDE, it seems like that the "Save In" box only allows a choice of LibreOffice, and I thought this was the problem. But if I open the organizer from within the Calc document itself, it gives me a choice of the .ods document name in the "Save In" box, but if I try to assign a macro, it STILL only gives me the choice of macros in My Macros or in LibreOffice Macros.

Does anyone know what I am doing wrong? Thanks, Jeff

2015-10-05 01:35:16 +0200 recibió una medalla  Pregunta Famosa (fuente)
2015-08-28 13:59:07 +0200 recibió una medalla  Pregunta Famosa (fuente)
2015-01-28 02:31:57 +0200 recibió una medalla  Pregunta Famosa (fuente)
2015-01-12 12:05:43 +0200 recibió una medalla  Pregunta Famosa (fuente)
2015-01-12 12:05:43 +0200 recibió una medalla  Pregunta Notable (fuente)
2014-05-19 16:30:29 +0200 recibió una medalla  Pregunta Famosa (fuente)
2014-05-16 12:48:14 +0200 recibió una medalla  Pregunta Notable (fuente)
2014-05-16 12:05:17 +0200 respondió a una pregunta Calc BASIC can’t append to network text file from Linux

This appears to be a bug in LibreOffice which I am in the process of reporting here: https://bugs.freedesktop.org/show_bug.cgi?id=77902.

I found a work-around, but it’s pretty ugly, and probably quite dependent on the version of Linux that you are using. We use Linux Mint 16 for all of our office computers, so the key thing that I found was that when I connect to our server at "smb://abc.def.org/server", and then perform a logical network access in LibreOffice with Samba like this: "smb://abc.def.org/server/Data/Finance/Reconcile.log", that the operating system at some point converts that to a "normal" Linux file access that's not via Samba, and in the case of Linux Mint 16, that "normal" access will be through this path:


where 1000 happens to be my Linux user ID (which you can see with the "id" command). I found, however, that there was a convenient environment variable named XDG_RUNTIME_DIR which has the first part of this path, including the user ID. From what I’ve heard from other people, my guess would be that in Ubuntu this "normal" Linux file access would be through "~/.gvfs/...", but I don't know if the environment variable would help you in that case or not.

So for my work-around, I create a server path which is different depending on whether I am on a Windows or Linux machine (the "OS" environment variable will start with "Windows" on a Windows machine). On a Windows machine, we continue to use the logical server file access. On a Linux machine I create the ugly "normal" file path for server file access. So here's the code:

If Environ("OS") Like "Windows*" Then
    sServerPath = "file://abc.def.org/server/"
    sServerPath = Environ("XDG_RUNTIME_DIR") & "/gvfs/smb-share:server=abc.def.org,share=server/"
End If
Open sServerPath & "Data/Finance/Reconcile.log" For Append As #1
Print #1, "Reconciling at: "; Format(Date, "d-mmm-yyyy"); " "; Time
Close #1

Yes, it's ugly, but it is the only work-around that I could find. Hopefully my bug report will inspire a fix, so we won't have to do these kinds of gymnastics in the future.

2014-05-14 13:27:16 +0200 recibió una medalla  Pregunta Notable (fuente)
2014-05-14 10:43:49 +0200 respondió a una pregunta Linux access to Windows server no lock files

We found a work-around to this problem in the answer from oweng found here:


Specifically the code line that starts with "sed", but we found we needed to prefix it with a "sudo", like this:

sudo sed -i 's/X-GIO-NoFuse=true/#X-GIO-NoFuse=true/' /usr/share/applications/libreoffice-*

So it appears to be some sort of LO optimization that needs to be turned off to avoid this bad behavior.

2014-05-14 10:36:31 +0200 recibió una medalla  Pregunta Popular (fuente)
2014-05-01 11:45:29 +0200 recibió una medalla  Pregunta Famosa (fuente)
2014-04-17 16:31:00 +0200 recibió una medalla  Pregunta Popular (fuente)
2014-04-17 13:15:41 +0200 realizó una pregunta Linux access to Windows server no lock files

My LibreOffice woes continue regarding access to a Windows 2012 server (via DFS) from Linux Mint 16. In addition to the problems appending to a text file on the server ( http://ask.libreoffice.org/en/questio... ), now we are noting that sometimes a Calc document will be opened from a Linux machine and it will not create a .~lock. file, so another user can open the file thinking he has exclusive access and make changes, save and quit, but those changes will be lost if the original user then saves his changes. We are using the default Samba settings, and accessing the server through smb://myserver.org/server. Is the problem with Samba? LO? Is there another way to access our Windows server that I could try other than Samba?

Maybe this is beyond the scope of this forum, but if anyone has some pointers or suggestions, I would be very appreciative.

2014-04-08 17:50:46 +0200 recibió una medalla  Pregunta Famosa (fuente)
2014-04-08 17:30:05 +0200 comentar pregunta Calc BASIC can’t append to network text file from Linux

I also tried shifting to using the SimpleFileAccess methods:

oFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
oOutStream = oFileAccess.openFileWrite(sUrl)

But using those methods, when I try to openFileWrite it gives me an UnsupportedDataSinkException.

2014-04-08 14:00:23 +0200 comentar pregunta Calc BASIC can’t append to network text file from Linux

I upgraded LO to (latest stable for Linux Mint, I believe), and still have the problem.

2014-04-08 12:12:22 +0200 comentar pregunta Calc BASIC can’t append to network text file from Linux

I forgot to mention that all of these server accesses work fine from a Windows machine.

2014-04-08 11:49:58 +0200 realizó una pregunta Calc BASIC can’t append to network text file from Linux

Thanks to the discussion at http://ask.libreoffice.org/en/questio... I can now open CSV files on our Windows server as Calc documents, even from Linux. The key was using the ConvertToURL function, which changes the initial "file://" of a file path to "smb://", so that Linux knows to use Samba to access the file.

But now I’m trying to do some simple file access to just append a few lines of text to a log file on the Windows server, and I am again having problems. You can see from that other page that I gave a basic example of reading from a text file. That still seems to work fine, whether you use "file://" or "smb://" at the beginning of the path. But if I try to open the file for Output or Append, I always get an error 57 Device I/O error on the open command. But that doesn’t happen if I am writing to a local file on the computer, just over the network. And it doesn’t happen if I open the file for Input to read from a file on the server.

Just to make it clear, here’s what works:

fileURL = "smb://abc.def.org/server/Data/Finance/Reconcile.log"
Open fileURL For Input As #1
Line Input #1, sStr

This works if I put "file://" at the start as well, but I imagine smb is probably better, since it is required for the loadComponent call. This also works:

fileURL = "file:///home/user/Bureau/Reconcile.log"
Open fileURL For Append As #1
Print #1, "This is a test"

which is on the local machine, for Output or Append. But this fails with an error 57 on the Open call:

fileURL = "smb://abc.def.org/server/Data/Finance/Reconcile.log"
Open fileURL For Append As #1
Print #1, "This is a test"

So the problem is just writing to the text file on the server. But I use the storeAsURL method to save .ods files to the server from Linux quite a lot, so there isn’t a networking or permissions problem. In fact when I run that Open/Append command with a fileURL of a file that does not exist, it actually creates the file on the server before giving the error.

Any suggestions? (Linux Mint 16 French, LO Calc French)

2014-04-02 09:38:04 +0200 recibió una medalla  Pregunta Notable (fuente)
2014-03-20 10:26:12 +0200 comentar respuesta Set cell background color based on adjacent cell value

Thanks again for that insight. I assume that putting the formatting in the cell formulas would only work if your cell has a formula (and then you "add in" the conditional formatting). If the user can type a number into that cell, that would wipe out any formula that was there, correct? So I think I need to use the conditional formatting from the menu.

2014-03-20 10:04:05 +0200 recibió una medalla  Comentador
2014-03-20 10:04:05 +0200 comentar respuesta Calc basic decimal rounding suggestion

Thanks, oweng. Yes, that section in Pitonyak's document was just what I needed to read. It seems like in the old Excel days that it wasn't quite this complicated, but Excel was probably just making assumptions for us, and probably not always correctly. So it's probably not a bad thing to have this fine level of control. Note that in the latest version of OpenOffice.org Macros Explained (just downloaded from here), listings 407-408 contain the relevant code.

2014-03-19 19:19:24 +0200 recibió una medalla  Pregunta Notable (fuente)
2014-03-19 16:04:09 +0200 comentar respuesta Calc basic decimal rounding suggestion

I believe using Double helps, thanks. What I don't really understand is the "multi" nature of the different types of data. If I setValue(), then any String that was there gets wiped out, correct? And if I have a String, a ClearContents of Values doesn't touch it. Well if the user is entering data in cells, and enters "1234" then it becomes a Value, but if he enters "1234a" then it becomes a String. How do I reliably pull the data out of the cell in my code if I don't know what it is?

2014-03-19 11:57:49 +0200 recibió una medalla  Pregunta Popular (fuente)
2014-03-18 14:55:47 +0200 comentar pregunta Calc basic decimal rounding suggestion

Another related thing I don't really understand: I don't really understand the dual nature of a cell as string or value. E.g. if I setString(Format(nAmount, "0.##")), what would be the implications be for the use of that cell in a SUM() formula? Does it no longer have a "value" just a "string"? What is the real difference in how those two things are treated?

2014-03-18 13:38:50 +0200 la mejor respuesta fue marcada Set cell background color based on adjacent cell value

I have a finance journal in Calc and I would like to use (I think) conditional formatting to set the background color of a cell, but not based on its own value, but the value of an adjacent cell, in fact when it matches a certain account name. So if I have FIN: Bank in column 1, I would like to apply the cell format Highlighted in column 2. Any other contents in column 1 and column 2 would just have the Default formatting. Is that possible?