none
Querytable.Add error in 2013 but works in 2010 RRS feed

  • Question

  • I have a macro that imports data from our mainframe into an Excel sheet using Querytables.add. This macro works fine in 2010 but everytime I run the exact same code using Excel 2013 (Windows 7) I receive the error Sorry, we couldn't open 'https://mymainframepath/mvsds/'TMP.MYFILE.ONTHE.MAINFRAM''. The code is below.  Is there something I need to change between using Excel 2010 and Excel 2013?

        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;https://mymainframepath/mvsds/'TMP.MYFILE.ONTHE.MAINFRAM'" _
            , Destination:=Range("$A$1"))
            .Name = "'TMP.MYFILE.ONTHE.MAINFRAM'"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    Any help in clearing this up is greatly appreciated!

    Tina

    Friday, July 8, 2016 6:30 PM

All replies

  • Hi Tina List,

    did you got the following error box after the error," Sorry, we couldn't open 'https://mymainframepath/mvsds/'TMP.MYFILE.ONTHE.MAINFRAM''.

    if so then please verify that it does not currently used by any other program when you try to access it.

    Also try to comment the line below just for testing purpose and try to run the code again.

    .Refresh BackgroundQuery:=False

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 11, 2016 1:00 AM
    Moderator