Using VBA to import BDH from Bloomberg

[offthelip]

I did something similar a while ago hopefully the interface hasn't changed , this is the code I used to write the functions acorss the sheet "Download" from a named range called "Instruments" on the sheet "stocklist:
This code downloads prices between two dates

Sub writebdh()' this is the double quotes character
If True Then
tt = Chr(34)


Sheets("Stocklist").Select
inarr = Range("Instruments")
cnt = Range("Instruments").Rows.Count


Sheets("Download").Select
For i = 1 To cnt
titl = inarr(i, 1)




s1 = tt & titl & tt
s21 = Cells(6, 6)
s22 = Cells(6, 7)
s23 = Cells(6, 8)
s24 = Cells(6, 9)
s2 = tt & s21 & tt & " " & tt & s22 & tt & " " & tt & s23 & tt & " " & tt & s24 & tt
'open price only for this test
s2 = tt & s21 & tt
s3 = tt & Cells(7, 2) & tt


s4 = tt & Cells(7, 4) & tt
s5 = tt & Cells(5, 5) & tt
s6 = tt & "BarSz="
s7 = Cells(2, 9) & tt
Endstr = "," & tt & "Dir=V" & tt & "," & tt & "Dts=S" & tt & "," & tt & "Sort=A" & tt & ", " & tt & "Quote=C " & tt & ", " & tt & "UseDPDF=Y " & tt & ")"
frmtxt = "=BDH(" & s1 & "," & s2 & "," & s3 & "," & s4 & "," & s5 & "," & s6 & s7 & Endstr
Sheets("Download").Select
Cells(7, 7 + i * 7).Formula = frmtxt
Next i
End If
End Sub

This is the formula that it creates:
=BDH("Z 1 Index","OPEN","30/01/00","03/09/12","","BarSz=","Dir=V","Dts=S","Sort=A", "Quote=C ", "UseDPDF=Y ")