CornFused
06-17-2018, 02:05 PM
I am trying to update a Macro that I recorded so that content from the lines in the spreadsheet will update the web query target but have run into a a few errors. I've sorted through several of them but the current issue is a Runtime error. The following is the code for my query:
Sub GetAllPkgInfo()
Sheets("AllPkgs").Select
Range("A2").Select
Do Until ActiveCell.Value = ""
Call PkgInfo1
Loop
End Sub
Sub PkgInfo1()
'
' PkgInfo1 Macro
' Import RHEL package information from RPMFind
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Selection.Copy
Sheets("WebQuery").Select
ActiveSheet.Cells.Clear
Range("$G$1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add( _
Connection:="URL;https://rpmfind.net/linux/rpm2html/search.php?query=" & _
Range("$G$1").Value, Destination:=Range("$A$1"))
.CommandType = 0
.Name = "search.php?query=abrt_1"
.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 = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(1, -5).Range("A1").Select
Selection.Copy
Sheets("AllPkgs").Select
ActiveCell.Offset(0, 1).Range("CombinedPackages[[#Headers],[Column1]]").Select
ActiveSheet.Paste
Sheets("WebQuery").Select
ActiveCell.Cells.Select
Application.CutCopyMode = False
Selection.QueryTable.Delete
Selection.ClearContents
Sheets("AllPkgs").Select
ActiveCell.Offset(1, -1).Range("CombinedPackages[[#Headers],[Column1]]").Select
End Sub
The error message I receive states "Runtime error '1004': Paste Method of Worksheet Class Failed" and highlights the first code instance of
ActiveSheet.Paste
Can anybody help me get straightened out please?
Sub GetAllPkgInfo()
Sheets("AllPkgs").Select
Range("A2").Select
Do Until ActiveCell.Value = ""
Call PkgInfo1
Loop
End Sub
Sub PkgInfo1()
'
' PkgInfo1 Macro
' Import RHEL package information from RPMFind
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Selection.Copy
Sheets("WebQuery").Select
ActiveSheet.Cells.Clear
Range("$G$1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add( _
Connection:="URL;https://rpmfind.net/linux/rpm2html/search.php?query=" & _
Range("$G$1").Value, Destination:=Range("$A$1"))
.CommandType = 0
.Name = "search.php?query=abrt_1"
.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 = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(1, -5).Range("A1").Select
Selection.Copy
Sheets("AllPkgs").Select
ActiveCell.Offset(0, 1).Range("CombinedPackages[[#Headers],[Column1]]").Select
ActiveSheet.Paste
Sheets("WebQuery").Select
ActiveCell.Cells.Select
Application.CutCopyMode = False
Selection.QueryTable.Delete
Selection.ClearContents
Sheets("AllPkgs").Select
ActiveCell.Offset(1, -1).Range("CombinedPackages[[#Headers],[Column1]]").Select
End Sub
The error message I receive states "Runtime error '1004': Paste Method of Worksheet Class Failed" and highlights the first code instance of
ActiveSheet.Paste
Can anybody help me get straightened out please?