Solved

macro vb open dialog and read excel file

Posted on 2011-03-23
2
561 Views
Last Modified: 2012-05-11
i'm new to writing macro in excel. can someone let me know how to write little macro that pops up open dialog pop to allow users navigate to whatever folder and then select an excel file. After selected the file and hit ok, what i need to do is read through the file and then put it into the right format and save it as new csv file with comma delimiter.
any help really appreciate. thanks
0
Comment
Question by:StewSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 20

Accepted Solution

by:
darbid73 earned 500 total points
ID: 35199410
here is something you can start with

Sub Main()

    '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

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

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
                MsgBox "The path is: " & vrtSelectedItem

            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

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

End Sub

Open in new window


This is not my example it is from MSDN

To customize your filedialog like you want you need to look at the properties section.  There you will see all the things you want so that it looks like you want.

You can for example add a title, open the dialog to a special folder etc
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35203177
Can you pls post a sample file
- opening the file is easy
- your format requirements are unknown
- saving as csv is easy

Cheers

Dave
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question