help in debugging macro
HELLO,
i am trying to debug an excel macro which is a web grabber.when i run it i get a sql syntax error when i try to debug it it goes to
Refresh BackgroundQuery:=False on the the last line just above end with
can someone help me fix this bug,this macro was working fine before now it gives only syntax error
the code is below
Sub grab(url1 As String, wt As Integer)
With ActiveSheet.QueryTables.Add(Connection:="URL;" & url1, Destination:=Range("A1"))
.Name = "refbills"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = wt
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Sub retrieveamts()
Dim rng As Range
Dim wt As Integer
Dim dt As Date
Dim cusip As String
Dim url1 As String
Sheets("Overall").Select
Cells.Delete
Sheets("Individual").Select
Cells.Delete
year1 = InputBox("Please enter year", , 2005)
Sheets("Overall").Select
wt = 6
url1 = "http://www.freddiemac.com/debt/data/cgi-bin/refbillaucres.cgi?order=AD&year=" & year1
Call grab(url1, wt)
Range("a1").Select
Set rng1 = Range(Range("D3"), Range("D3").End(xlDown))
'individual cusips
i = 4
Cells(i, 5).Activate
Do Until ActiveCell = ""
dt = ActiveCell.Offset(0, -2)
If dt < Now Then
With ActiveCell.Offset(0, 6)
.Value = "Ref Bill has Matured Will Not Check"
.Interior.ColorIndex = 26
End With
Else
wt = 5
url1 = "http://www.freddiemac.com/debt/data/cgi-bin/lookup.cgi?cusip2=" & ActiveCell & "&SUBMIT=Go"
Sheets("Individual").Select
Call grab(url1, wt)
Set rng = Range(Range("D3"), Range("D3").End(xlDown))
amount = WorksheetFunction.Sum(rng)
Sheets("Individual").Select
Cells.ClearContents
Sheets("Overall").Select
Cells(i, 11).Activate
ActiveCell = amount
cusip = cusip & "'" & Cells(i, 5) & "',"
End If
i = i + 1
Cells(i, 5).Activate
Loop
If cusip <> "" Then
cusip = Left(cusip, Len(cusip) - 1)
End If
Sheets("Govcorp").Select
Range("A1").Select
Call gcdata(cusip)
Sheets("Overall").Select
Range("A1").Select
i = 4
Cells(i, 5).Activate
Do Until ActiveCell = ""
isin = ActiveCell
Sheets("Govcorp").Select
On Error Resume Next
test = Cells.Find(What:=isin, After:=Range("a1"), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
If test = Empty Then
With Sheets("Overall").Cells(i, 13)
.Value = "Cant find CUSIP please check Manually"
.Interior.ColorIndex = 28
End With
Sheets("Overall").Select
Else
amount1 = ActiveCell.Offset(0, 1)
Sheets("Overall").Select
Cells(i, 12).Value = amount1 * 1000
If Cells(i, 12) <> Cells(i, 11) Then
With Cells(i, 13)
.Value = "Amounts do not match, please check website"
.Interior.ColorIndex = 27
End With
End If
End If
On Error GoTo 0
i = i + 1
Cells(i, 5).Activate
test = Empty
Loop
Columns("K:L").AutoFit
End Sub
Sub gcdata(cusip As String)
gcquery = "SELECT asset.cusip, asset.cur_amt_outsd " & _
"FROM dmo_govcorp..asset asset WHERE asset.cusip in (" & cusip & ")"
Range("A1") = gcquery
connejv = "ODBC;Driver={Sybase System 11};DSN=FISDB3;SRVR=FISDB3;" & _
"DB=dmo_govcorp;UID=reporter;PWD=reporter"
With Sheets("Govcorp").QueryTables.Add(Connection:=connejv, _
Destination:=Range("a1"), Sql:=gcquery)
'.CommandText = gcquery
.Name = "Query from EJV"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
