Run-time error '1004' in import macro

hi derek

Thnks for the reply

sure thing

here is the code 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

don76 at 2007-8-30 >