VBA Runtime Error 1004 “Application-defined or Object-defined error”

Question

I'm running multiple worksheets to populate data for my meetings. Every time I try to run I get a memory error and then the '1004' error. I am a beginner at coding so please give detail. Any suggestions?

    Sub PullData()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Sheets("Production").Select

    Dim StartYear As String
    Dim StartMonth As String
    Dim StartDay As String

    Dim EndYear As String
    Dim EndMonth As String
    Dim EndDay As String

    Dim NextDay As String
    Dim StartHour As String
    Dim EndHour As String

    StartYear = Year(Range("b2").Value)
    StartMonth = Month(Range("b2").Value)
    StartDay = Day(Range("b2").Value)

    EndYear = Year(Range("b2").Value)
    EndMonth = Month(Range("b2").Value)
    EndDay = Day(Range("b2").Value)

    NextDay = EndDay + 1

    StartHour = Hour(Range("b3").Value)
    EndHour = Hour(Range("b3").Value)




     Sheets("PPA").Select
     Cells.Select
     Selection.ClearContents
     With ActiveSheet.QueryTables.Add(Connection:="URL;https://fclmportal.amazon.com/ppa/inspect/node?nodeType=FC&warehouseId=ONT8&startDateDay=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&startDateWeek=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&startDateMonth=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&maxIntradayDays=1&spanType=Intraday&startDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&startHourIntraday=" & StartHour & "&startMinuteIntraday=0&endDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&endHourIntraday=" & EndHour & "&endMinuteIntraday=0", Destination:=Range("A1"))

     Selection = 3
     Formatting = None
     PreFormattedTextToColumns = True
     ConsecutiveDelimitersAsOne = True
     SingleBlockTextImport = False
     DisableDateRecognition = False
     DisableRedirections = False


     End With

     Sheets("PPR").Select
     Cells.Select
     Selection.ClearContents
     With ActiveSheet.QuryTables.Add(Connection:="URL;https://fclm-portal.amazon.com/reports/processPathRollup?reportFormat=HTML&warehouseId=ONT8&startDateDay=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&maxIntradayDays=1&spanType=Intraday&startDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&startHourIntraday=" & StartHour & "&startMinuteIntraday=0&endDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&endHourIntraday=" & EndHour & "&endMinuteIntraday=0&_adjustPlanHours=on&_hideEmptyLineItems=on&employmentType=AllEmployees", Destination:=Range("A1"))

     Selection = 2
     Formatting = None
     PreFormattedTextToColumns = True
     ConsecutiveDelimitersAsOne = True
     SingleBlockTextImport = False
     DisableDateRecognition = False
     DisableRedirections = False

      End With


      Sheets("FR").Select
      Cells.Select
      Selection.ClearContents
      With ActiveSheet.QueryTables.Add(Connection:="URL;https://fclm-portal.amazon.com/reports/functionRollup?warehouseId=ONT8&spanType=Intraday&startDate=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "T" & StartHour & ".000&endDate=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "T" & EndHour & ".000&reportFormat=HTML&processId=01003021", Destination:=Range("A1"))

      Selection = Summary
      Formatting = None
      PreFormattedTextToColumns = True
      ConsecutiveDelimitersAsOne = True
      SingleBlockTextImport = False
      DisableDateRecognition = False
      DisableRedirections = False

      End With

      Sheets("PR").Select
      Cells.Select
      Selection.ClearContents
      With ActiveSheet.QueryTables.Add(Connection:="URL;https://fclm-portal.amazon.com/reports/functionRollup?reportFormat=HTML&warehouseId=ONT8&processId=1003032&maxIntradayDays=1&spanType=Intraday&startDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&startHourIntraday=" & StartHour & "&startMinuteIntraday=0&endDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&endHourIntraday=" & EndHour & "&endMinuteIntraday=0", Destination:=Range("A1"))

      Selection = Summary
      Formatting = None
      PreFormattedTextToColumns = True
      ConsecutiveDelimitersAsOne = True
      SingleBlockTextImport = False
      DisableDateRecognition = False
      DisableRedirections = False

      End With

      Sheets("PV").Select
      Cells.Select
      Selection.ClearContents
      With ActiveSheet.QueryTables.Add(Connection:="URL;https://fclm-portal.amazon.com/reports/functionRollup?reportFormat=HTML&warehouseId=ONT8&processId=1003018&startDateDay=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&maxIntradayDays=1&spanType=Intraday&startDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&startHourIntraday=" & StartHour & "&startMinuteIntraday=0&endDateIntraday=" & StartYear & "%2F" & StartMonth & "%2F" & StartDay & "&endHourIntraday=" & EndHour & "&endMinuteIntraday=0", Destination:=Range("A1"))

     Selection = Summary
     Formatting = None
     PreFormattedTextToColumns = True
     ConsecutiveDelimitersAsOne = True
     SingleBlockTextImport = False
     DisableDateRecognition = False
     DisableRedirections = False

     End With

     End Sub

Oh. `selection` is a reserved word. You can't use that as a variable. Choose another name. And declare the variable. — findwindow, Apr 07 '16 at 23:00

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

Methods inside With ... End With have to be prepended with . This will throw an error for sure, so you have to fix quite a few lines of code. And Selection might not work even fixing this (I do not have Excel here to test it).

In addition, it is convenient to fully qualify your Ranges. See this answer, only as an example. This shows up once and again.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 08 '16 at 01:40

sancho.s ReinstateMonicaCellio

14,708
20
93
185

VBA Runtime Error 1004 “Application-defined or Object-defined error”

1 Answers1