Re: Huge SQL Statement, VBA, Timeouts
Agree with Craig - use stored procedure if poss.
I see there is an Excel ODBCTimeout property that you could try eg.
To convert SQL can use a bit of gen.
Code
Public Sub ConvertSQLStrToVBA()
Dim LastRow As Long
Dim RowCounter As Long
Dim StartStr As String
Dim EndStr As String
Dim SubStartStrRows As Long
Dim RowStr As String
Dim AutoArrayCounter As Long
Dim counter As Long
Dim AutoArray(1000) As String
For counter = LBound(AutoArray) To UBound(AutoArray)
AutoArray(counter) = ""
Next counter
StartStr = Chr(34) & " "
EndStr = " " & Chr(34) & " & _"
Sheet2.Range("A1:Z65000").ClearContents
LastRow = Sheet1.Range("A65000").End(xlUp).Row
AutoArray(1) = "Dim SQLStr as String"
AutoArray(2) = "SQLStr = " & Chr(34) & " " & EndStr
SubStartStrRows = 2
For RowCounter = 1 To LastRow
RowStr = Sheet1.Range("A" & RowCounter).Value
If RowCounter <> LastRow Then
AutoArray(RowCounter + SubStartStrRows) = StartStr & RowStr & EndStr
Else
AutoArray(RowCounter + SubStartStrRows) = StartStr & RowStr & " " & Chr(34)
End If
Next RowCounter
RowCounter = 0
For AutoArrayCounter = LBound(AutoArray) To UBound(AutoArray)
RowCounter = RowCounter + 1
Sheet2.Range("A" & RowCounter).Value = AutoArray(AutoArrayCounter)
Next AutoArrayCounter
End Sub
Display More
Paste SQL in Sheet1 A1, run code, result in Sheet2.