Run-time error '1004' in import macro

Run-time error '1004' in import macro

• Paul Stovell

  Hi don76,

  That helped a lot. The first thing I see is a problem with the SQL statement stored in gcquery.

  gcquery = "SELECT asset.cusip, asset.cur_amt_outsd " & _
  "FROM dmo_govcorp..asset asset WHERE asset.cusip in (" & cusip & ")"

  There is an additional . in the FROM clause and also there looks like a duplicate asset in there too.

  FROM dmo_govcorp.asset WHERE asset.cusip

  That might solve the problem.

  
  
  

• CodePfo

  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

  
  

• Phil Rogers

  Hi,

  Can you post some more of the code. You have an error in a SQL statement somewhere but its very difficult to see where.

  .Refresh BackgroundQuery:=False

  
  
  
• Run-time error '1004' in import macro