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