Insert Sheet by File

PeteS · General User Joined: 12 Feb 2005 Posts: 43

I'd like to know the macro for inserting an external calc worksheet into an existing calc document (by specifying the filename of the external calc worksheet/workbook).

Interestingly, if you right click the worksheet tabs at the bottom of a calc document (ex: 'Sheet1' tab), you should see 'Insert Sheet...'.

If you try to record a macro for 'Insert Sheet...', it REMs out the uno:Insert command. Try it.

Cybb20 · Posted: Mon Mar 14, 2005 9:38 am Post subject:

So what?

Christian
_________________
- Knowledge is Power -

PeteS · General User Joined: 12 Feb 2005 Posts: 43

So what?

I'd like to know the macro for inserting an external calc worksheet into an existing calc document (by specifying the filename of the external calc worksheet/workbook).

So far, I have a solution that links the sheet:

uros · Posted: Wed Mar 16, 2005 3:29 am Post subject:

Hi PeteS!
Try this macro. It copy sheet "Sheet1" from sUrlMask file to sUrlTemp file.

hazabaral · Power User Joined: 16 Apr 2008 Posts: 69

Sub CopySheetToOtherDocument
Dim sPath as String
Dim sUrlTemp as String
Dim sUrlMask as String
Dim oDesktop
Dim oDocument
Dim oDocument2

Dim oMaskSheet
Dim oDoc2Frame
Dim oPrgSheet

Dim mNoArgs()
sPath = "file:///D:/Data/SIMPROJECTS/CALIBRATION_FINAL/"
sUrlTemp = "aaa.ods"
sUrlMask = "A5.ods"

oDesktop = createUnoService("com.sun.star.frame.Desktop")
oDocument = oDesktop.loadComponentFromURL(sPath & sUrlTemp,"_default",0,mNoArgs())
oDocument2 = oDesktop.loadComponentFromURL(sPath & sUrlMask,"_default",0,mNoArgs())

oMaskSheet = oDocument2.Sheets().getByName("Sheet1")
oDocument2.getCurrentController.select(oMaskSheet)
oDoc2Frame = oDesktop.ActiveFrame

dim document As Object, dispatcher As Object
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "DocName"
args1(0).Value = sUrlTemp
args1(1).Name = "Index"
args1(1).Value = 65535
args1(2).Name = "Copy"
args1(2).Value = True
dispatcher.executeDispatch(oDoc2Frame, ".uno:Move", "", 0, args1())

oPrgSheet = oDocument.Sheets().getByName("ppp")
oDocument.getCurrentController.select(oPrgSheet)

oDocument2.dispose
End Sub

Hi
I worked on your code, but it does not copy the content of the sheet from A5.ods to aaa.ods.
May be I have done some mistake, someone please check and advise .

arivas_2005 · Posted: Thu Apr 24, 2008 7:15 pm Post subject:

hi
Excuse my english

I use a form like this to copy leaves of other archives
I only open a file
I insert a Link of the Sheet of the other file
I make visible the sheet link and I copy it with another name
I delete the sheet link

Code:

Sub CopySheetfFromOtherDocument
'
Dim mNoArgs()
sPath = "file:///C:/intranet/test/"
sUrlTemp = "Nar.sdc"
sUrlMask = "qq.sxc"
'Convert the path to URL OObasic
sUrlTempall=ConvertToURL(sPath & sUrlTemp)
sUrlMaskall=ConvertToURL(sPath & sUrlMask)

REM Only Open 1 file
   oDesktop = createUnoService("com.sun.star.frame.Desktop")
   oDocument = oDesktop.loadComponentFromURL(sUrlTempall,"_default",0,mNoArgs())

REM I use the link sheets.

   mySheetToCopy="='"+sUrlMaskall+"'#$Sheet1.A1"
oDocument.Sheets.getByName("Sheet1").getCellByposition(0,0).setFormula(mySheetToCopy)
REM Count Sheets for next process
CountSheets=oDocument.getSheets().count
REM make the copy of sheet linked


   for tosheet=1 to CountSheets

      sheetpresent=oDocument.Sheets(tosheet-1).Name
      if instr(sheetpresent,"file")>0 then
         REM forgot the sheet of the link
         if oDocument.getSheets().getByName(sheetpresent).IsVisible="False" then
            oDocument.getSheets().getByName(sheetpresent).IsVisible="True"
         endif
         REM make the copy
         oDocument.Sheets().copyByName(sheetpresent,"MySheet1Copy",0)
         REM delete the sheet link
         oDocument.Sheets().removeByName(sheetpresent)
         exit for
      endif
      next tosheet
      REM clean the cell A1
oDocument.Sheets.getByName("Sheet1").getCellByposition(0,0).setString("")
End Sub

I hope to help !

digitaltouchit · Newbie Joined: 10 Aug 2009 Posts: 4

Hi,

unforunately the macro doesn't work for me. I think that the problem could be in the name of files that I use.

I need to put in the principal file that I called "peter.ods" after the sheet that I called "thunder"

a new sheet from file

the file name is "peter2.ods" and the sheet that i would like to import in the principal file is "thunder2".

Can you compile the macro (that is upside my post) with this names? I don't know where I should put the name of my files inside the macro that you've built.

thanx a lot to everybody!!!
Very Happy

idankek · Newbie Joined: 22 Mar 2013 Posts: 4

idankek · Newbie Joined: 22 Mar 2013 Posts: 4