Excel VBA : 400 error: Annoying!

[jh1234]

I have this code that seems to run but then kicks out a 400 error thus preventing the remainder of the macro from running. Please help!

Sub ChangeSQL_NEW()

Const SName As String = "email"
Const SQLStnDte As String = "MY SQL SCRIPT"
Dim OrigSQL As String
Dim NewSQL As String
Dim ws As Worksheet, lo As ListObject
Dim StartDate As String
Dim EndDate As String
Dim TheActiveWindow As Window
Dim TempWindow As Window

If ThisWorkbook.Sheets("Email List").Range("f24") > 0 Then
StartDate = ThisWorkbook.Sheets("Email List").Range("f24")
EndDate = ThisWorkbook.Sheets("Email List").Range("f25")

For Each ws In ThisWorkbook.Worksheets

If InStr(1, ws.Name, SName) = 0 Then
ws.Select
Set lo = ws.ListObjects(1)
OrigSQL = lo.QueryTable.CommandText
NewSQL = SQLStnDte
NewSQL = Replace(NewSQL, "START", StartDate)
NewSQL = Replace(NewSQL, "END", EndDate)
NewSQL = Replace(NewSQL, "REPLACETHIS", Left(ws.Name, 6))
lo.QueryTable.CommandText = NewSQL
lo.QueryTable.Refresh
lo.QueryTable.CommandText = OrigSQL
End If

Next
End If
​

 

[Derek Brown]

You did not say which line is giving the error.
However, I would guess that you may have to activate worksheet "Email List" before using the Range object.
Alternatively, and probably better, you could use Cells instead e.g. instead of Range("F24").Value use Cells(24,6).
Note also the use of the ".Value".

 

[jh1234]

Thanks for your help. It turns out that I'd made a schoolboy error, looking for 'email' in the worksheet name when I should have been looking for 'Email'...