kod u potpunosti izgleda ovako:
ovaj makro treba da vraca rezultat u 3 kolone. u prvoj su datumi (kolona M), a u drugoj (to je idn i kolona N) i trecoj (bm i kolona O) podaci.
postoji slucaj kad nema trece kolone, ja hocu da u slucaju kad nema trece kolone, ignorise to i downloaduje prvu i drugu kolonu.
sta god pokusam, samo mi se pojavi msgbox.
-postoji li mogucnost, da kad napisem npr startdate 01.01.2011, a lista postoji tek od 01.03.2011, da u ovom periodu ispise neki broj (npr 100) i onda od 01.03.2011 da nastavi sa podacima koji su vec dostupni. nesto kao if empty, ne da javlja kao gresku vec da upise 100.
Code:
Function find_index(list, item)
For i = 0 To UBound(list)
If list(i) = item Then
find_index = i
Exit Function
End If
Next i
find_index = -1
End Function
Sub GetFundPlusBM()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim d As Date
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Set DataSheet = ActiveSheet
StartDate = DataSheet.Range("B2").Value
EndDate = DataSheet.Range("B3").Value
Symbol = DataSheet.Range("B8").Value
'construct the URL for the query
qurl = "http://pmtest:8090/xmlrpc/seri...ance_series_csv?show_bm=1&" & "fund_idn=" & Symbol
qurl = qurl & "&startdate=" & Format(StartDate, "yyyymmdd")
qurl = qurl & "&enddate=" & Format(EndDate, "yyyymmdd")
DataSheet.Range("A13").Value = qurl
DataSheet.Range("M:O") = Clear
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", qurl, False
WinHttpReq.Send
response = WinHttpReq.responseText
On Error Resume Next
If WinHttpReq.Status = 200 Then
Lines = Split(response, "" & Chr(10) & "")
heads = Split(Lines(0), ",")
DataSheet.Range("M1").Value = heads(0)
DataSheet.Range("N1").Value = heads(1)
If index_bm > -1 Then
DataSheet.Range("O1").Value = heads(2)
End If
For i = 1 To UBound(Lines)
If Lines(i) <> "" Then
s = Split(Lines(i), ",")
d = DateValue(s(0))
DataSheet.Range("M" & (i + 1)).Value = d
DataSheet.Range("N" & (i + 1)).Value = s(1)
If index_bm > -1 Then
DataSheet.Range("O" & (i + 1)).Value = s(2)
DataSheet.Range("O" & (i + 1)).NumberFormat = "0.00%"
End If
DataSheet.Range("M" & (i + 1)).NumberFormat = "m/d/yyyy"
DataSheet.Range("N" & (i + 1)).NumberFormat = "0.00%"
End If
Next i
Else
MsgBox "Fund series download failed."
End If
Range("A1").Select
End Sub