Formula generated by oracle query doesn't work

[Linde]

Hai everybody,

I connect my excel 2003 to an Oracle database. Then I use a query to retreive some data from oracle and put it into an excel sheet.

In the query I generate an formula for excel (including the rownumber) Then problem is that excel won't accept this formula and display's it as text.
Entering the cell and close it (<F2> followd by <ENTER>) results in a working formula.

If I put the FORMULA field into a fariable i get a error 1004 that is not giving me a direction for the problem .
Does any one have a good solution for this problem?

Below is the code I'm using:

Sub test()
Dim SQL As String
Dim tmp As String
Dim tmp2 As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
' open query file and load the query
Open "D:\ontwikkeling\test.sql" For Input As #1
While Not EOF(1)
Line Input #1, tmp
    SQL = SQL + tmp + Chr(13) + Chr(10)
Wend
Close #1
 
' open the connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=xx;Uid=xx;Pwd=xx;"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = SQL
rs.Open
R = 2
rs.MoveFirst
While Not rs.EOF
 Sheets("TEST").Cells(R, 1).Value = rs.Fields("DW")
  Sheets("TEST").Cells(R, 2).Value = rs.Fields("FR")
Sheets("TEST").Cells(R, 3).Value = rs.Fields("ZS")
 Sheets("TEST").Cells(R, 4).Value = rs.Fields("TB")
 Sheets("TEST").Cells(R, 5).Value = rs.Fields("AQ")
 Sheets("TEST").Cells(R, 5).Value= rs.Fields("FORMULA")
  Range("G" & R).Select
    ActiveCell.FormulaR1C1 = rs.Fields("FORMULE")
  Range("H" & R).Select
    ActiveCell.Formula = rs.Fields("FORMULE")
  tmp = rs.Fields("FORMULE")
  Range("I" & R).Select
    ActiveCell.FormulaR1C1 = tmp    [COLOR=red] 'results in error 1004[/COLOR]
R = R + 1
rs.MoveNext
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
 
 
End Sub

The FORMULA field is a string value. apparently Excel puts a ' in front of the FORMULA string and this ' is not removable by using the mid() statement.

Hoping to hear from you soon,

Harm

 

[GlennUK]

You refer to
rs.Fields("FORMULA")
and
rs.Fields("FORMULE")
in your code. What is the difference? What are the contents of these when the error occurs?

 

[Linde]

Hello Glenn,

They are the same (typing error from me).
The contents of FORMULA for row 2 wil be:

"=IF(ISERROR(INDEX(INDIRECT((C2)&""!$A$2:$A$9999""),MATCH(B2,INDIRECT(C2),0),1,1)),"""",INDEX(INDIRECT((C2)&""!$A$2:$A$9999""),MATCH(B2,INDIRECT(C2),0),1,1))"

for row 3 the query wil return C3 and B3 and so on.

Harm

 

[GlennUK]

Can you try changing FormulaR1C1 to Formula in the row that errors?

 

[Linde]

I tryed that but the error stays

Harm

 

[Andrew Poulsom]

Are you saying that it works in column H but not in column I? The only difference is that you are assigning the formula to a string variable.

 

[Linde]

In column E to H the formula string is returned and in in column I the error occurs. In Column E to H I'm getting a text in stead of a formula

harm.

 

[Andrew Poulsom]

I don't know whether it will work for you, but for me this converted a formula entered as text into an actual formula:

    With ActiveCell
        .Formula = .Text
    End With

 

[Linde]

I wil try it as soon as I have the oppertunity.
I had to close my work.
result wil be posted

harm