Select sheet/cells and copy from another Calc file

[komododragon]

Hi,

I need a little help with a copy/paste macro that I tried to record. I've tried to look it up in all available resources, but all I found was how to open a file, not select and copy data from another file.
So, here is what the macro should do:
1. Go to the file d:\docs\source.ods
This file was open when I recorded
2. Go to Sheet 4
3. Select cell range $A$1:$BN$1500
4. Copy
5. Go back to the file from which I recorded the macro (d:\docs\target31.ods).
6. Go to sheet 2 and cell $D$1
7. Here I performed a paste special to add only the results from the formulas in source.ods, and with no formatting.

Nothing was recorded before point 6.

Code: Select all

sub CopyToSheet2
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 args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 2

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$B$1"

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

rem ----------------------------------------------------------------------
dim args3(5) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Flags"
args3(0).Value = "SVDN"
args3(1).Name = "FormulaCommand"
args3(1).Value = 0
args3(2).Name = "SkipEmptyCells"
args3(2).Value = false
args3(3).Name = "Transpose"
args3(3).Value = false
args3(4).Name = "AsLink"
args3(4).Value = false
args3(5).Name = "MoveMode"
args3(5).Value = 4

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


end sub

Can this be done in a macro? If so, how?
Regards.

[pitonyak]

Using two documents complicates things. Notice the trickery in the following macro that copies an entire sheet between two documents:

Code: Select all

Sub CopySpreadsheet
  Dim doc1
  Dim doc2
  doc1 = ThisComponent
  selectSheetByName(doc1, "Sheet2")
  dispatchURL(doc1,".uno:SelectAll")
  dispatchURL(doc1,".uno:Copy")
  doc2 = StarDesktop.loadComponentFromUrl("private:factory/scalc" , _
                      "_blank",0,dimArray())
  doc2.getSheets().insertNewByName("inserted",0)
  selectSheetByName(doc2, "inserted")
  dispatchURL(doc2,".uno:Paste")
End Sub

Sub selectSheetByName(oDoc, sheetName)
  oDoc.getCurrentController.select(oDoc.getSheets().getByName(sheetName))
End Sub

Sub dispatchURL(oDoc, aURL)
  Dim noProps()
  Dim URL As New com.sun.star.util.URL
  Dim frame
  Dim transf
  Dim disp

  frame = oDoc.getCurrentController().getFrame()
  URL.Complete = aURL
  transf = createUnoService("com.sun.star.util.URLTransformer")
  transf.parseStrict(URL)

  disp = frame.queryDispatch(URL, "", _
            com.sun.star.frame.FrameSearchFlag.SELF _
         OR com.sun.star.frame.FrameSearchFlag.CHILDREN)
  disp.dispatch(URL, noProps())
End Sub

First, you must be able to identify the different documents. You may need to search for the documents by name / URL through the loaded components, or load the document to obtain a reference to them.

Next, because you are using dispatches, you must be certain to send the dispatch to the correct document.

Does this at least get you started?

I think that you will not be able to record this macro...

[shardalule]

As,this Macro save the whole sheet into the other sheet.I needed same macro but it should copy only the column from the main file how to do that?? please help...