0

I am using draw to mark up a pdf format index map. So in grid 99, the text hyperlinks to map99.pdf

There are 1000's of grid cells - is there a way for a (macro) to scan for text in a sheet that is like

Text in File | Link to add
99|file:///c:/maps/map99.pdf
100|file:///c:/maps/map100.pdf

and add links to the relevant file whenever the text is found (99,100 etc).

I don't use libre much but happy to implement any programatic solution.

GeorgeC
  • 956
  • 5
  • 16
  • 40
  • 1
    VBA is not LibreOffice and never the twain shall meet. Are you saying you are looking for an [excel-vba] solution to use in [excel] and NOT [libreoffice]? –  Feb 14 '16 at 12:29
  • @Jeeped -thank you. Egg on my face as I didn't know that VBA doesn't work in libreoffice. I am looking for any programmatic solution for this in Libreoffice Draw as it is adding links to a pdf file and exporting back into pdf.. – GeorgeC Feb 14 '16 at 12:32

1 Answers1

1

Ok, after using xray to drill through enumerated content, I finally have the answer. The code needs to create a text field using a cursor. Here is a complete working solution:

Sub AddLinks
    Dim oDocument As Object
    Dim vDescriptor, vFound
    Dim numText As String, tryNumText As Integer
    Dim oDrawPages, oDrawPage
    Dim oField, oCurs
    Dim numChanged As Integer

    oDocument = ThisComponent
    oDrawPages = oDocument.getDrawPages()
    oDrawPage = oDrawPages.getByIndex(0)
    numChanged = 0
    For tryNumText = 1 to 1000
        vDescriptor = oDrawPage.createSearchDescriptor
        With vDescriptor
            '.SearchString = "[:digit:]+"  'Patterns work in search box but not here?
            .SearchString = tryNumText
        End With
        vFound = oDrawPage.findFirst(vDescriptor)
        If Not IsNull(vFound) Then
            numText = vFound.getString()
            oField = ThisComponent.createInstance("com.sun.star.text.TextField.URL") 
            oField.Representation = numText
            oField.URL = numText & ".pdf"
            vFound.setString("")
            oCurs = vFound.getText().createTextCursorByRange(vFound)
            oCurs.getText().insertTextContent(oCurs, oField, False)
            numChanged = numChanged + 1 
        End If
    Next tryNumText
    MsgBox("Added " & numChanged & " links.")
End Sub

To save relative links, go to File -> Export as PDF -> Links and check Export URLs relative to file system.

I uploaded an example file here that works. For some reason your example file is hanging on my system -- maybe it's too large.

Replacing text with links is much easier in Writer than in Draw. However Writer does not open PDF files.

There is some related code at https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=1401.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • Thank you. So I am new to macros in Libre. I created a new one from tools--macros--organise--basic. When I run it I get runtime error (object variable not set). In tools--options--base--database I just see one for Bibliography. What do I need to add here and in the code? Also is there any way to add a relative link to the file? I can send you my pdf if you want. – GeorgeC Feb 15 '16 at 23:08
  • 1
    The error was due to a typo on my part -- should have been args1 instead of args2. I changed it in the answer. Database options are only for using databases, not needed for this code. – Jim K Feb 16 '16 at 03:37
  • 1
    Relative links can be created by specifying `file://abc.txt` for the path. See also https://forum.openoffice.org/en/forum/viewtopic.php?f=7&t=54592. When a file with such a link is exported as PDF, the link gets converted to an absolute path, using the directory of the file. – Jim K Feb 16 '16 at 03:43
  • Thanks. That worked but we need to figure out how to get it to be placed on the "2". Can it be coded to just add the hyperlink to the text obj rather than the grid square. I tried file:// but we need the files to open in a mobile environment and on multiple system so it needs to open files in the same directory as the index. – GeorgeC Feb 17 '16 at 04:21
  • 1
    Checking "Export URLs relative to file system" makes the links relative on my system. See https://forum.openoffice.org/en/forum/viewtopic.php?f=7&t=67922. Change the code to `args1(1).Value = numText & "|file://" & numText & ".pdf"`. – Jim K Feb 17 '16 at 08:58
  • Great. Any idea on how we can get it to hyperlink the text number rather than the grid. Also how to get it to do 1 to 1000? – GeorgeC Feb 17 '16 at 09:41
  • My index file for testing is in https://www.dropbox.com/s/dh8wmkm577grkjz/index_hotlink.pdf?dl=0 – GeorgeC Feb 17 '16 at 09:46
  • Thanks for the edits. Relative worked on the old code when I put in --args1(1).Value = numText & "|file://" & numText & ".pdf"-- but when I run the new code it creates hyperlinks on the text perfectly but they try to open the browser at "file://1.pdf/" rather than in the folder where the index currently is c:/test – GeorgeC Feb 17 '16 at 12:46
  • Ok, change it to simply `oField.URL = numText & ".pdf"`. Now it opens files in the same folder. If 1.pdf file is not in the folder then it opens an empty page in the web browser. – Jim K Feb 17 '16 at 16:17
  • Thank you for all your efforts on this. So if I have the grids as A1, A2, B1 etc can we do something like characters=[a..z] and then.somehow change SearchString = tryNumText to search for a value in characters followed by a number? and make that a link to map_A1.pdf, map_A2.pdf etc – GeorgeC Feb 17 '16 at 22:44
  • Yes, that should be a simple change. Add an outer loop for the letter. Then do `.SearchString = tryLetter & tryNumText`. – Jim K Feb 18 '16 at 02:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103796/discussion-between-georgec-and-jim-k). – GeorgeC Feb 18 '16 at 06:39