[Solved] Macro for open, copy, and special paste

[IamaOOuser]

I am a Mac user with system 10.6.8 (Snow Leopard) and running OpenOffice 3.3.0.

I have a multi-sheet Calc spreadsheet template that uses data from a column on one of the sheets. The source of the data is a text file that I download from an internet source and store in my Downloads folder. The text file is a list of numbers and text, separated by returns (no tabs or commas). To get the data into my spreadsheet I do the following steps:

1. File menu, Open
2. Navigate to my Downloads folder, select it from the list in the Open window and then click the open button. This brings up the ASCII Filter Options window with the settings: Character Set = Unicode (UTF-8), Default font = Times New Roman, Language = English (USA), and Paragraph break - CR&LF. I don't change any of these settings and click OK.

3. Once the text file is opened by Writer, I Select All, Copy, and Close Window (using keyboard commands).
I now have my data on the clipboard and I have my blank spreadsheet open with the cell selected, waiting for my PasteSpecial command.

4. I give the Paste Special command which brings up the Paste Special window. In the Paste Special window I select Unformatted text and click the OK button.

5. The Text Import window now appears with the following settings: Character Set = Unicode, Language = Default English (USA), From row=1, Separation Options = Separated by has NO boxes checked; Other options = Quoted field as text is checked. I don't change any of those settings EXCEPT for the Fields section, where I click the column header in the preview and then change the Column type to US English.

I use this spreadsheet a lot and can do these steps easily but I have friends who also use this spreadsheet and remembering all these steps is a little overwhelming for some of them. I would like to add a push button to my spreadsheet that would run a macro that does most of the steps listed above.

Once the push button is clicked, the Open Window would appear, the macro would pause and wait for me to select my text file and would resume when I click the Open button. The macros would copy all the data in the text file to the clipboard and then use the Paste Special command with the column type as US English and paste the data into my spreadsheet at the selected cell location ($C$12).

I have no programming experience so the most I've been able to do is add two push buttons the call up the Open window and the Paste Special window (using uno:Open and uno:PasteSpecial in the URL fields). I've also tried recording a macro as I've gone through the various steps but the recorded macro doesn't do anymore than what I've been able to do with the uno:Open and uno:PasteSpecial commands in my push button setup.

Any assistance in creating such a macro and explaining how to run the macro using a push button on the spreadsheet would be greatly appreciated.

Thanks,
Ron

[kingfisher]

I have been using code for several years which opens a .csv file I have downloaded and copies data from it to at least one spreadsheet file. Please excuse my memory lapse. The data ends up in two spreadsheet documents and I would have to read through the code to see how it gets to each.

In my case, the downloaded file always has the same name and path; the script saves a copy of each downloaded file to another location with a name which includes the date. I overwrite the original .csv file when I download a file with the same name the next business day.

It sounds as though you want to use what is called a file picker. A good place to start would be Andrew Pitonyak's documents. I suggest you download the pdf versions.

[rudolfo]

I am not sure if I oversimplify this. But I don't see any point in opening the file first with Writer (in ASCII import mode). If you simply open the file in Notepad select all text and copy it to the clipboard (Ctrl-A followed by Ctrl-C) you have the plain text without formatting in the clipboard, and Ctrl-V or Ctrl-Shift-V for pasting and pasting special would be just the same. Of course this is from a MS Windows point of view, but I am sure there is something similar to notepad on the Mac, as well.
But after all this copy and paste (special) operations are not needed at all. You can simply move your downloaded csv file (yes, it is a csv file although csv files usually have more than only one field per line) to a dedicated directory and create a Base (.odb) file to link all the csv like files in that directory to OpenOffice. In general when you create the .odb file with the "New Database" wizard you will choose "Connect to an existing database" and "Text" and specify the directory where your downloaded files are located (or copied to). The additional settings like string delimiter or field separator are similar to what you have to specify in the import dialog. Loading CSV into preformatted spreadsheets has a detailed example for this in section 3 about the read-only csv databases. Make sure you read also about what you can do with ... registered datasources in Calc.

[pitonyak]

Do you desire to select the file in a macro?

http://user.services.openoffice.org/en/ ... =20&t=7407

As already stated, you are probably better served to load the file directly into a Calc document as a CSV file. I have numerous detailed examples of this in AndrewBase.odt available on my web site. I don't remember if I do this in OOME (in the Calc chapter).Oh, and there are also numerous examples of selecting a file using a file picker in AndrewBase.odt as well.

Is this enough of a hint to get you started?

[IamaOOuser]

I've taken some of the advice that was offered, done some reading, and made a little progress.

My spreadsheet now has two push buttons and each button is tied to a macro.

The first button/macro brings up the "Open" window so I can select the text file that contains the data I want to import. Once I've selected the text file and clicked the open button, the macro opens the text file (in Writer), selects all the data, copies it to the clipboard, closes the text file, and displays a message that the data now resides on the clipboard.

This macro works fine as long as the macro is global (resides in My Macros library). I want to embed the macro in the spreadsheet so it will be available to other users of the spreadsheet (template). If I embed it in the spreadsheet the text file opens but the macros stops there and I have to do the select all, copy, and close the window steps manually.

Any suggestions as to how I can make this macro work when embedded in the spreadsheet would be greatly appreciated.

Ron

rem define variables
dim document as object
dim dispatcher as object

rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dispatcher.executeDispatch(document, ".uno:Open", "", 0, Array())

document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

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

dispatcher.executeDispatch(document, ".uno:CloseWin", "", 0, Array())

MsgBox "The data was copied to the clipboard"]

[pitonyak]

Having a little trouble understanding the problem, but, I think that it is because everything is done off of "thiscomponent". For example, Assume that document A is open and you run this macro. ThisComponent refers to document A, and it will as long as the macro runs, it will NOT change to reference another document while the macro is running (at least that is my understanding). If this works as a Global macro, then I might be wrong. For sure, however, if your macro is contained in document A, then it is true.

the "document" variable refers to document A.

What does this do? Does this open a dialog for you to choose the file and then open it? I have a comment, on this, but I will come back to it if I do not run out of time (someone is coming to get me).

Code: Select all

dispatcher.executeDispatch(document, ".uno:Open", "", 0, Array())

At this point, you reset "document". This is WRONG for sure if you are inside document A. Assuming that you want to do something in document B, then you cannot use "ThisComponent".

Code: Select all

document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

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

dispatcher.executeDispatch(document, ".uno:CloseWin", "", 0, Array())

I expected to see something closer to this (assuming I understand):

First, select the file to open using something like what is shown here:

http://forum.openoffice.org/en/forum/vi ... 25&t=36441

When you have the URL to the file you desire to open, open the file and maintain a reference to the document..... Something like this:

Code: Select all

oDocB = Stardesktop.loadComponentFromURL(url,"_blank",0,Array())
document = oDocB.CurrentController.Frame

And then your dispatches should be directed to the newly opened document.

Hope this helps some!

[rudolfo]

This is so terribly difficult because it is not the way how it is meant to be done. If the initial plain text files are properly formatted csv files you can directly import them into Calc. After all copying them into Writer is only a temporary step (Writer will be closed again and will forget anything it knows about the text once it is pasted into the spreadsheet). Why the heck do all people think they have to use macros if they want to automate something? Please, do yourself a favor and read the two tutorials Loading CSV into preformatted spreadsheets (section 3 about the read-only csv databases) and ... registered datasources in Calc.

OpenOffice macro programming is difficult, but in this case I would wish that it would be even more difficult. To finally tell those who try to do it with macros that they are on the wrong track.

[IamaOOuser]

On a scale of 1 to 10, what I know about programming is obviously 0.00001 or less. That said, here is the progress I've made so far.

It works, except the section in remarks 11-14. I don't know how to write the dispatcher statement so it sees both the paste special args2 and the text import args4. Currently, it handles the args from the paste special dialog (because I've only included args2 in my dispatcher statement) and I must deal manually with the setting in the text import dialog.

Thanks for your comments and help.
Ron

Sub Main
rem define variables
dim document as object
dim dispatcher as object

rem 1. Make spreadsheet this component
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem 2. Allow user to select the text file to import
dispatcher.executeDispatch(document, ".uno:Open", "", 0, Array())

rem 3. User manually dismisses the ASCII Filter Options dialog
rem 4. Text file opened by Writer - need to make it this component
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem 5. Automate the selection and copying of the data
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:CloseWin", "", 0, Array())

rem 5. Text file is closed
rem 6. Use msgbox to bring focus back to spreadsheet
Msgbox "Click the OK button to continue"

rem 7. closing the messagebox made Calc spreadsheet the active document again
rem 8. make spreadsheet this component
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem 9. moves cursor to the proper cell for pasting
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$12"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem 10. set the paste special text format to unformatted text
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Format"
args2(0).Value = 1

rem 11. PasteSpecial has two dialog windows in OO3.4.1
rem 12. The second dialog is Text Import
rem 13. The args for Text Import should be something like
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "FilterName"
args4(0).Value = "scalc: Text - txt - csv (StarCalc)"
args4(1).Name = "FilterOptions"
args4(1).Value = "44,34,76,1,1/10,false,false"

rem 14. I don't know how to write the dispatcher to include ags2 and ags4
dispatcher.executeDispatch(document, ".uno:PasteSpecial", "", 0, args2())

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$I$13"

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

End Sub

[pitonyak]

Is the file always the same? Specifically, is each column always the same format so that if you specify the column formats in a macro (such as column 1 is always a date, column 2 is always text, column 3 is always a number.....) that format will work with every file you will import?

[IamaOOuser]

The data from the text file is pasted into a single column on the spreadsheet. There is only one column.

The data is made up of numbers, text, and dates. The first item is a number, the 126th item might be a date, the 283rd item in the list could be text.

The regular paste command causes the cells to become protected and dates in the data change from 12/16/12 to '12/16/12 so the spreadsheet doesn't process them as date values. The paste special command along with the column type designation US English solves the date issue, keeps the cells unprotected and preserves the formatting (appearance) of the data column on the spreadsheet.

Ron

[Villeroy]

There is only one column.

One column of mixed data? Are sure that you don't mix columns and rows?
Are you really, really sure that you need a complicated spreadsheet program to work with small data snippets in text files? You do not understand availlable data types, thus you do not specify appropriate import options.
Your recorded macro selects the entire source sheet. Then you try to paste that huge rectangle to C12 which can not work. Just try it manually with any empty spreadsheet. A whole sheet can be pasted to A1 only.

[IamaOOuser]

I solved my problems by going back to a normal paste and then added to the macro to correct the issues that made me choose paste special in the first place.

Thanks for everyone's help and time.
Ron