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 ubound 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 understandble....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...
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 …
Software Details: Lookout version 4.0.1 b51
NI Hardware : None
Operating System: Windows XP
CPU:
RAM (MB):
Computer Vendor:
Message Edited by Laura F. on 10-12-2009 04:53 PM
