VBA提示输入不带定界的DAT，添加新图纸，然后导入DAT

Question

代码提示用户选择一个Excel文件和五个不同的DAT文件。Excel文件加载到工作表上，然后应该为要导入的每个DAT文件添加新的工作表。Excel文件加载正确，但程序在第一次尝试导入DAT文件时出错。

错误："运行时错误"1004"：应用程序定义或对象定义的错误"。

这就是错误发生的地方：

ActiveSheet.QueryTables.Add(Connection:= _
    DIFN, Destination _

这是代码的其余部分：

' Prompt user for files
    CAFN = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
    DIFN = Application.GetOpenFilename("ESDI DAT File (*.dat), *.dat")
    FOFN = Application.GetOpenFilename("ESFO DAT File (*.dat), *.dat")
    FSFN = Application.GetOpenFilename("ESFS DAT File (*.dat), *.dat")
    IPFN = Application.GetOpenFilename("ESIP DAT File (*.dat), *.dat")
    PPFN = Application.GetOpenFilename("ESPP DAT File (*.dat), *.dat")
' Load Combined All
    Dim x As Workbook
    Dim y As Workbook
    '## Open both workbooks first:
    Set y = ActiveWorkbook
    Set x = Workbooks.Open(CAFN)
    'Now, transfer values from x to y:
    With x.Sheets("Sheet1").UsedRange
        'Now, paste to y worksheet:
        y.Sheets("Start").Range("A1").Resize( _
            .Rows.Count, .Columns.Count) = .Value
    End With
    'Close x:
    x.Close
    y.Sheets("Start").Name = "Combined All"
' Load DAT files
    ActiveWorkbook.Worksheets.Add.Name = "ESDI"
    With ActiveSheet.QueryTables.Add(Connection:= _
        DIFN, Destination _
        :=Range("$A$1"))
        .Name = "ESDI"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 9, 1, 9, 9, 1, 9, 9, 9)
        .TextFileFixedColumnWidths = Array(3, 7, 7, 2, 10, 8, 3, 8, 40, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Answer 1

这是为我工作的代码（感谢YowE3K和Scott Holtzman为我指明了正确的方向！）：

' Prompt user for files
Dim CAFN As String
Dim DIFN As String
Dim FOFN As String
Dim FSFN As String
Dim IPFN As String
Dim PPFN As String
CAFN = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
DIFN = Application.GetOpenFilename("ESDI DAT File (*.dat), *.dat")
FOFN = Application.GetOpenFilename("ESFO DAT File (*.dat), *.dat")
FSFN = Application.GetOpenFilename("ESFS DAT File (*.dat), *.dat")
IPFN = Application.GetOpenFilename("ESIP DAT File (*.dat), *.dat")
PPFN = Application.GetOpenFilename("ESPP DAT File (*.dat), *.dat")
' Load Combined All
Dim x As Workbook
Dim y As Workbook
'## Open both workbooks first:
Set y = ActiveWorkbook
Set x = Workbooks.Open(CAFN)
'Now, transfer values from x to y:
With x.Sheets("Sheet1").UsedRange
    'Now, paste to y worksheet:
    y.Sheets("Start").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With
'Close x:
x.Close
y.Sheets("Start").Name = "Combined All"

' Load DAT files
    ActiveWorkbook.Worksheets.Add.Name = "ESDI"
    MsgBox ">>>" & "TEXT;" & DIFN & "<<<"
    With Sheets("ESDI").QueryTables.Add(Connection:="TEXT;" & DIFN, Destination:=Range("$A$1"))
        .Name = "ESDI"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 9, 1, 9, 9, 1, 9, 9, 9)
        .TextFileFixedColumnWidths = Array(3, 7, 7, 2, 10, 8, 3, 8, 40, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh
    End With

Answer 2

假设您打开的文件是一个文本文件

With ActiveSheet.QueryTables.Add(Connection:= _
    DIFN, Destination _
    :=Range("$A$1"))

应该用代替

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & DIFN, _
                                 Destination:=Range("$A$1"))