I have a macro in excel to run .sql files connecting to Oracle to extract datas.
My problem is I have a very weird “bug” based on if the first line of the .sql file is a comment line.
If there are only the “–” or the whole comment line is after the select, it will work, but if there is something after the “–” on the first line, it won’t work. (It is the same deal with /**/. Can’t put something inbetween the /* and */ else it won’t work).
Here are some example of .sql:
Example 1 (don’t work):
-- test select * from ABC.ABC_FIELD ABCField where what_ever = 15
Example 2 (works):
-- select * from ABC.ABC_FIELD ABCField where what_ever = 15
Example 3 (works):
select -- test * from ABC.ABC_FIELD ABCField where what_ever = 15
Here are the code of the VBA that might be relevant. It fail at the rs.Open line (the rs doesn’t open at all).
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection cnn.ConnectionString = "DSN=ABC_ORA;UID=" & UserId & ";PWD=" & Password & ";Persist Security Info=true" cnn.Open
Dim myFile As String Dim text As String Dim textline As String LaRequete = "" myFile = Application.Worksheets(FeuilleParametres).Range("FICHIER").Value Open myFile For Input As #1 Do Until EOF(1) Line Input #1, textline LaRequete = LaRequete & textline & " " & vbCrLf Loop Close #1
Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open LaRequete, cnn.ConnectionString
I hope I provided every needed information to solve this. Thanks.