D
drinese18
I'm not too familiar much with the DAO syntax, I basically want to download
information from a SQL database into certain columns of a spreadsheet my code
can be seen below:
Sub SPICEdownload_indexvalue()
'On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("S&P 500 PKA NET TR 15%")
Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
'Dim ts As String
Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=SPICE;UID=eqiqry;pwd=eqiqry;SERVER=PSDR1.MHF2.MHF.MHC;")
ts = ""
'i = 3
'While Trim(data.Cells(i, 1)) <> ""
'ts = ts & IIf(ts <> "", "or ", "") & "(Index_id = " &
Trim(data.Cells(i, 1)) & " and index_date='" & _
'Format(Trim(data.Cells(i, 2)), "dd-MMM-yyyy") & "') "
'i = i + 1
'Wend
Sql = "SELECT S&P_500_PKA Ethical, S&P_500_PKA_Ethica_Indx_Div WHERE order
by index_id"
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)
i = 3
While Trim(data.Cells(i, 1)) <> ""
data.Range("A" & i & ":C" & i) = Array(rs!index_id, rs!index_dividend,
rs!close_index_dividend)
rs.MoveNext
i = i + 1
Wend
rs.Close
db.Close
Set sb = Nothing
Set rs = Nothing
End Sub
I'm not sure what is wrong with my code but basically its not downloading
into those columns, what am I doing wrong? Can anyone help me with this
please?
Any help would be greatly appreciated
information from a SQL database into certain columns of a spreadsheet my code
can be seen below:
Sub SPICEdownload_indexvalue()
'On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("S&P 500 PKA NET TR 15%")
Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
'Dim ts As String
Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=SPICE;UID=eqiqry;pwd=eqiqry;SERVER=PSDR1.MHF2.MHF.MHC;")
ts = ""
'i = 3
'While Trim(data.Cells(i, 1)) <> ""
'ts = ts & IIf(ts <> "", "or ", "") & "(Index_id = " &
Trim(data.Cells(i, 1)) & " and index_date='" & _
'Format(Trim(data.Cells(i, 2)), "dd-MMM-yyyy") & "') "
'i = i + 1
'Wend
Sql = "SELECT S&P_500_PKA Ethical, S&P_500_PKA_Ethica_Indx_Div WHERE order
by index_id"
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)
i = 3
While Trim(data.Cells(i, 1)) <> ""
data.Range("A" & i & ":C" & i) = Array(rs!index_id, rs!index_dividend,
rs!close_index_dividend)
rs.MoveNext
i = i + 1
Wend
rs.Close
db.Close
Set sb = Nothing
Set rs = Nothing
End Sub
I'm not sure what is wrong with my code but basically its not downloading
into those columns, what am I doing wrong? Can anyone help me with this
please?
Any help would be greatly appreciated