OpenOffice.info

Code: Alles auswählen

REM  *****  BASIC  *****
Option Explicit

' Original: http://www.oooforum.org/forum/viewtopic.phtml?t=22988
' Anpassung 2006-12-24 wro
' http://de.openoffice.info/posting.php?mode=quote&p=40369

Sub Main
  DoTheWork()
End Sub

Sub DoTheWork(Optional oDocument)
  Dim oSheet   ' Sheet to use
  Dim oWorkSheet   ' Arbeitsblatt für Ausgabe
  Dim oDoc     ' Document to use
  Dim sName$   ' Will hold the stock to get
  Dim oCell
  Dim oDisp
  Dim oFrame

  REM Use ThisComponent if the argument is missing
  oDoc=IIF(IsMissing(oDocument), ThisComponent, oDocument)

  REM Assume that we should use the first sheet.
  REM Perhaps it would be safer to use the current sheet.
  oSheet = oDoc.getSheets().getByName("Aufstellung")
  oWorkSheet = oDoc.getSheets().getByName("_work")
 
  REM Yes, I will get the active sheet rather than the first sheet.
  ' oSheet = oDoc.getCurrentController().getActiveSheet()

  REM Get string value from cell B1. B = Column 1 and 1 = Row 0
  
  ' Startzeilen-Index
  i = 1 
  ' ersten Suchbegriff aus Aufstellung
  sName = oSheet.getCellByPosition(1, i).getString()

  Do 

	' Ausgabe in Arbeitsblatt vorbereiten
	oCell = oWorkSheet.getCellByPosition(0, 0)
	oDoc.getCurrentController().select(oCell)

  REM Create a dispatch helper so that you can use the new
  REM easier to sue dispatch syntax.
  oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
  oFrame = oDoc.getCurrentController().getFrame()
  InsertExternalDataSource(sName$, oFrame, oDisp)


	' Ergebnis holen und hinter Suchbegriff einstellen
	oSheet.getCellByPosition(2, i).setValue( _
		oWorkSheet.getCellByPosition(1, 18).getValue()  )


	' Index der nächsten Zeile
	i = i + 1  
	' nächsten Suchbegriff aus Aufstellung
  	sName = oSheet.getCellByPosition(1, i).getString()

	
  Loop While sName <> ""
  
End Sub

REM sName is the string name of the stock.
REM oFrame is the frame to use for the dispatch
REM oDisp is the dispatch helper to use
Sub InsertExternalDataSource(sName$, oFrame, oDisp)
  Dim oArgs(2) as new com.sun.star.beans.PropertyValue
  oArgs(0).Name = "FileName"
  oArgs(0).Value = "http://de.finance.yahoo.com/q?s=" & sName
  oArgs(1).Name = "FilterName"
  oArgs(1).Value = "calc_HTML_WebQuery"
  oArgs(2).Name = "Source"
  oArgs(2).Value = "HTML_tables"
  oDisp.executeDispatch(oFrame , ".uno:InsertExternalDataSource", "", 0, oArgs())
End Sub