首页 文章

将txt文件导入excel; QueryTables

提问于
浏览
0

我试图将数据从TXT文件导入我的Excel工作表 .

它适用于下面的VBA宏,但唯一的问题是重新打开excel文件宏后试图查看txt目录,当它找不到它时,它给出了一个错误 .

我并不打算把这样的命令放在那里,但现在我不知道如何禁用它 . 你们知道我应该改变什么来禁用这个功能吗?

Sub test_9()
Dim jess916 As Variant, FullPath As String
Set jess916 = Application.FileDialog(msoFileDialogFilePicker)
With jess916
    .InitialView = msoFileDialogViewDetails
    .InitialFileName = ThisWorkbook.Path
    .Filters.Add "Open File ", "*.txt", 1
    .ButtonName = "Import file"
    .Title = " jess916c Search for .txt file to Import"
    If .Show = -1 Then
        FullPath = .SelectedItems(1)
Else:
        Exit Sub
    End If
End With
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & FullPath, Destination:=Range("A2"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 9
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub

1 回答

  • 0

    我使用On Error Goto来处理我的代码 . 见下文 .

    Sub ImportData()
        Application.ScreenUpdating = False
        Dim intChoice As Integer
        Dim strPath As String
    
        'only allow the user to select one file
        Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
        'make the file dialog visible to the user
        intChoice = Application.FileDialog(msoFileDialogOpen).Show
        'determine what choice the user made
        If intChoice <> 0 Then
            'get the file path selected by the user
            strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
        End If
        'Import data from file
        On Error GoTo errorHandler
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strPath, Destination:=Range("$A$1"))
            .Name = "MemoQ Data Range"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 1252
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    
        Application.ScreenUpdating = True
        Exit Sub
    
    errorHandler:
        Exit Sub
    End Sub
    

相关问题