help in returning array from access to excel in vba

[nssolanki]

Hi every body,
I have an Excel file from where i pull data from my access database through ado connection defined in a function. The existing function is as below,

======================================
Function Stockprice(sTicker As String, sField As String, sDate As Date, eDate As Date)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

Set cn = New ADODB.Connection
cn.Open "Provider= Microsoft.ACE.OLEDB.12.0; Data Source=C:\MDB-XL trial\EOD-EQ.mdb;"

Set rs = New ADODB.Recordset
With rs
.Open "Select * from Quotes_Eq where eqTicker= '" & sTicker & "' AND eqDate= #" & sDate & "#"
Stockprice = rs.Fields(sField)

Set rs = Nothing
cn.Close
Set cn = Nothing
End Function
====================================
Now the above function needs input as follows,
Stockprice("ticker", "ClosePrice","date") and returns a number in a cell.

Now, i wanted to improve upon this function so that i can provide field "ClosePrice" to be returned with a date range, say 'Between'.
For that i did some modification in the above code but to avail no benifit. My new code is.....

======================
Function Stockprice(sTicker As String, sField As String, sDate As Date, eDate As Date)

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider= Microsoft.ACE.OLEDB.12.0; Data Source=C:\MDB-XL trial\EOD-EQ.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
With rs
.Open "Select * from Quotes_Eq where eqTicker= '" & sTicker & "' AND eqDate between #"&sDate&"# and #"&eDate&"#", cn, , , adCmdText
Stockprice = rs.Fields(sField)
For iCols = 0 To Stockprice.Count - 1 ' the field names
ws.Cells(1, iCols + 1).Value = Stockprice(iCols).Value
Next
ws.Range(ws.Cells(1, 1), _
ws.Cells(1, Stockprice.Count)).Font.Bold = True
CopyFromRecordset rs

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function
=========================

the above code is not working and terminates at ws.Cells(1, iCols + 1).Value = Stockprice(iCols).Value line,
need help to sort the issue.

the output should be like this,
cell A1,A2,A3,A4 contains input variables for function Stockprice("ticker","ClosePrice","StartDate","EndDate") respectively.
And output is required in any cell where function is called and return array of prices below that column.

Thanks.

 

[JHB]

It can be in many places it goes wrong:
We don't se where you declare the "ws", if it is Public or Private variable.
How you open the Excel file and where.

In a start:
What happen if you put in a hard-code value in ws.Cells(1, iCols + 1)?
ex. ws.Cells(1, iCols + 1).Value =10
Then you can see, if it is the ws.Cells(1, iCols + 1).Value part there make problem or if it is the Stockprice(iCols).Value!