从文件导入数据

问题描述

大家好，我正在尝试使用以下命令从excel中的文件导入数据:
数据->来自文本->然后选择我的文件.

为此记录的宏是:

Hi all, I''m trying to import data from a file in excel using :
Data -> From Text -> And select my file.

The macro recorded for this is :

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Desktop\Project1.csv",Destination:=Range("$A$1"))
        .Name = Project1"
        .FieldNames = True ..........       

这总是从相同的路径和文件名获取数据，如何更改宏以使宏打开文件对话框并让用户选择所需的文件?

This always gets the data from the same path and file name, how do I change the macro such that the macro opens up the file dialogue and let the user select the required file ?

推荐答案

这总是从相同的路径和文件名获取数据，如何更改宏以使宏打开文件对话框并让用户选择所需的文件?

This always gets the data from the same path and file name, how do I change the macro such that the macro opens up the file dialogue and let the user select the required file ?

Sub getFile()
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
    Dim File_Name

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Allow the selection of multiple files.
        .AllowMultiSelect = False

        'Use the Show method to display the file picker dialog and return the user's action.
        'If the user presses the action button...
        If .Show = -1 Then
            File_Name = .SelectedItems(1)
            MsgBox File_Name


        'If the user presses Cancel...
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
End Sub

可以通过这种方式使用.........在VBA中
使用ActiveSheet.QueryTables.Add(Connection:= _
文本;" & File_Name，Destination:= Range("

This can be used this way............in VBA
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & File_Name,Destination:=Range("

这篇关于从文件导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！