Import data from a file

Question

0.00/5 (No votes)

See more:

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 :

VB

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 ?

Posted 5-Jul-12 2:43am

Sumal.V

Updated 5-Jul-12 22:02pm

v2

Add a Solution

Comments

ZurdoDev 5-Jul-12 11:08am

Depends on the version of VB you are doing. Put a OpenFileDialog on your form and then use that instead.

perilbrain 5-Jul-12 11:41am

He/She is talking about VBA.....

ZurdoDev 5-Jul-12 12:04pm

You can still do forms in VBA.

2 solutions

Solution 2

See Exporting Data to Excel[^]

Posted 5-Jul-12 15:36pm

Pandvi

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

perilbrain · Accepted Answer · 2012-07-05T05:51:00

VB

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

This can be used this way............in VBA
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & File_Name,Destination:=Range("$A$1"))
.Name = Project1"
.FieldNames = True ..........