VBA Code - Extracting data from URL

[uzzimacro]

Hi All,

I am trying to simplify a code on Excel VBA. Im a beginner in VBA and recorded a macro however im trying to learn from it but VBA adds a lot of useless stuff to it im told. Could any simplify the below code? All im doing is coding it in VBA so it goes to a website, extra an Exchange rata data table into excel, copy and pasts the 2 Exchange rates I want into specific cells and then delete the imported data. Appreciate the help.

Sub AnotherOneTestRates()
'
' AnotherOneTestRates Macro
'
'
Sheets("Data Reports").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.x-rates.com", _
Destination:=Range("$AB$32"))
.Name = "www.x-rates"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=3
Range("AC36").Select
Selection.Copy
Range("W4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AD36").Select
Application.CutCopyMode = False
Selection.Copy
Range("W5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AC32:AG37").Select
Selection.ClearContents
Range("W10").Select
End Sub

 

[baitmaster]

Try

Sub AnotherOneTestRates()
With Sheets("Data Reports")
    With .QueryTables.Add(Connection:="URL;http://www.x-rates.com", Destination:=Range("$AB$32"))
        .Name = "[URL="http://www.x-rates"]www.x-rates[/URL]"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
    .Range("W4").Value = .Range("AC36").Value
    .Range("W5").Value = .Range("AD36").Value
    .Range("AC32:AG37").ClearContents
End With
End Sub

Code is untested. You can probably also delete any setting that shows "=False"

 

[uzzimacro]

Thanks! easier to learn from and also shorter! and removing the false doesn't work but thanks anyway

 

[baitmaster]

To be clear, recording a macro picks up lots of settings that aren't always necessary, as the default setting is usually false for these anyway. I can't think of a specific occasion when I've deleted such settings and caused a problem as a result. For example, I suspect that if you remove the entire following line, you'll not notice a difference

.RowNumbers = False

I might be wrong about this in some cases but I can't think of examples. The benefit would be simplification of your code