Calc Macro: Paste without prompting

John7856 · Post by **John7856** » Wed Oct 15, 2008 6:47 am

I am moving from MS Office to Open Office so I'm getting a few surprises. Many of them have been postive. OO3 is looking like a great suite. On the downside I have a paste problem.

I copy from another application a 7 column by 2 row set of numbers (tab separated when i paste them into NP++)
In excel I could paste them directly in and used the paste function in the macro.

In calc paste is refused so I have to use paste special and then select unformatted text and then click ok in the next box.
Fine.

The problem is that I've created a macro (just by recording the functions then editing to tidy it up) but the Text Import box comes up outside of the control of the macro which needs user input. The code generating this paste special command is:

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Format"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:PasteSpecial", "", 0, args1())

So, my question is: Is there a cleverer way of pasting the text from the clipboard directly into 7x2 cells without a box requiring user intervention?

(Obviously a clever way to respond to the box from calc would be equally satisfactory - I could catch it with an autohotkey script I run but would prefer to keep it within the generating application if possible.)

Post by **JohnV** » Wed Oct 15, 2008 4:04 pm

Try uno:Paste instead of uno:PasteSpecial.

John7856 · Post by **John7856** » Wed Oct 15, 2008 11:39 pm

Thanks JohnV but that function performs just like a CtrlV paste function and is refused by Calc.

I was hoping for a function that will perform like paste special but doesn't create a user interaction box outside of the macro's control.

Anser · Post by **Anser** » Tue Jan 13, 2009 7:51 am

Dear John7856,

Did you find a solution to this problem.

I am also facing the same problem. I do not want the Dialog box to appear while executing the Macro

Any help ??

Regards

Anser

Post by **FJCC** » Wed Jan 14, 2009 5:17 am

Can you describe the type of data you want to import? I played with this for a bit and found that doing a Paste Special as Formatted Text [RTF] did not bring up a dialog box when running the macro. The macro code was

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Format"
args1(0).Value = 10

dispatcher.executeDispatch(document, ".uno:PasteSpecial", "", 0, args1())

Selecting Unformatted Text did produce a dialog box when running the macro. Of course, this approach may not work for you. I'd be happy to poke at this some more if knew more about your needs.

Anser · Post by **Anser** » Wed Jan 14, 2009 6:55 am

Dear FJCC

Can you describe the type of data you want to import?

I have TAB Delimited Text in the ClipBoard and I need to paste those contents to Calc thru macro. When I execute the Macro a Dialog named "Text Import" will popup and the user will have to click the OK button for the paste to work. My requirement is that I don't want the Dialog to appear and the paste should work withoput any user intervention.

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Format"
args1(0).Value = 10

dispatcher.executeDispatch(document, ".uno:PasteSpecial", "", 0, args1())

I tried the code which you gave but did not work, it is showing the Text Import Dialog

I'd be happy to poke at this some more if knew more about your needs.

Thankyou for your concern

I think the problem is all about the the PropertyValue
If we know the right ProperyValues the system will not ask for the user confirmation thru the Text Import dialog.
To find out the ProperyValues, Macro recording is not possible while we are in a Dialog.

PasteSpecial will show 2 dialogs, whereas Paste shows only 1 dialog

I am struck right now and have spend more than 2 days time just to resove this issue.

Regards

Anser

edit this topic's initial post · Post by **Villeroy** » Wed Jan 14, 2009 12:08 pm

Dispatch a normal paste (Ctrl+V), which should select the target range and then ...

Code: Select all

oView = thisComponent.getCurrentController()
oRange = oView.getSelection()
if oRange.supportsService("com.sun.star.sheet.SheetCellRange") then
with com.sun.star.sheet.CellFlags
  iFlag = .VALUE + .STRING + .DATETIME + .FORMULA + .ANNOTATION
end with
oRange.clearContents(iFlag)
endif

http://api.openoffice.org/docs/common/r ... ation.html
http://api.openoffice.org/docs/common/r ... Flags.html

Anser · Post by **Anser** » Wed Jan 14, 2009 2:35 pm

Dear Villeroy,

Thanks for the Code.

But still the Popup Dialog is asking me to confirm the Import Text Type after the uno:Paste command.

I don't know whether I have succesfully told you my problem.

I have a TAB delimitted text in the clipboard. I want to paste the clipboard content to Calc thru VB/xHarbour code. But after executing the Uno:Paste a Popup Dialog "Text Import" is appearing (means I am loosing control on the code and the user has the power to change formats through the options available in the dialog). I do not want that dialog to appear, instead of that (I beleive) If I knew the right PropertyValue of the uno:Paste, I can avoid/bypass the Popup dialog.

Code: Select all

oBook:CurrentController:select( oSheet:GetCellByPosition( 0,nPasteRow-1 ) )
oDispatcher:ExecuteDispatch(oBook:GetCurrentController():GetFrame(), ".uno:Paste", "", 0, {})
oView  := oBook:getCurrentController()
oRange:= oView:getSelection()
if oRange:supportsService("com.sun.star.sheet.SheetCellRange")

   // iFlag = :VALUE + :STRING + :DATETIME + :FORMULA + :ANNOTATION
   iFlag:= 1+4+2+16+8
   oRange:clearContents(iFlag)
Endif

Any hint?. I have gone mad after trying to get a soulution for this problem for the past 3 days

Regards

Anser

edit this topic's initial post · Post by **Villeroy** » Wed Jan 14, 2009 2:43 pm

Sorry, I got you totaly wrong. It thought you were after a method how to paste formattings from a html-table or some other spreadsheet.
There is a whole API module for clipboard and data transfer: http://api.openoffice.org/docs/common/r ... le-ix.html
Using the usual inspection tools you should find your way through it.

Post by **FJCC** » Thu Jan 15, 2009 4:39 am

I failed. I worked on this for a while last night and today and I can't find a way to avoid the dialog. I can get direct access to the clipboard when in contains tab delimited data, but only as a text string. Code could be written to parse that into cells, but that amounts to recreating the import function and is way more work than seems reasonable. I have never found a list of what PropertyValues are available for what Dispatcher functions, so, though I agree that the right choice might solve the problem, I don't know a way to determine those settings. If someone can provide a specific location for PropertyValues associated with particular Dispatcher calls, that would be great.

So, let's take a step back. Could your problem be solved by by importing the source file into Calc using direct API commands, rather than a recorded macro that uses Dispatcher calls? Even if you don't need the whole file, it might be easier to import it all and filter or sort it in Calc, rather than copy part of it to the clipboard and find a way around the dialog.

Anser · Post by **Anser** » Thu Jan 15, 2009 6:37 am

Dear FJCC,

Thankyou for your effort.

Could your problem be solved by by importing the source file into Calc using direct API commands, rather than a recorded macro that uses Dispatcher calls? Even if you don't need the whole file, it might be easier to import it all and filter or sort it in Calc, rather than copy part of it to the clipboard and find a way around the dialog.

I am not using macro but using Direct API calls from xHarbour+FiveWin. In my application I have a Data Grid/Browser where I have given my users a choice to transfer the Grid/Browser data to MS Excel. I wanted to extend this service to OpenOffice Calc also. The data inside the Grid/Browser sometimes may be huge and after having several tests, I have found that copying the Grid/Browser content to clipboard, and then copying data from clipboard to Calc/Excel makes the transfer much much faster.

As you said, now I have to compromise the speed at which the data get transfered to excel and find out a solution to read the conents of Grid/Browser one by one and write to Calc. I did not expect such a simple paste procedure will be a herculeous task in Calc. I have googled a lot to find a solution and failed. I wonder how OpenOffice macro developers overcome this problem.

Thanks & Regards

Anser

Post by **FJCC** » Thu Jan 15, 2009 7:00 am

I am clearly totally out of my depth on this. I googled xHarbour to try to understand your last post and I failed to understand most of what I read. Anyways, in the proud tradition of the ignorant, I will make a stupid suggestion. Would it work to send your data first to a temporary text or Excel file and then import that into Calc? I'll go away now and stop slowing you down.

Anser · Post by **Anser** » Thu Jan 15, 2009 11:10 am

Dear FJCC,

Would it work to send your data first to a temporary text or Excel file and then import that into Calc?

One of my primary intention to use Calc is to provide an alternative option for my users instead of MS Excel so that they can avoid the licencing costs. To convert data to Excel I use API methods, so needs excel to be installed in the PC. So converting to excel and then to calc is not acceptable.

I have tested the alternative method that is reading from clipboard and then reading the clipboard conents character by character and eliminmating the TAB character and pasting it to each Cell. The problem with this method is that it is a time consuming process (Macro execution time is high), wheras the Tab delimited Paste is very quick.

I googled xHarbour to try to understand your last post and I failed to understand most of what I read.

The API commands are very much similiar to VB API commands the major difference is that Basic MAcro like

Code: Select all

oDispatcher.ExecuteDispatch(oBook.GetCurrentController().GetFrame(), ".uno:Paste", "", 0, {})
oView  = oBook.getCurrentController()
oRange= oView.getSelection()

In xHarbour will look like ( Instead of . in Basic, : is used in xHarbour)

Code: Select all

oDispatcher:ExecuteDispatch(oBook:GetCurrentController():GetFrame(), ".uno:Paste", "", 0, {})
oView  := oBook:getCurrentController()
oRange:= oView:getSelection()

Hope some solution will be there.

Regards

Anser

edit this topic's initial post · Post by **Villeroy** » Thu Jan 15, 2009 11:35 am

I did not expect such a simple paste procedure will be a herculeous task in Calc.

This API is very fine grained. It works with arbitrary programming languages under a wide range of different operating systems and window systems (and their clipbboard implementations).

I am not using macro but using Direct API calls from xHarbour+FiveWin.

I hate those long threads where all the specifics come out during the process. Proprietary soulutions require proprietary software. If your clipboard manager works well with Microsoft's concept of a clipboard you should use their office suite as well.

Anser · Post by **Anser** » Thu Jan 15, 2009 3:17 pm

Dear Villeroy,

I hate those long threads where all the specifics come out during the process. Proprietary soulutions require proprietary software. If your clipboard manager works well with Microsoft's concept of a clipboard you should use their office suite as well.

Initially I test the macro with the OOo Basic in the calc, If it works fine only then I port the same code to xHarbour equivalent code. But in this case even in the OOo Basic code the Popup Dialog "Text Import" is coming up on the screen when I try to paste.

Hope it is clear to you now.

Regards

Anser

Anser · Post by **Anser** » Fri Jan 16, 2009 8:07 pm

Anybody any help ?

Regards

Anser

Anser · Post by **Anser** » Thu Jan 29, 2009 8:48 am

Any help please....

Will a DoImport() serve my purpose. If so what could be the ProperyValue parameters to import a Tab Delimited text from ClipBoard to Calc. After searching a lot I found something similiar

Code: Select all

Set oImport = objSheet.getCellRangebyName("A3")

strExport = "select * from biblio"

importParam(0).Name = "DatabaseName"
importParam(0).Value = "Bibliography"

importParam(1).Name = "SourceType"    
importParam(1).Value = "SQL"              // Can it be TAB Delimited or "OEM/ANSI Text"  ?

importParam(2).Name = "SourceObject"
importParam(2).Value = strExport       // Can it be ClipBoard ?
oImport.doImport (importParam())

Regards

Anser

PWE13 · Post by **PWE13** » Tue Dec 15, 2009 10:41 pm

Hi,

I think I had the same problem as the original poster: How to paste data from the clipboard to calc without a dialog box coming up? After some experimenting I found that the dialog box can be avoided by having the data in the right format on the clipboard. In MS excel, tab and CR/LF separated data is imported without dialog box. In calc, this will popup the dialog box.
However, if the data on the clipboard is tab and CR separated, the paste will not trigger the dialog box.
So instead of CELL1\tCELL2\nCELL3\tCELL4 you should paste CELL1\tCELL2\rCELL3\tCELL4

PS I use a normal paste, not a paste-special

Hope this helps.

srgiri · Post by **srgiri** » Wed May 02, 2012 3:29 pm

Hi Anser,

I think I have found the solution. Please find the following code (in Harbour) useful to you.

Code: Select all

   LOCAL oServiceManager, oDesktop, oDoc, oSheet, oCtrl, oDispatcher
   IF ( oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != NIL
      oDesktop := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )
      oDispatcher = oServiceManager:createInstance ( "com.sun.star.frame.DispatchHelper" )
      oDoc := oDesktop:loadComponentFromURL( "private:factory/scalc", "_blank", 0, {} )
   ELSE
      msginfo( "Error. OpenOffice not available.", win_oleErrorText() )
   ENDIF
   oCtrl := oDoc:getCurrentController()
   oSheet := oDoc:getSheets:getByIndex( 0 )      
   oCell := oSheet:getCellByPosition( 0, 0 )
   oCtrl:Select( oCell )
   system.clipboard := 'Rathinagiri' + chr( 9 ) + '123'
   oDispatcher:executeDispatch( oCtrl, ".uno:Paste", "", 0, { } ) 
      return nil

Anser · Post by **Anser** » Wed Jun 13, 2012 8:18 am

Hi PWE13 and SrGiri,

Thank you for the information.

Yest it is working fine now.

This was a long pending issue for me.

Regards

Anser

jozombi · Post by **jozombi** » Tue Dec 23, 2014 8:56 am

I'm having a similar issue. I am not coming from harbour, or some other API, I am just trying to write macro that pastes the clipboard contents into C2 of a spreadsheet without having to deal with the GUI. What I was trying to do in Calc, is emulate what I used to do in Excel, with Powershell. Excel works nice with PS since both are MS products, but I was unable to directly interact with a spreadsheet in Calc with PS, so the best way I could circumvent the issue is by doing running the following line in powershell:

"$Str `n $Oct" | clip.exe

In PS `n is carriage return, so it pastes as:

$Str
$Oct

The carriage return is what is causing the prompt to come up. With a working macro I want to set it to run when the document opens, so the user doesn't have to do anything.

The problem here is exactly as mine, except the code post here that works is no Basic and I tried making sense of it and trying to translate it, but that was a bust. If anyone could help that would be greatly appreciated.

Post by **Zizi64** » Tue Dec 23, 2014 10:24 am

You can use the macroRecorde, or can write the code manually, based on API function.

Usege of the macroracorder:
https://wiki.openoffice.org/wiki/Docume ... o_recorder

Other threads:
viewtopic.php?f=20&t=10706

viewtopic.php?f=5&t=31798

viewtopic.php?f=20&t=55775

https://www.google.hu/url?sa=t&rct=j&q= ... 63l4tL8tQA

Post by **MTP** » Tue Dec 23, 2014 4:19 pm

You might want to check out Andrew Pitonyak's OpenOffice Macros Explained (free download). One of the chapters explains interacting with the clipboard. This is the first example in the chapter:

Code: Select all

Listing 430. Use the clipboard to copy data between two documents.
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oFrame1 = oDoc1.CurrentController.Frame
' Use the documents controller to select the cells A1:B2.
oSheet = oDoc1.Sheets(0)
rng = oSheet.getCellRangeByName("A1:B2")
oDoc1.CurrentController.Select(rng)
' Use a dispatch to copy to the clipboard.
oDispatcher.executeDispatch(oFrame1, ".uno:Copy", "", 0, Array())
' Upper left corner of where to paste the data.
rng = oDoc2.Sheets(0).getCellRangeByName("A1")
' Place the view cursor there then paste the clipboard.
oDoc2.CurrentController.Select(rng)
oFrame2 = oDoc2.CurrentController.Frame
oDispatcher.executeDispatch(oFrame2, ".uno:Paste", "", 0, Array())

jozombi · Post by **jozombi** » Tue Dec 23, 2014 7:51 pm

Yeah I looked at both of his documents, the OOME(?) and the other one that he has with examples. The issue with it is with the format of the items in the clipboard, or in this case lack of:

123
1.234

Mrprogrammer puts it best in viewtopic.php?f=75&t=60944&p=269742&hil ... es#p269742:

A similar Text Import dialog is used in three situations in addition to Text to Columns in the Data menu.
File → Open → Type → Text CSV
Insert → Sheet from file → Type → Text CSV
Edit → Paste Special → Unformatted text (when the clipboard contains at least two lines of text)

I'm the last one. I'm trying to avoid the prompt or all together select the filters as part of the macro so it doesn't need to ask, it will already know. What I ended up doing as a quick fix for the time being, but not clean fix, was using a portion of the spreadsheet that I designed to have a gray backround, I paste the data from powershell to the clipboard as:

123;1.234

and then I use an if loop to split the data between the cell and the next cell. Since I know which cells have what, I just copy and paste the date to the actual spots I want. This since I'm doing cut/copy and paste, this changed cell formatting so I have to change them back as part of the macro and then delete the temp cells that had the data at first. I notice it because I wrote it, but it runs too quick for others to take note.

Seems to work fine now, but kinda wish there was a cleaner approach. With Excel, I can directly manipulate data from Powershell, but I can't do so with Calc. Then with Excel itself, I was able to do some macros that needed 2 words to do something, whereas now it's 10 lines. I'll have a lot of learning to do.

Post by **MTP** » Tue Dec 23, 2014 8:14 pm

If you use oRange.getDataArray() and oRange.setDataArray you can change cell contents (including cut/copy and paste-like operations) without affecting formatting. Generally it is faster to pull in cell contents with .getDataArray and loop through them within the macro compared to looping through cell selection. You may already be using this: oRange.clearContents(31) is a quick way to delete cell contents without affecting formatting. Using the data arrays may not be completely clean, but maybe an improvement in that it should run slightly faster plus you wouldn't have to fix formatting anymore?

jozombi · Post by **jozombi** » Tue Dec 23, 2014 8:32 pm

Cool I'll give that a try. The area getting the data by the end has no background, since the font is black, it is not noticeable (unless you know to look for it) in the cell I temporarily paste it in since the background is gray. I had tried cutting and pasting, but that also cuts all of the formatting of the cell, copy seems to select only the data and some formatting, but not background fill; still seems to affect borders. I'll give this a try as soon as I can. The quicker the macro runs the better. Even though aesthetics is important, I'm mostly concerned with the user doing something that might interrupt the macro between it starting and it ending, so the quicker it runs, the better both things will fare.

Calc Macro: Paste without prompting

Calc Macro: Paste without prompting

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function

Re: Calc Macro: Paste Function