Sir,
subject:: Retrieving data from citadel from excel macro
Actually what i want is to retrieve data from citadel to excel having interval of 1 day...what the problem i am facing
is that i am using excel macro in which i am using sqlrequest to get the data into array , then i retrieve data from that array into excel sheet using lbound and unbound function of array......i am also sending my file plz go thro' it....when i request a query with * it returns the data to the excel sheet and all the fields are overlapping...not get data required......but get the data which is not understandable....but when i run a query with star(all fields) its run well...but when i query the data with the specific fields in citadel like localtime and utctime...it gives the error �subscript out of range..�run time error 9..its the error of array dynamically generated...but when i convert the citadel database into excess ..and then retrieve that data into excel using same code it works fine with specific fields and conditions but it fails with the citadel database...i have the requirement with the citadel database..plz help out me...I want to use only
excel macro as it runs automatically when we want like when we open the excel sheet it works whereas with the Microsoft query my requirement works fine�plz let me know if u have solution with excel macro only�
my code as follows...
Private databaseName As Variant
Dim returnArray As Variant
Private queryString As Variant
Dim strText As String
Private Sub Worksheet_Activate()
databaseName = "CITADEL4"
queryString = "SELECT * FROM Traces WHERE interval=1:04:00"
MsgBox queryString
returnArray = SQLREQUEST("DSN=Citadel4", queryString, Worksheets("Sheet1").Range("A1"), 2, True)
For i = LBound(returnArray, 1) To UBound(returnArray, 1)
For j = LBound(returnArray, 2) To UBound(returnArray, 2)
Worksheets("Sheet1").Cells (i, j).Formula = returnArray(i, j)
'MsgBox j
Next j
Next i
End Sub
What I want is the query this one�
�Select Localtime, utctime, Resolution from traces where localtime >11/7 6:00 and localtime<11/7 18:00 and interval=1:00:00� �
the code which I wanted to run is as follows but it fails �giving error subscript out of range but works well with all fields not the specific fields required�
Private databaseName As Variant
Dim returnArray As Variant
Private queryString As Variant
Dim strText As String
Private Sub Worksheet_Activate()
databaseName = "CITADEL4"
queryString = "�Select Localtime , utctime ,Resolution from traces where localtime >11/7 6:00 and localtime<11/7 18:00 and interval=1:00:00� �
returnArray = SQLREQUEST("DSN=Citadel4", queryString, Worksheets("Sheet1").Range("A1"), 2, True)
For i = LBound(returnArray, 1) To UBound(returnArray,1)
For j = LBound(returnArray, 2) To UBound
(returnArray, 2)
Worksheets("Sheet1").Cells(i, j).Formula = returnArray(i, j)
Next j
Next i
End Sub
Plz help me out �
Sukesh puri
sukeshpuri@hotmail.com
sukeshpuri@yahoo.com
SPRAY ENGG. DEVICES CHANDIGARH
