Hallo,
ich habe hier eine Funktion die Daten von einer csv Datei ladet.. die funktioniert wunderbar aber nur mit 202 Zeilen ..wenn die Zeilezahl größer ist versagt sie.. hat einer eine Idee wieso so ist und wie man es beheben kann..??
hier die Funktion:
Sub Main
End Sub
'----------
' This wrapper function is used when you want to use
' the stock prices macro on a spreadsheet.
' Example:
' 1. Create four cells A3:A6 that contain four symbols.
' 2. Select cells B3:B6
' 3. With all cells B3:B6 selected, enter the formula: =GetPrices( A3:A6 )
' 4. Instead of pressing ENTER, press CTRL-SHIFT-ENTER.
' This enters an array formula.
' Note that array formulas are surrounded by {curly} brackets.
' Now all four cells B3:B6 contain the prices for symbols in A3:A6.
'
Function LookupStockPrices( aCellsWithSymbolsByRows )
nNumRows = UBound( aCellsWithSymbolsByRows, 1 )
aStockSymbols = DimArray( nNumRows-1 )
' Dim aStockSymbols( nNumRows-1 )
For i = 1 To nNumRows
aStockSymbols(i-1) = aCellsWithSymbolsByRows(i,1)
Next
aStockPrices = GetStockPrices( aStockSymbols() )
aResults = DimArray( nNumRows-1, 0 )
For i = 0 To nNumRows-1
aResults(i,0) = aStockPrices(i)
Next
LookupStockPrices = aResults
End Function
'----------
' This function looks up the price of a stock symbol from yahoo.
' Pass in these parameters:
' An ARRAY of the stock symbols to look up.
' Each symbol is a string.
' Returns:
' An ARRAY of the stock prices.
' Each price is a number.
'
Function GetStockPrices( aStockSymbols )
cSymbols = ""
For i = 0 To UBound( aStockSymbols )
If i > 0 Then
cSymbols = cSymbols + "&"
EndIf
cSymbol = aStockSymbols(i) ' get single symbol from array
cSymbols = cSymbols + "s=" + cSymbol
Next
cURL = "
http://quote.yahoo.com/d/quotes.csv?" + cSymbols + "&f=srd1t1c1ohgv&e=.csv"
' Open up a new spreadsheet from the above URL.
' Specify the CSV filter with options that decode the CSV format comming back from Yahoo.
' Specify the Hidden property so that the spreadsheet does not appear on the screen.
oCalcDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0,_
Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_
MakePropertyValue( "FilterOptions", "44,34,SYSTEM,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10" ),_
MakePropertyValue( "Hidden", True ) ) )
' Get the first sheet of the Calc document.
oSheet = oCalcDoc.getSheets().getByIndex( 0 )
' Create an array of the prices.
' The prices are in column B of the spreadsheet (i.e. column 1).
Dim aStockPrices( UBound( aStockSymbols ) ) As Double
For i = 0 To UBound( aStockSymbols )
aStockPrices( i ) = oSheet.getCellByPosition( 1, i ).getValue()
Next
' Be sure to close the spreadsheet, because it is hidden, and the user cannot close it.
oCalcDoc.close( True )
' Return the array of prices.
GetStockPrices = aStockPrices()
End Function
'----------
' Create and return a new com.sun.star.beans.PropertyValue.
'
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
Dim oPropertyValue As New com.sun.star.beans.PropertyValue
If Not IsMissing( cName ) Then
oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function
grüß