Querytable.Add错误在2013年，但在2010年工作 [英] Querytable.Add error in 2013 but works in 2010

问题描述

我有一个宏，使用Querytables.add将数据从我们的主机导入Excel表格。这个宏在2010年工作正常，但每次我使用Excel 2013（Windows 7）运行完全相同的代码我收到错误抱歉，我们无法打开'https：//mymainframepath/mvsds/'TMP.MYFILE.ONTHE.MAINFRAM' "。
代码如下。 在使用Excel 2010和Excel 2013之间是否需要更改？

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?

   使用ActiveSheet.QueryTables.Add（连接：= _

       " 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 =数组（2,2,2,2,2,2,2,2,2,2）

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery：= False

   结束

    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

推荐答案

您好
Tina List，

错误后，您是否收到以下错误框？抱歉，我们无法打开'https：//mymainframepath/mvsds/'TMP.MYFILE.ONTHE.MAINFRAM''。

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

问候

Deepak

这篇关于Querytable.Add错误在2013年，但在2010年工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！