"Text Import" interrupts macro for settings and stops macro from completing

dscotese · February 4, 2023, 4:44pm

I would like my macro to use the information that I put on my clipboard. The problem is strange because of what does actually happen. Here I will list the steps of the macro and explain how they are handled:

Paste the text on the clipboard into the cells of the sheet. (This works great but it also produces the “Text Import” dialog.)
Copy the cells which already have formulas that find the data I need. (This works, apparently, because see the next step…)
Paste the calculated values into the cells where I need to use them. (This works great, which is weird because the macro does it again for some other values and it seems to have no effect for this second area.)
Perform steps 2 and 3 again with a different area of cells. (This never happens.)

No errors are produced. The code is below…

sub TransferKraken
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim arg1(0) as new com.sun.star.beans.PropertyValue
arg1(0).Name = "Nr"
arg1(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, arg1())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$S$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
args8(0).Value = "$M$23"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
args8(0).Value = "$K$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:PasteOnlyValue", "", 0, Array())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$N$23:$N$30"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
args8(0).Value = "$B$9:$B$16"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:PasteOnlyValue", "", 0, Array())

rem ----------------------------------------------------------------------
args8(0).Value = "$M$24:$M$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
args8(0).Value = "$E$10:$E$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:PasteOnlyValue", "", 0, Array())


end sub

KamilLanda · February 4, 2023, 6:57pm

It is fine you try to use a macros, but the operation you want isn’t so easy for Macro recorder. Can you upload example ODS and write what you need with data? I think it will not complicated to write the macro with API commands for copying values from cells to other ones :-). But it really need example with input data and expected result, the divination from text from Ask isn’t satisfying :-).

dscotese · February 8, 2023, 12:24am

I solved this by alerting the user to paste first (and then exiting) if the the active cell at the beginning of the script is NOT the target cell of the paste using this code:

dim dataCells As Object
dim mainSheet As Object
mainSheet = ThisComponent.Sheets.getByName("Main")
dataCells = mainSheet.getCellRangeByName("$S$3:$U$20")
dim checkMe
dim oConv
checkMe = ThisComponent.currentSelection
oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
  oConv.Address = checkMe.getCellAddress
if not "Main.S3" = oConv.UserInterfaceRepresentation Then
	Msgbox "Looks like you didn't paste first!"
	Exit Sub
EndIf

If there is a function (like MsgBox) which stopped execution and waited for the user to indicate that they finished performing a task the Macro can’t do (and allowed the user to use Calc to do it), that would be very useful for these situations where user interaction is the only way to handle a step in a macro.

KamilLanda · February 8, 2023, 12:21pm

Unfortunately no . It is possible to do some very difficult simulation of this function via keyboard/mouse listeners, but not to continue in macro, but run macro again from start. And it will unrealiable and problematic.

Lupp · February 8, 2023, 4:58pm

Any Basic code originally recorded can be modified using whatever means from Basic or the API.
Information prompting the user can be output, and user input can be accepted using the INPUT() function. Arbitrary interaction with the document would not be sensible.

I rarely use dispatch commands, but now I tried. Result (as I see it):
Oh yes. It happens, but the command .uno.PasteOnlyValues doesn’t treat strings and dates as ‘Values’.
Use this code:

Sub TransferKraken() REM What are "Kraken"?
doc          = ThisComponent
cCtrl        = doc.CurrentController
sheet        = doc.Sheets(2 - 1)
s3Cell       = sheet.getCellRangeByName("S3")
cCtrl.select(s3Cell)

REM Only for the following command there is no simple replacement with API-based
REM code because it would need acces to the formerly copied contents. 
dim document   as object REM Doubtable name for the .Frame of the CurrentController! 
dim dispatcher as object REM Name?
document     = ThisComponent.CurrentController.Frame
dispatcher   = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
REM Dispatcher now no longer needed
document = Nothing : dispatcher = Nothing
REM There arte also API means to access the system clipboard. Too complicated, imo.

m23Cell      = sheet.getCellRangeByName("M23")
m23DA        = m23cell.getDataArray()

k7Cell       = sheet.getCellRangeByName("K7")
k7Cell.setDataArray(m23DA)

firstRgToCopyValuesOnly _
             = sheet.getCellRangeByName("N23:N30")
daToTransfer = firstRgToCopyValuesOnly.getDataArray()
REM N23:N30; 1 column, 8 rows
REM B9:B16 ; size OK; move 12 columns to the left, 14 rows upwards.
offsetable   = sheet.createCursorByRange(firstRgToCopyValuesOnly)
offsetable.gotoOffset(-12, -14)
offsetable.setDataArray(daToTransfer)
secondRgToCopyValuesOnly _
             = sheet.getCellRangeByName("M24:M31")
daToTransfer = secondRgToCopyValuesOnly.getDataArray()
REM M24:M31; 1 column, 8 rows
REM E10:E17 ; size OK; move 8 columns to the left, 14 rows upwards.
offsetable   = sheet.createCursorByRange(secondRgToCopyValuesOnly)
offsetable.gotoOffset(-8, -14)
offsetable.setDataArray(daToTransfer)
End Sub