Type mismatch macro for external data

Question

Hello need some help with the below code but an explanation in what I am needing might help you.

I am creating a macro that takes external data( FX rates) and places them down in a specified range on sheet1. sheet2 has just a little sum conversion using the FX rate from sheet1 to give GBP rate. This will live conversions when people fill in expenses for me on a project.

My issue is making the macro select the correct sheet. I have come up with the below however. i'm now getting a runtime error 13.type mismatch.

before it just selected the activesheet and not the sheet I wanted.

hope you guys can help. All new to VBA and i cannot find right answer when researching. so any help will be greatly appreciated.

regards. Ross

Sub fxRate()
    '
    ' fxRate Macro
    '

    '
    Set activateFXSheet = Sheet1.Activate
    Set FXSheet = activateFXSheet.QueryTables.Add(Connection:= _
        "URL;http://www.x-rates.com/table/?from=USD&amount=1",   Destination:=Range( _
        "$C$4:$E$14"))

    With FXSheet
        .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 = "1"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

score 1 · Answer 1 · answered Feb 14 '18 at 11:26

1

Here you go for the ActiveSheet:

Sub fxRate()
    Sheet1.Activate
    Set activateFXSheet = ActiveSheet
End Sub

But using ActiveSheet and etc is not a good idea in VBA: How to avoid using Select in Excel VBA

answered Feb 14 '18 at 11:26

Vityata

42,633
8
55
100

score 1 · Answer 2 · answered Feb 14 '18 at 11:28

You don't need to select or activate a worksheet in order to reference or modify/update it.

Sub fxRate()
    With Sheet1.QueryTables.Add(Connection:="URL;http://www.x-rates.com/table/?from=USD&amount=1", Destination:=Range("$C$4:$E$14"))
        .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 = "1"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

Type mismatch macro for external data

2 Answers2