Choose file in same folder, update links DDE

[Adek1243]

Hello. Im from poland so in first sorry for my english language.
I have question. I want will be printing with Calc document a someone Writer document which will be in same folder. I will be want to doing makro which will be open a Writer document with update links DDE, printing it (with my name printer) double-sided printing and save and close document. I trying did this macro, but dont know how to doing it:
1) choose file in my folder without all file path.
2) update links DDE
3) double-sided printing option

This is my code:

Code: Select all

Sub PrintADocument()
  oDesk = createUnoService("com.sun.star.frame.Desktop")


  fileName = "file:\\document.odt" '< how to doing a take file with same folder and  update DDE link'


  Dim args0(1) as new com.sun.star.beans.PropertyValue
  oDoc = oDesk.LoadComponentFromURL(fileName, "RootFrame", 255, args0())
  oDoc.currentController.Frame.ComponentWindow.setVisible(false)

Sub oPrintFileName
   Dim oProps(0) as new com.sun.star.beans.PropertyValue
      oProps(0).Name = "Name" '< it will be good?'
      ThisComponent.print(oProps())
End Sub

  oDoc.Print(oProps()) '< how to doing a double sided print'



  wait(200) '<how to save file before close'
  oDoc.close(false)
End Sub

And have question. Can i doing in macro that when will be in Calc for example in A2 number 0 so it will be scratch off in Writer for example text "Today", but when will be number 1 will be scratch off text "Tommorow". Thanks for answer so much friend. Healthy!

[Lupp]

You may try the attached example containing code.
It was developed with LibreOffice V 6.4.2, but also tested partly with AOO V 4.1.5.

I don't think there is a way to update only DDE links.
Documents from imported MS Word files may have issues. I also don't think they can come with working DDE.

batchAndDdeSourceExample.ods

(13.48 KiB) Downloaded 166 times

[/strike](Don't use. Containing errors.)

===Editing 2020-03-20 about 11:40 UTC===
Due to the answer below I came back to the thread, and found the code contained in the already attached file not working correctly when tested again. I made a few improvements to it and attach the reworked version now.
(Generally I had previously used spreadsheets+macros for a kind of batch processing successfully, but the usage of loadArgs() and printArgs() was new in this case - and not sufficiently analyzed and tested. Hopefully the issue is over now.)

batchAndDdeSourceExample_2.ods

(13.67 KiB) Downloaded 168 times

[Adek1243]

Thanks. I will be try in my home. But this dokument isnt imported with MS Word. It's Dde link with Calc to oryginal Writer Libra Office doccument.

[Lupp]

Please regard my editing of the above answer.

[Adek1243]

Hello. I come back to try doing it.

I have problem, becouse dont know what i must writeing in A1 when is "FOLDERPATH_or_FolderURL" i trying something writeing and dont work.
But when i will be want start macro it seeing a folder with for example f1.odt but dont wont open it and have a error:

Code: Select all

BASIC language startup error.
An exception occurred
Type: com.sun.star.lang.IllegalArgumentException
Message: Unsupported URL <file: /// C: / Users / dyzur / Dyzur Szybkie />: "type detection failed".

and it stop in code:

Code: Select all

   r_doc = StarDesktop.loadComponentFromUrl(r_fiUrl, "_blank", 0, loadArgs())

And now dont know what i must doing Thanks for help.

[Villeroy]

This office suite is perfectly well usable without macro programming.
If you can not program, there are two options:
1. Use the built-in dialog Edit>Links...
2. Do a mail merge. Connect a database document to the spreadsheet and replace DDE links with database fields.

[Lupp]

...what i must writeing in A1 when is "FOLDERPATH_or_FolderURL"...

The default folder is the one you keep the 'batchAndDdeSourceExample_2.ods' in. Column A may be used to override this default, but I didn't thoroughly check and test this facility. Usage not recommended - except you did the check.

The first sheet 'Batch' acts as a container for files (identified by names) to be processed by the included code.
The column F there receives a new date-time-stamp if the respective file actually existed and was processed.
"f1.odt" and "f2.doc" are placeholders for two of your files you want to process, and which you need to move to the same folder as you keep the batchAndDdeSourceExample_2.ods I provided in. You also need to enter their actual names then contiguously into column B (and an existing filtername into column C if needed).

When testing on my system the files were very banal examples I had created for the testing only. The 'f2.doc' was derived from 'f1.odt' by saving to the alien fileformat and only for proving absent the capability of .doc to store working DDE links - at least when exported from LibO. (I have no native Word files at my disposition.)

Also only for testing I created the second sheet 'ExampleDDElinkedTable' containing the source for a DEE link only used for a linked TextTable in f1.odt.

When I developed and tested, no errors occurred. The existence of any given file is checked by the code before trying to open it. I don't understand the error message you reported, and you missed to tell for what line of Basic code it was thrown. The line of code (line 55) you quoted and reported to stop execution is under control of line 44 testing for the existence. This seems inconsistent to my understanding, and I'm afraid I cannot clear this up without sitting next to you at your computer.

My advice can only be: Execute the code stepwise, take the opportunity to understand it, and check specifically how you can get to line 55 with a nonexisting file despite line 44.

Though its only an ugly toy, I attach my experimental f1.odt now. Remember that it must be placed into the same folder as the 'batchAndDdeSourceExample_2.ods'. First of all make sure that you don't use the older faulty 'batchAndDdeSourceExample.ods'.

If there is another problem, please don't wait a week or more to report it. Needing to understand my sparsely commented code from 10 days ago again takes me quite a time. If you are decisive to do such things by macros, you anyway need to develop your debugging skills.

Now the banal file for testing. Users understanding the code and being aware of the nature of this file don't need to download it. They should also delete the second sheet from my .ods.

f1.odt

(10.92 KiB) Downloaded 188 times

Edit:  Insert Code: Select all If NOT (Right(r_foUrl, 1)="/") Then r_foUrl = r_foUrl & "/" as the new line 42 into the code (subsequent lines get their numbers increased by 1 then) - or assure you will give folder paths with a terminating slash (for Win paths: backslash). The original question anticipated the default folder to be mandatory. I don't like unnecessarily specialized code, however, and therefore wanted to provide a way to work with different folders. Sorry! I didn't test the facility.

[Adek1243]

Still is same..
See it, it :

Code: Select all

      If NOT (Right(r_foUrl, 1)="/") Then r_foUrl = r_foUrl & "/"

must be here?


And all configuration:






@@@@@@@@@@@@@@@EDIT
When i took this

Code: Select all

      If NOT (Right(r_foUrl, 1)="/") Then r_foUrl = r_foUrl & "/"

on 42 into the code
i now cant end complication

Code: Select all

BASIC syntax error.
Unexpected symbol: r

[Lupp]

Hopefully you won't expect me to study these squaremiles of images.
I only can tell you:

-1- What your first image from the Basic IDE shows as Line 44 is line 42 for me (and under this number I also described it in my previoius post).
-2- The next image then shows line 56 which is the line number I also have.
-3- The images mentioned under -1- and -2- are in conflict therefore.
-4- And running the code as I suggested it you wouldn't reach the line mentioned under -1- at all if not a folderpath was given in column A.
-5- The image taken from the sheet itself shows a 1 in cell D5 where it obviously is displaced.

If you attach the files you are actually using I may find the time to look at them.

Otherwise we should close this discussion. It's starting to become a waste of time and resources. Factually you cannot use "macros" beyond the 2-liners without a basic knowledge concerning programming and debugging. Things worked for me as decribed. I cannot supply line-by-line support from here to there this way.

Please don't again attach images where the issue not explicitly is the rendering.

[Adek1243]

This is my file, what i want to print with excel.
But in first i will be want to print your document to test, but it dont work.

Hopefully you won't expect me to study these squaremiles of images.
I only can tell you:

-1- What your first image from the Basic IDE shows as Line 44 is line 42 for me (and under this number I also described it in my previoius post).
-2- The next image then shows line 56 which is the line number I also have.
-3- The images mentioned under -1- and -2- are in conflict therefore.
.

and yes, 1,2,3 photo is bad, when i change 42 is okay, but now i have a error with 73 lines

Code now:

Code: Select all

REM  *****  BASIC  *****

Private Const defaultRangeName = "A1:F1001"
Private loadArgs(1) As New com.sun.star.beans.PropertyValue
Private printArgs(0) As New com.sun.star.beans.PropertyValue

Sub processBatch(Optional pSheet As Object, Optional pRangeName As String)
If IsMissing(pSheet) Then
  bDoc   = ThisComponent
  bSheet = bDoc.Sheets.getByName("Batch")
Else
  bSheet = pSheet
  bDoc   = bSheet.DrawPage.Forms.Parent
End If
If IsMissing(pRangeName) Then
  bRangeName = defaultRangeName
Else
  bRangeName = pRangeName
End If
bRange = bSheet.getCellRangeByName(bRangeName) REM Row=0 containing the labels
With bRange.RangeAddress
  processCodeRange = bRange.getCellRangeByPosition(3, 1, 3, .EndRow - .StartRow)
  filled = processCodeRange.queryContentCells(1).getByIndex(0)
  lRange = bRange.getCellRangeByPosition(0, 0, .EndColumn - .StartColumn, 0)
  bRange = bRange.getCellRangeByPosition(0, 1, .EndColumn - .StartColumn, filled.RangeAddress.EndRow - .StartRow)
  uR = .EndRow - .StartRow
  dtRange = bRange.getCellRangeByPosition(5, 0, 5, uR)
  bRange  = bRange.getCellRangeByPosition(0, 0, 4, uR)
End With
batchData = bRange.GetDataArray
splUrl = Split(bDoc.Url, "/")
uSplUrl = Ubound(splUrl)
batchFileName = splUrl(uSplUrl)
batchFolderUrl = Left(bDoc.Url, Len(bDoc.Url) - Len(batchFileName))
For r = 0 To uR
  If batchData(r)(3)=1 Then
    r_foUrl = batchData(r)(0)
    If r_foUrl="" Then
      r_foUrl = batchFolderUrl
    Else
      r_foUrl = ConvertToUrl(r_foUrl)
      If NOT (Right(r_foUrl, 1)="/") Then r_foUrl = r_foUrl & "/"
    r_fiUrl = r_foUrl & batchData(r)(1)
    If FileExists(r_fiUrl) Then
      r_FilterName = batchData(r)(2)
      loadargs(0).Name  = "Hidden"
      loadArgs(0).Value = True
      If ((r_FilterName<>"") AND (r_FilterName<>"auto")) Then
        loadargs(1).Name  = "FilterName"
        loadArgs(1).Value = r_FilterName
      Else
        loadargs(1).Name  = "FilterName"
        loadArgs(1).Value = ""'r_FilterName
      End If
      r_doc = StarDesktop.loadComponentFromUrl(r_fiUrl, "_blank", 0, loadArgs())
      r_doc.updateLinks() REM Don't know a way to control this more detailed - except for spreadsheet documents.
'      r_doc.CurrentController.Frame.ContainerWindow.setVisible(True)
REM If you want to see the document
	  r_doc.Store
	  printArgs(0).Name = "DuplexMode"
	  printArgs(0).Name = "CopyCount"
	  r_doc.Print(printArgs)
	  ' REM Comment out during tests to not waste too much paper.
REM Via printArgs you can choose a printer (queue/driver) different from the standard printer if there any exist
REM or set different properties (first three) for the standard printer probably.
REM See https://www.openoffice.org/api/docs/common/ref/com/sun/star/view/PrinterDescriptor.html
REM or https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1view_1_1PrinterDescriptor.html
      dtRange.getCellByPosition(0, r).String = enhancedNowString()
      REM Provided as string to avoid silly misformatting.
      r_doc.Close(True)
    End If
  End If
Next r
End Sub

Code: Select all

Next r

here is error

[Adek1243]

I try doing that it work it. Now i doing that it work it. I only write yet one "End if" on end of code, but now after compliacte doing nothing ;(

[Villeroy]

Code: Select all

MID(CELL("filename");2;SEARCH("/[^/]+'#";CELL("filename"))-1)

returns the current path including the trailing slash if regular expressions are enabled in this file's calculation options (and if the file is saved, of course).

Code: Select all

=DDE("soffice";MID(CELL("filename");2;SEARCH("/[^/]+'#";CELL("filename"))-1)&"FileName.ods";"Sheet1.A1")

returns the value from FileName.ods#Sheet1.A1 with FileName.ods being in the same folder as this file.

You may define the path function as a named expression via Insert>Names>Define... [Ctrl+F3] and reduce the formula to

Code: Select all

=DDE("soffice";PathURL&"FileName.ods";"Sheet1.A1")

where PathURL is the name of the expression MID(CELL(...etc...)).