Hi, I wrote following code to query some tables (sybase)

sqlStr = "SELECT r.Desk, r.LimitSubGroup, l.LimitName, r.RealDataColumnShift," & _
            " l.ReportType, r.Column, r.RowNum, s.Value" & _
            " FROM ReportTemplate r, LimitDefinition l, RiskRptDetails s" & _
            " WHERE r.SheetName = 'CreditDeltaDetail' " & _
            " AND r.TemplateName='GFM.xlt' " & _
            " AND s.PositionDate='" & Format(positionDate, "mm/dd/yyyy") & "'" & _
            " AND r.LimitId = l.LimitId" & _
            " AND l.ReportType not in('DeskID')" & _
            " AND r.Desk = s.Grouping" & _
            " AND r.LimitSubGroup = s.SubGroup" & _
            " AND r.LimitId = s.LimitId"

    rstData.Open sqlStr, dbconn, adOpenKeyset, adLockReadOnly
    ....
Then I clicked save button for this VBAproject, I got error message saying "Excel cannot save this file" and excel renamed my worksheet to temporay name such as E03F0190.

Finally I changed above code to

sqlStr = "SELECT r.Desk, r.LimitSubGroup, l.LimitName, r.RealDataColumnShift," & _
            " l.ReportType, r.Column, r.RowNum, s.Value" & _
            " FROM ReportTemplate r, LimitDefinition l, RiskRptDetails s" & _
            " WHERE r.SheetName = 'CreditDeltaDetail' AND r.TemplateName='GFM.xlt'" & _
            " AND s.PositionDate='" + positionDateStr + "'" & _
            " AND r.LimitId = l.LimitId" & _
            " AND r.Desk = s.Grouping" & _
            " AND r.LimitSubGroup = s.SubGroup" & _
            " AND r.LimitId = s.LimitId"

    rstData.Open sqlStr, dbconn, adOpenKeyset, adLockReadOnly
......

And excel allowed saving this time.

Is this because sqlStr is too large? I checked String limit(2^16). sqlStr is definitely within that limit. So what's wrong with the first sqlStr?

Thanks a lot
Wendy

You might have hit a line-continuation-character (“_”) count limit.
The limit is 25 (I think) in VB. Might be different in Excel VBA.

Thanks a lot. I tried a dummy string and it seems that for Excel VBA the line-continuation-character (“_”) limit is also 25. Does the module size have limit? Or the query for database has limit?

does it work if you place your string in an array?

rstData.Open Array("SELECT r.Desk, r.LimitSubGroup, l.LimitName, r.RealDataColumnShift,", " l.ReportType, r.Column, r.RowNum, s.Value", " FROM ReportTemplate r, LimitDefinition l, RiskRptDetails s", " WHERE r.SheetName = 'CreditDeltaDetail' ", " AND r.TemplateName='GFM.xlt' ", " AND s.PositionDate='" & Format(positionDate, "mm/dd/yyyy") & "'", " AND r.LimitId = l.LimitId", " AND l.ReportType not in('DeskID')", " AND r.Desk = s.Grouping", " AND r.LimitSubGroup = s.SubGroup", " AND r.LimitId = s.LimitId"), dbconn, adOpenKeyset, adLockReadOnly

Mat

One of the things I have resorted to from time to time is to take a long, line-continuationed string that’s giving me trouble, and insert a “'” just before one of the “&”s to turn the rest of the string into comment (comments support line-continuation), then see if the string will fly.

You can highlight and drag/drop the “'” to a location farther down the string, a little bit at a time, to try to see just exactly where the problem arrises.

I would really like to know exactly what part of the statement is generating the problem...

I used ' to comment the string little by little. Then it turns out

            " WHERE r.SheetName = 'CreditDeltaDetail' " & _
            " AND r.TemplateName='GFM.xlt' " & _

failed. It works if I combined them into one line
 " WHERE r.SheetName = 'CreditDeltaDetail' AND TemplateName='GFM.xlt' " & _

The wired thing is if I copy that failed code to a brand new worksheet, everything works fine.