OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Insert Sheet by File

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
PeteS
General User
General User


Joined: 12 Feb 2005
Posts: 43

PostPosted: Sat Mar 12, 2005 3:57 pm    Post subject: Insert Sheet by File Reply with quote

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.
Back to top
View user's profile Send private message
Cybb20
Super User
Super User


Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany

PostPosted: Mon Mar 14, 2005 9:38 am    Post subject: Reply with quote

So what?

Christian
_________________
- Knowledge is Power -
Back to top
View user's profile Send private message Send e-mail
PeteS
General User
General User


Joined: 12 Feb 2005
Posts: 43

PostPosted: Tue Mar 15, 2005 5:57 am    Post subject: Reply with quote

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:
Code:

Sub ImportSheet
   ' would you like to update link? YES NO   
   Dim filterNames(1) As String
   filterNames(0) = "*.sxc"
   filterNames(1) = "*.txt"
   x = GetAFileName(filterNames())
   If x = "" Then Exit Sub

   dim document   as object
   dim dispatcher as object
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dim args1(1) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "Name"
   args1(0).Value = "Untitled"
   args1(1).Name = "Index"
   args1(1).Value = 0
   dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args1())

   Sheet = ThisComponent.CurrentController.getActiveSheet
      sURL = x
      sFonte = ""
      sFilter = "StarOffice XML (Calc)"
      sOpt = ""
      iMode = com.sun.star.sheet.SheetLinkMode.NORMAL
      Sheet.link(sURL, sFonte, sFilter, sOpt, iMode)
End Sub


The problem with this is that I get the following message box question whenever I load up my main calc file: 'This file contains links to other files. Should they be updated?' [Yes / No]

This is something I do not want. Is there a way to insert a external sheet (file) into a calc document without linking?
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Wed Mar 16, 2005 3:29 am    Post subject: Reply with quote

Hi PeteS!
Try this macro. It copy sheet "Sheet1" from sUrlMask file to sUrlTemp file.
Code:
Sub CopySheetToOtherDocument
   Dim mNoArgs()
   sPath = "file:///C:/intranet/test/"
   sUrlTemp = "Nar.sdc"
   sUrlMask = "qq.sxc"
   
   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(oMaskaSheet)
   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("Sheet1")
   oDocument.getCurrentController.select(oPrgSheet)

   oDocument2.dispose
End Sub

Hope this helps!
Uros
Back to top
View user's profile Send private message
hazabaral
Power User
Power User


Joined: 16 Apr 2008
Posts: 69

PostPosted: Thu Apr 24, 2008 6:44 am    Post subject: Reply with quote

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 .


Last edited by hazabaral on Fri Apr 25, 2008 12:39 am; edited 1 time in total
Back to top
View user's profile Send private message
arivas_2005
Power User
Power User


Joined: 10 Oct 2006
Posts: 98
Location: El Salvador CentroAm

PostPosted: Thu Apr 24, 2008 7:15 pm    Post subject: Reply with quote

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 !
Back to top
View user's profile Send private message
digitaltouchit
Newbie
Newbie


Joined: 10 Aug 2009
Posts: 4

PostPosted: Mon Aug 10, 2009 8:35 am    Post subject: help! Reply with quote

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
Back to top
View user's profile Send private message
idankek
Newbie
Newbie


Joined: 22 Mar 2013
Posts: 4

PostPosted: Fri Mar 22, 2013 10:30 am    Post subject: Reply with quote

uros wrote:
Hi PeteS!
Try this macro. It copy sheet "Sheet1" from sUrlMask file to sUrlTemp file.
Code:
Sub CopySheetToOtherDocument
   Dim mNoArgs()
   sPath = "file:///C:/intranet/test/"
   sUrlTemp = "Nar.sdc"
   sUrlMask = "qq.sxc"
   
   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(oMaskaSheet)
   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("Sheet1")
   oDocument.getCurrentController.select(oPrgSheet)

   oDocument2.dispose
End Sub

Hope this helps!
Uros
Back to top
View user's profile Send private message
idankek
Newbie
Newbie


Joined: 22 Mar 2013
Posts: 4

PostPosted: Fri Mar 22, 2013 10:36 am    Post subject: Reply with quote

uros wrote:
Hi PeteS!
Try this macro. It copy sheet "Sheet1" from sUrlMask file to sUrlTemp file.
Code:
Sub CopySheetToOtherDocument
   Dim mNoArgs()
   sPath = "file:///C:/intranet/test/"
   sUrlTemp = "Nar.sdc"
   sUrlMask = "qq.sxc"
   
   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(oMaskaSheet)
   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("Sheet1")
   oDocument.getCurrentController.select(oPrgSheet)

   oDocument2.dispose
End Sub

Hope this helps!
Uros


Hey uros,
I need to do similar functionality with my code, I need to import a csv file as a new sheet into my current document, I think the program that you wrote does the same thing. However, I get an error on the following line:
oMaskSheet = oDocument2.Sheets().getByName("OPC_Signals_Edit") I am not sure what name I should put in the qutation marks.

Thanks in advance.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group