2

I have the following VBA which updates the command text in my data connection in excel.

Sub ClaimLine_Macro()

    Dim strsql As String
    strsql = "Select a.* from claim a "
    strsql = strsql & Worksheets("SQL_ClaimLine").Range("claim1")
    strsql = strsql & Worksheets("SQL_ClaimLine").Range("claim2")
    strsql = strsql & Worksheets("SQL_ClaimLine").Range("claim3")

    With ActiveWorkbook.Connections("ClaimLineExtract").ODBCConnection
        .BackgroundQuery = True
        .CommandText = strsql

    End With

    ActiveWorkbook.Connections("ClaimLineExtract").Refresh

End Sub

If I run the VBA above I get the error Run-time error 1004 application-defined or object defined error.

What's interesting is if I comment out

strsql = strsql & Worksheets("SQL_ClaimLine").Range("claim3")

The VBA works! I am not sure what is causing the error.

Further more, if I run the VBA without claim3 and copy and paste the SQL in to TOAD, I get one really long text string.

When I run it with claim3, it now wraps the text string at character 1,023 on every line. I believe this is causing the issue. Is there any way around the wrapping at 1,023 characters?

The ranges in the VBA (Claim1, claim2, and claim3) each reference a single cell that contains a concatenated block of 1,000 claims. The cells have a UDF formula that places them into the correct syntax while appending "or a.claim " at the end when needed. Claim1 is hardcoded to say "Where a.claim " in the beginning.

I have been trouble shooting to see if it is a specific claim but what I found is a little weird. Each range has 1,000 claims in it, regardless of which range I change, if I reduce the total number of claims to 2,580 the VBA will work with no error(If I increase to 2,581 the VBA errors). This means there are 32,698 characters in the strSQL.

AlmostThere
  • 557
  • 1
  • 11
  • 26
  • Have you verified that the named range "claim3" exists and has no typo in the name? – braX Aug 22 '17 at 17:08
  • If `"claim3"` was a *single cell* this would work as you would get its `.Value` by default, but as its a bunch of cells there is no string representation of them all - attempting to treat it like a string is not going to work. You can build your own string representation [E.g.](https://stackoverflow.com/questions/41777996/how-can-i-convert-a-range-to-a-string-vba) but as there are so many values its probably not the correct approach. – Alex K. Aug 22 '17 at 17:09
  • I have verified the name and claim3 is one cell (UDF formula in it that concatenates 1,000 claims together) Claim2 and claim3 are essentially duplicates just referencing a different set of claims. – AlmostThere Aug 22 '17 at 17:13
  • 1
    The concatenation is resulting in illegal SQL. We can't help you without seeing what the SQL looks like. Did you try setting a breakpoint (F9) and `Debug.Print strsql` to output the resulting string to the *immediate pane* (Ctrl+G)? (perhaps try with fewer values first) Seems that string is missing a `WHERE` clause, too. Please read [mcve] and [edit] your question accordingly. As it stands, it's not answerable. – Mathieu Guindon Aug 22 '17 at 17:25
  • The format of the ranges is not the issue. As I stated in the question, if I do not use claim3, the VBA works. when I add claim 3 it does not work . what I noticed was that when I add claim3 and use Debug.Print strsql and tehn copy and paste the SQL into TOAD, the sql wraps at 1,023 characters on every line. when I remove claim3 it does not do this. It seems something is happening with the formatting when claim3 range is added. – AlmostThere Aug 22 '17 at 19:17

1 Answers1

2

I was able to find a solution! The issue was with the length of the character strings and how the VBA was reading/splitting the lines. After hours of researching, looking for similar issues, I came across a solution. I had to create a function that broke the VBA into smaller chunks.

The VBA below is a function that allows the VBA string to max out at 200 characters.

Function SplitMeUp(strin As String)

    Const MAX_LEN As Long = 200
    Dim rv(), n, i
    n = Application.Ceiling(Len(strin) / MAX_LEN, 1)
    ReDim rv(0 To n - 1)
    For i = 1 To n
        rv(i - 1) = Mid(strin, 1 + ((i - 1) * MAX_LEN), MAX_LEN)
    Next i
    SplitMeUp = rv
End Function

This means in my the line for my command text now looks like this.

.CommandText = SplitMeUp(strsql)

By splitting up the strsql, and then having it put back together allows the SL to be read correctly in the data connections and does not result in a runtime error.

As a bonus I went out to a range of claim23 (so that's 23,000 claims) and the VBA still worked!

AlmostThere
  • 557
  • 1
  • 11
  • 26
  • Thanks this fixed my similar issue of reading in over 1k lines of SQL and having the 1004 error as well. – ApplePie Jan 05 '18 at 20:06