×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Opening new workbooks in excel

Opening new workbooks in excel

Opening new workbooks in excel

(OP)
I am trying to open a new file within excel when running a macro. The file to open will have a different name each time so i require the user to input the filename. The file will always be a comma separated text file. I need the file to open in excel without having to go through the text import wizard each time.

I have got the code to automatically open the CSV if i know the filename but am having problems in allowing the user to select the file.

I would like to use the standard open file dialog box to do this. I can show the dialog using the code
 Application.Dialogs(xlDialogOpen).Show
and this works fine except that once the user selects the file excel tries to open it and launches the text import wizard.

Is there a way that i can use the standard dialog to return the filename but not open it? I can then use the code below to avoid the text import wizard running

Workbooks.OpenText Filename:= name_and_path_of_file, StartRow:=1, DataType:=xlDelimited, comma:=True

RE: Opening new workbooks in excel

Use the GetOpenFilename method to raise a dialogue that will return the file name but not open the file.  Once you have the file name use the WorkBooks.OpenText method to open the text file

RE: Opening new workbooks in excel

Expanding on cummings54's reply, here is some example code:

CODE

Sub GetFile()
Dim FPath As Variant
Dim FFilter As String

   FFilter = "Comma Delimited (*.csv),*.csv"
   FPath = Application.GetOpenFilename(FFilter, , "Select File To Open", "Open")
   If FPath = False Then Exit Sub
   'FPath contains path & filename of user-selected file
   
   'Your existing code here
   
End Sub


Regards,
Mike

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources