SHARE
TWEET

copy/paste import

a guest Dec 5th, 2013 577 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     Sub Main
  2.             dim thisDoc as object
  3.             dim thisUrl as string
  4.             dim n as long
  5.             dim setSheet as object
  6.             dim path as string
  7.             dim fDataFilenameCell as object
  8.             dim pDataFilenameCell as object
  9.             dim fDataOut as object
  10.             dim pDataOut as object
  11.             dim fDataFilename as string 'quite obvious meaning...
  12.             dim pDataFilename as string
  13.             dim fUrl as string
  14.             dim pUrl as string
  15.             dim tempDoc as object
  16.             dim tempSheet as object
  17.             dim oPropertyValue(0) as new com.sun.star.beans.PropertyValue
  18.             dim dispatcher as object
  19.             dim dispArgs(0) as new com.sun.star.beans.PropertyValue
  20.             dim outDocument as object
  21.             dim fDocument as object
  22.             dim pDocument as object
  23.            
  24.             thisDoc = thiscomponent
  25.             thisUrl = thisDoc.url
  26.             outDocument = thisDoc.CurrentController.Frame
  27.  
  28.             for n = len( thisUrl ) to 1 step -1
  29.                     if mid( thisUrl, n, 1 ) = "/" then exit for
  30.             next n
  31.             path = left( thisUrl, n )
  32.             dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  33.            
  34.             'Dim myrange = starDeskTop.getCellRangeByName("test2")
  35.             'fDataFilenameCell = oSheet.GetCellbyPosition( 6, 1 )
  36.             'msgbox "hello"
  37.             'settingSheet = thisComponent.sheets(0) ' if you rearrange them or add other cheets this number would change !!
  38.             setSheet = thisDoc.Sheets.getByName("home")
  39.             fDataSheet = thisDoc.Sheets.getByName("fData")
  40.             pDataSheet = thisDoc.Sheets.getByName("pData")
  41.            
  42.             'pDataFilenameCell = settingSheet.getCellbyPosition( 1, 9 )
  43.             fDataFilenameCell = setSheet.getCellRangeByName("fDataFilename")
  44.             pDataFilenameCell = setSheet.getCellRangeByName("pDataFilename")
  45.             fDataOut = fDataSheet.getCellRangeByName("fData")
  46.             pDataOut = pDataSheet.getCellRangeByName("pData")
  47.             fDataFilename = fDataFilenameCell.string
  48.             pDataFilename = pDataFilenameCell.string
  49.             fUrl = path + fDataFilename
  50.             pUrl = path + pDataFilename
  51.             'pUrl = convertToUrl(pDataFilename)
  52.            
  53.             oPropertyValue(0).name= "FilterOptions"
  54.             oPropertyValue(0).value= "59,34,76,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10/10/10/10/10/11/10/12/10/13/10/14/10/15/10/16/10/17/10/18/10/19/10/20/10/21/10/22/10/23/10/24/10/25/10/26/10/27/10/28/10/29/10/30/10/31/10/32/10/33/10/34/10/35/10/36/10/37/10/38/10/39/10/40/10/41/10/42/10/43/10/44/10/45/10/46/10/47/10/48/10/49/10/50/10/51/10/52/10/53/10/54/10/55/10/56/10/57/10/58/10/59/10/60/10/61/10/62/10/63/10/64/10/65/10/66/10/67/10/68/10/69/10/70/10/71/10/72/10/73/10/74/10/75/10/76/10/77/10/78/10/79/10/80/10/81/10/82/10/83/10/84/10/85/10/86/10/87/10/88/10/89/10/90/10/91/10/92/10/93/10/94/10/95/10/96/10/97/10/98/10/99/10/100/10/101/10/102/10/103/10/104/10/105/10/106/10/107/10/108/10/109/10/110/10/111/10/112/10/113/10/114/10/115/10/116/10/117/10/118/10/119/10/120/10/121/10/122/10/123/10/124/10/125/10/126/10/127/10/128/10/129/10/130/10/131/10/132/10/133/10/134/10/135/10/136/10/137/10/138/10/139/10/140/10/141/10/142/10/143/10/144/10/145/10/146/10/147/10/148/10/149/10/150/10/151/10/152/10/153/10/154/10/155/10/156/10/157/10/158/10/159/10/160/10/161/10/162/10/163/10/164/10/165/10/166/10/167/10/168/10/169/10/170/10/171/10/172/10/173/10/174/10/175/10/176/10/177/10/178/10/179/10/180/10/181/10/182/10/183/10/184/10/185/10/186/10/187/10/188/10/189/10/190/10/191/10/192/10/193/10/194/10/195/10/196/10/197/10/198/10/199/10/200/10/201/10/202/10/203/10/204/10/205/10/206/10/207/10/208/10/209/10/210/10/211/10/212/10/213/10/214/10/215/10/216/10/217/10/218/10/219/10/220/10/221/10/222/10/223/10/224/10/225/10/226/10/227/10/228/10/229/10/230/10/231/10/232/10/233/10/234/10/235/10/236/10/237/10/238/10/239/10/240/10/241/10/242/10/243/10/244/10/245/10/246/10/247/10/248/10/249/10/250/10/251/10/252/10/253/10/254/10/255/10/256/10,2057,false,false"
  55.             'oPropertyValue(0).Value= "59,34,76,1,,2057,false,false" ' would be ok, but not sure with old versions of openoffice
  56.            
  57.             'For the filter options: this macro shows the filter used to open a csv file (open a csv file with the options you want, and run this on it. A pop up will come showing the used filters):
  58.             ' see also page 285 of OOME_3_0.pdf
  59.             'Sub showFilterOptions
  60.             '   Dim args(),i%
  61.             '   args() = thisComponent.getArgs
  62.             '   for i = 0 to uBound(Args())
  63.             '   if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
  64.             '   next
  65.             'End Sub
  66.             'Options are separated by comma, multiple values within an option are separatd by /
  67.             ' 59 -> delimiter, ASCII value for semicolon
  68.             ' 34 -> surronding char for text, ASCII value for double quotation mark
  69.             ' 76 -> character set, UTF-8
  70.             ' 1  -> number of row to start the import from
  71.             ' 1/10/2/10.. -> number of field and its formatting pairs. 10 is US english locale
  72.             ' 2057 -> seems a new option for UK locale. This seems to be enough to recognise dots as decimal separators
  73.             ' false -> ?
  74.             ' false -> ?
  75.            
  76.             'Flags = 23
  77.             fDataOut.clearContents(7) 'flags enumerations: value 1,string 4, datetime 2
  78.             pDataOut.clearContents(7)
  79.             tempDoc = starDeskTop.loadComponentFromURL(fUrl, "_blank", 0, oPropertyValue)
  80.             copyData(tempDoc, thisDoc, fDataSheet)
  81.             tempDoc.close (-1)
  82.             tempDoc2 = starDeskTop.loadComponentFromURL(pUrl, "_blank", 0, oPropertyValue)
  83.             copyData(tempDoc2, thisDoc, pDataSheet)
  84.             tempDoc2.close (-1)
  85.             msgbox ( "Data loaded", 0+64+128, "Confirmation")
  86.            
  87.     End Sub
  88.  
  89.     Sub copyData (sourceDoc as object, destDoc as object, destSheet as object)
  90.             Dim oSourceSheet, oSourceRange
  91.             Dim oTargetCell
  92.             Dim oDisp, octl
  93.             Dim NoArg()
  94.            
  95.             octl = sourceDoc.getCurrentController()
  96.             oSourceframe = octl.getFrame()
  97.             oSourceSheet = sourceDoc.Sheets(0)
  98.             oSourceRange = oSourceSheet.getCellRangeByPosition(0,0,1000,1000000)
  99.      
  100.             REM create the DispatcherService
  101.             oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
  102.  
  103.             REM select source range
  104.             octl.Select(oSourceRange)
  105.  
  106.             REM copy the current selection to the clipboard.
  107.             oDisp.executeDispatch(octl, ".uno:Copy", "", 0, NoArg())
  108.        
  109.             REM Move focus to cell 0,0.
  110.             REM This ensures the focus is on the "0,0" cell prior to pasting.  
  111.             REM You could set this to any cell.
  112.             REM If you don't set the position, it will paste to the
  113.             REM position that was last in focus when the sheet was last open.
  114.             oTargetCell = destSheet.getCellByPosition(0,0)
  115.             destDoc.getCurrentController().Select(oTargetCell)
  116.  
  117.             REM paste from the clipboard to your current location.
  118.             oTargetframe = destDoc.getCurrentController().getFrame()
  119.             oDisp.executeDispatch(oTargetFrame, ".uno:Paste", "", 0, NoArg())
  120.            
  121.             refresh_DBRanges_And_Pilots() ' refresh pilots
  122.     End
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top