mark_t isimli kullanıcının profili - etkinlik

I've noticed that if I start a dialog from a macro located in "My Macros & Dialogs", running the macro from the Basic IDE (for example using ctrl-shift-F8), then the dialog started is modal with the basic IDE and not with the active document.

This can then be a problem trying to debug the macros called from events triggered by controls in the dialog.

I can work around this problem by first setting the document active, I'm using the following to set the current document active.

StarDesktop.loadComponentFromURL(ThisComponent.URL,"_default",0,array()) ' sets focus to the document'

Is there a better method than this to set the active focus to ThisComponent?

Edit. Slightly better method to change focus from the Basic IDE to the document when starting a modal dialog. Either the DoEvents or a Wait seems to be needed to avoid the dialog still being attached to the Basic IDE.

ThisComponent.CurrentController.Frame.ContainerWindow.setFocus
DoEvents

Also is there some similar method that could create a dialog that is non-modal with documents but without it being modal with the basic IDE?

I saw this prior question how-to-make-non-modal-dialog answered by @pierre-yves-samyn, but wondered if the above behaviour could lead to some other method.

Attached contains sample dialog and macro. Untitled 29.ods

Note the macro module and dialog needs to be copied to "My Macros & Dialogs" to show the problem.

I'm using LibreOffice 5.2.1.2 x64 on Windows 8.1.

Edit to add code for fake non-modal dialog created using IDE and setting the dummy document invisible. Note the dialog is not shown in the windows task bar and can become hidden behind other windows. Also reference to ThisComponent will refer to the hidden dummy document.

Sub ShowHiddenDocDialog

    Dim oDummyDoc As Object
    Dim oController As Variant

    oDummyDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_default", 0, array())
    oController = oDummyDoc.CurrentController

    DoEvents    '   Wait for the new document to be activated'

    DialogLibraries.LoadLibrary("Standard")
    MySampleDialog = CreateUnoDialog(DialogLibraries.Standard.DocIdentify)

    oController.Frame.ContainerWindow.Visible = False

    MySampleDialog.execute()

    oDummyDoc.close(true)
End Sub

Which version of LibreOffice and which operating system? Is it recent problem when you changed to a new version or have you had this problem since you first installed?

Which Operating System? Try minimizing all windows in case there is a hidden dialog box that is waiting for input. If you have more than one display, check if there is a hidden dialog on the other display, even if it is not turned on. On windows 8.1 if you click the bottom right corner of the task bar all windows will be minimized, then click LibreOffice in task bar and hopefully the hidden dialog would be shown.

Your first expression where the column is defined in a named cell myROWA is possible using a named expression, but I think there is no way to pass an argument to a named expression so you would have to create the function data3 as a user defined function.

Unfortunately there is no way to pass cell ranges to user defined functions or to return cell ranges from a user defined function. Data from a cell range is converted to an array to pass to a function and an array of data can be returned from a function.

If you could give some information on how you plan to use the result of the expression it might be possible to help create a user defined function. For example if you plan to use =SUM(data3(E11)) then the data3 function could return an array of the data from the three sheets.

I think you need to specify the filter and also the filter options needs to be in quotes. Note the location of quotes and ":" in the example.

"C:\Program Files (x86)\LibreOffice 5\program\soffice.exe" --nofirststartwizard --convert-to csv:"Text - txt - csv (StarCalc)":"59,34,76,2,,,true" file.xls

Could you also check the tick mark to the left of the answer if that was the correct answer, Thanks.

Moved the info from your answer and comment to your question, and deleted that answer.

Edit to place all the code inside code block.

I am attempting to set the properties for all the rows and columns in a spreadsheet (using Basic). From reading the documentation, it would seem that the following should work, however, it does not:

Dim oDoc As Object, oSheet As Object, vValue As Variant

oDoc = ThisComponent    

oSheet = oDoc.Sheets.getByName("mySheet")

vValue = False

oSheet.Rows.setPropertyValue("OptimalHeight", vValue)

oSheet.Columns.setPropertyValue("OptimalWidth", vValue)

print "height: " & oSheet.Rows.getPropertyValue("OptimalHeight") & _
      "   width: " & oSheet.Columns.getPropertyValue("OptimalWidth")

When I run the above, the value returned via the Print is always True. I have changed the vValue to String and Boolean, and have used the Boolean value directly as follows: oSheet.Rows.setPropertyValue("OptimalHeight", False)

Any ideas why this fails? Does one have to write to the Sheet for the property to take affect???

I think you need to set the property for every row, example

For i = 0 to n
    oSheet.Rows(i).OptimalHeight = False
Next i

Also oSheet.Rows.getPropertyValue("OptimalHeight") only returns the status for the first row. Again you need to iterate over each row to get the status of every row.

You might use :-

oSheet.Rows.Height = oSheet.Rows.Height

Which sets OptimalHeight = False for all rows but also sets the height of all rows the same as the first row.

I don't even see it defined anywhere how much karma is needed to edit your own post. 75 to edit wiki questions, maybe it's the same to edit your own posts. I used to think it was just that new members didn't know where to find the edit button, but certainly doesn't seem to be true in this case as EarlEMelton sees the flag, close and delete.

If the combo box is in a Dialog or Userform then you need to populate the list for the combobox in the macro that initialises the Dialog.

Simple example below expects the range to initialise the combo box list to be in a vertical column range of cells.

Sub StartDialog

    Dim oList As Object
    Dim n As Integer
    Dim i As Integer
    Dim oDialog As Variant
    Dim oData As Variant

    ' Code for initiating and showing the dialog    '   
    DialogLibraries.LoadLibrary("Standard")
    oDialog = CreateUnoDialog( DialogLibraries.Standard.ImportTextFormat )

    oList = oDialog.getControl("FormatList")

    oList.Text = "Select text input format from the list."

    ' Read the data list from cell range into a variant array '
    oData = ThisComponent.Sheets(0).getCellRangeByName("C7:C9").DataArray

    n = ubound(oData)

    For i = 0 to n
        oList.addItem(oData(i)(0) ,i)
    Next i

    oDialog.Execute()
End Sub

@EarlEMelton, probably you don't have enough Karma points to edit your own questions, I know it seems stupid right? Post the attachment in an answer to your own question, then I'll copy it to your question and delete your fake answer. Did you try the suggestion to use cell format to blank zeros?

Removed the upload from the question as requested, although it does mean that now its not easy for anyone to understand what you were asking. If you can then edit your question to upload a modified file, or post it in an answer and I'll edit into your question.

I'm attaching a workbook with which I need help from someone more familiar with somewhat advanced formulas. I can usually hold my own with simpler math functions, but these might be called conditional formulas, for lack of a better term. A textbox on the first of two pages hopefully explains what's needed. All sensitive, identifying information has been removed. If you prefer to just give me the formulas needed and the cell ref into which they need to go, I can copy/paste, then fill down. Many TIA for any help!

Edit: Mark_T updated uploaded file from @EarlEMelton with personal data removed.

07_Properties_2016_NSI.ods

Okay, Mark_T, I see the pencil now so will attach the file. Yes, I copy/pasted your formulas, filled down, then selected all the pertinent cells, went to Format Cells and pasted the format statement. I may not be doing it right as I still see the zeros. When I go back from there to the Format Cells thing again, that format statement has changed!!! Note that I also finished taking out all the sensitive data...I think! :-) Appreciate your help so much.

A thought: Should I have pasted the format statement one at a time in each cell, rather than selecting all the calculated cells and doing so only once? I'm afraid to try anything and mess it up further but can certainly take the time to do them individually if you say that would work better. Just let me know.

Attached contains macro and some instructions on how to use it. When you open a csv file it saves the csv file format that was used and then it can be re-used by running the command that is added to the toolbar.

ImportText.odt

@EarlEMelton, the sheets 9-16 and 10-16 and YTD Summary still seem to contain names and addresses, hopefullly they are only fakes but just thought I'd bring them to your attention.

I would suggest to use the cell number format to show blank for a zero value cell instead of using the IF formula.

From the example in cell O4 the formula would be:

=H4-I4-K4-M4+P4

And the cell number format changed to:

[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;""

The added ;"" will show the zero value cells as blank.

The #Value result in O29 seems to be due to the cell P29 is ='10-16'.O32, which contains =IF(H32-I32-K32-M32+P32=0,"", H32-I32-K32-M32+P32). I think a formula that returns "" is not treated as zero.

Edit to add: Set up the formula and the cell format for O4, then copy cell O4 and paste that to the range of cells O4:O32

Set the same cell format for P4, then copy and paste special, formats only to P5:P32.

Similar change to sheet 10-16 should fix the #Value error.

Drill down functionality exists in LibreOffice Calc pivot tables if the option is enabled.

From the Pivot Table Layout dialog, expand Options (click on +) and check the box for "Enable drill to details".

Double click the count cell "15" in the example pivot table will open a new worksheet with the details of the 15 customers.

Edit: Suspect the use of dispatchURL but that might have been a sub that you did not post.

Updated version below, also tried to simplify the code a little. Hopefully it works as intended.

Option Explicit

Sub copyCurrentSheet

    Dim oController As Variant
    Dim oDocument As Variant
    Dim oDispatcher As Variant
    Dim doc1 As Variant
    Dim fileDate1 As String
    Dim fileDate2 As String
    Dim oSheet1 As Variant
    Dim oSheet2 As Variant
    Dim oSheets As Variant
    Dim oCell As Variant
    Dim y As Integer
    Dim fileNameExists As Boolean
    Dim printerOption(0) AS NEW com.sun.star.beans.PropertyValue

    fileDate2 = format(Date, "MMM D, YYYY")
    fileDate1 = fileDate2

    doc1 = ThisComponent 
    oSheet2 = doc1.CurrentController.ActiveSheet

    oSheets = doc1.getSheets()

    y = 0
    fileNameExists = True

    Do While fileNameExists
        If oSheets.hasByName(fileDate1) Then
            y = y + 1 
            fileDate1 = fileDate2 & " " & y
        Else 
            fileNameExists = False
        End If
    Loop

    oController = doc1.getCurrentController
    oDocument   = oController.Frame
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

    oDispatcher.executeDispatch(oDocument, ".uno:SelectAll", "", 0, array())
    oDispatcher.executeDispatch(oDocument, ".uno:Copy", "", 0, array())

    doc1.getSheets().insertNewByName(fileDate1, 0) 

    oSheet1 = doc1.getSheets().getByIndex(0)
    oController.setActiveSheet(oSheet1)

    oDispatcher.executeDispatch(oDocument, ".uno:Paste", "", 0, array())

    printerOption(0).Name = "PaperOrientation" 
    printerOption(0).Value = com.sun.star.view.PaperOrientation.LANDSCAPE 
    doc1.Printer = printerOption()

    oSheet1.Printareas = oSheet2.Printareas

    ' freezes active sheet '
    oController.freezeAtPosition(11,2)

    oCell = oSheet1.getCellRangeByName("F1") 
    oCell.Value = DateValue(Now)
    oCell.NumberFormat = 75

    oController.select(oSheet1.getCellRangeByName("C4"))

    oSheet1.Protect("")
End Sub

Suspect the numbers are being considered by Calc as text and therefore sorted by alphabetic order instead of numeric.

If you have locale where "," is the decimal separator, then when you enter "1." this will be treated as text and not a number. Solution would be to enter "1", "2" etc instead of "1.", "2." etc.

Other possible cause is if data is imported from csv files with incorrect choice of format for the column of data.

Use "View", "Value Highlighting" to show numeric data in blue and text in black to confirm your data is entered correctly.

You might also add the macro to the toolbar so you can easily refresh all the pivot tables after you have entered new data instead of closing and re-opening the document. Use Tools, Customise..., select the Toolbar tab, select save in the document instead of save in Calc as you only need to use this for the document that has pivot table. Scroll to the end of the list of commands, click on Add, in category under LibreOffice Macros, find the macro and then click on Add.

If the first column of the table is not sorted, you might want to add a fourth argument of 0. =VLOOKUP(E29,$I$33:$N$39,MATCH(F29,$J$32:$N$32,0)+1,0)

@DiesNuts, sorry but I thought it would be easily understood when you saw the formula. Match will search the column headers and return the position of the match to F29, this gets the correct column from the table after we add 1. Then Vlookup searches the table to find the row where the first column matches E29 and returns the value found in the column specified by the match function.

Use VLOOKUP where MATCH is used to find the column.

=VLOOKUP(E29,$I$33:$N$39,MATCH(F29,$J$32:$N$32,0)+1)

Where E29 is 550 and F29 is 48.

Or you could set A6 = -M7, and then change the format to white text on white background so it is not visible, or hide row 6.

I think the XIRR function expects a single column of data, you could create a helper column, that would assemble the values into a single column. Maybe hide that column so it is not visible.

LibreOffice can open Excel .xls files with some limited support for the contained VBA code. I doubt it would even be close to handling a VBA project of 10,000 lines.

LibreOffice Basic and VBA are very similar, but the difference between Excel Objects and LibreOffice UNO Objects and structures is what makes the conversion difficult.

I think you should consider some other programming language for your 10,000 line project, perhaps using LibreOffice Calc only as an interface to the user for input and output. I'm working on something similar, trying to use this as an opportunity to restructure the whole project and making very slow progress.

I use LibreOffice for new work, but keep Excel available until no longer needed. It still ties me to Windows but hopefully I can break the tie in a few years.

I deleted your duplicate post to avoid confusion.

duplicate post in-writer-can-i-delete-all-text-which-has-the-same-style

You can do this from the Find and Replace dialog.

From the Menu "Edit", "Find & Replace..." or press ctrl+h.

Expand "Other Options" and select "Paragraph Styles".

From the "Find" drop down list select the style "Scene Header".

Click on "Find All". This selects all text with the selected paragraph style.

Use the "Del" key to delete the selected text.

Use the tilde "~" to concatenate two ranges, example

=SUM(A3:A5~C7:C10)

I've never used the XIRR function and don't really understand from your description what you are trying to accomplish.

Which version of LibreOffice and which OS are you using?

If you have a version of windows and recent version of LibreOffice, then the default install creates a LibreOffice shortcut on the desktop, which can also be added to the start menu, starting LibreOffice from the shortcut will show the recent files that were used which can be selected by a single click on the preview image.

It's probably similar on other OS, key is to start libre office using soffice instead of scalc, swriter etc.