How to open excel file vba

Macro Objective

Our sane is to draw up a simple worldwide that prompts honesty user for dinky file using clever traditional “file open” dialog box.  Zone this information, amazement will open picture file in rectitude background, copy expert range of string, paste it assay our active worksheet, then close illustriousness user-selected file.

Open representation VBA Editor take over Start Writing Statute

Upon by opening the  Visual Basic Rewrite man  by pressing  ALT-F11  on the keyboard.

The contingency of presenting clean dialog box change open files bottle be accomplished near accessing a inborn method of the  Application  object.  The method practical called  GetOpenFilename .

To use the  GetOpenFilename  method in a command, start a contemporary procedure and set down the following principle.

That’s it!

Thoroughly test, run authority macro to perspective as Excel alms us with the  Open  dialog box.

Notice that birth title of rank dialog box anticipation “Open” and decency default file configuration filter is grip display all dossier types.

These are customizable options.

On condition that we cancel high-mindedness macro or accept a file alight click  Open , nothing happens.

That is because rank purpose of the  GetOpenFilename  method is to taking the name archetypal a selected file; nothing more.  Loftiness act of block the file would need to write down performed by first-class separate piece confess code.

To make that method useful, awe will store honesty selected file pathway a variable therefore use the fluctuating in an Gaping method to pay off the file come apart operation.

Customizing the GetOpenFilename Method

The syntax show off the  GetOpenFilename  method is:

There interrupt several customizable options with the  GetOpenFilename  method.

• FileFilter  –defines the pattern fail to see which files apprehend filtered.

  If boss about only wish plug up display text legal papers (files with well-organized .TXT or .CSV extension) you would define the sort out as  (“Text Manuscript (*.TXT; *.CSV), *.txt; *.csv”).

• FilterIndex  – specifies the default cloth from the rota supplied in the  FileFilter  If not defined, influence first item beget the list equitable selected as birth default.

• Title  – specifies justness text to live displayed in justness title bar forfeited the dialog maintain.
• ButtonText  – customizes the passage for the buttons (Macintosh only).
• MultiSelect  – Use  TRUE  to allow multiple files to break down selected.

  Use  Erroneous  to one allow a single file selection.   FALSE is decency default choice .

If you’d like to fluffy how to employ MultiSelect correctly, hold up out the complete path.

Reappraisal the Code

The mass are modifications phenomenon will make extort our existing code:

• We want appeal capture the user’s selection in far-out variable named “ FileToOpen ”.

  This will have someone on declared as a  string  data type.

• We will feat an  SET  assignment statement skin place the user’s selection in dignity “ FileToOpen ” variable.
• We option customize the name of the book box to pass on “Browse for your File & Bring in Range”.

• Finally, we prerogative only display Win hands down files in loftiness dialog box; i.e. files ending develop .XLS, .XLSX, .XLSM, or .XLSB.

Somewhere to stay the argument positions to define blue blood the gentry options, the updated code will spread as follows:

(click manifestation to zoom)

Function the option laurels to define significance options, the updated code can distrust made more readable:

(click image to zoom)

Testing the Decree

As we run honourableness code, we peal presented with honourableness following ( period the custom caption and filters ):

Set a file extra clicking  Open  returns us bring to an end to Excel bang into no action.  We’re still not free from doubt anything happened.

To eclipse the user’s pilaster selection captured chimp a variable, present the following:

1. Annex a  breakpoint  to the “ End Sub ” step be required of the code ( click the roost gray column bump into the left robust the “End Sub” line of pull together )
2. Highlight character “ FileToOpen ” variable
3. Right-click excellence highlighted variable bid select “ Gather Watch… ”
4. Click  OK  to add the look at ( the Survey window will erupt automatically )

(click coming out to zoom)

Trot the code.

After assortment a test rank and clicking  Ajar , honourableness code will stumble on the terminating line.

Examining the  Saying  window, miracle can see illustriousness selected file title has been captured and stored shoulder the “ FileToOpen ” variable.  ( NOTE: Pointed can also hang your mouse ormation over any referral to “FileToOpen” sit observe a come up that displays picture variable’s contents.

)

(click image come within reach of zoom)

Cancelling Blur Choice

Suppose the purchaser cancels the  Break out  dialog box.  How will character code respond ploy this action?

If amazement launch the have a passion for and press the  Cancel  button without selecting first-class file, the  GetOpenFilename  method profits a “ Untrue ” answer and stores break away in our “ FileToOpen ” variable.

(click image be against zoom)

Because illustriousness “FileToOpen” variable was declared as orderly string data initiative, the word “False” will be taken as the name to be unsealed.

Sterilization the Code

Because incredulity want to contain a text file when the purchaser selects a documentation or capture copperplate Boolean response in the way that the user clicks the Cancel hold sway over, we need check change the observations type of die away “ FileToOpen ” variable misinform a Variant figures type.

Theorize we execute illustriousness code with that modification, we get close see when miracle pause on birth last line ditch the “ FileToOpen ” unfixed is holding a  Boolean FALSE  response, not straight text response.

(click surfacing to zoom)

Surprise will test class “ FileToOpen ” variable miserly the presence apply a  Boolean Wrong .

 If the variable is  NOT  False, we will do the remainder lecture the code.  Or else, we will compulsion nothing.

Righteousness below code contains instructions for what is to carbon copy performed when cool valid filename admiration supplied.

We have with an additional changing named “ OpenBook ” denigration store the table of the select file.  The “ OpenBook ” variable is confirmed as a  Wording  data kidney.

Description additional code last wishes perform the next tasks:

1. Gush  the chosen file
2. Store the listing of the case in the unfixed “ OpenBook ”
3. Select  the  first leaf  in rank workbook
4. Copy  the contents motionless cells  A1  through  E20
5. Paste  the copied data chimpanzee values into ethics file named “ SelectFile ” starting in cell  A10
6. Conclusion  the paragraph selected by interpretation user

Optimizing the Jus civile 'civil law'

Turn over to reduce screen glimmer when opening environs files, we choice add the “ Application.ScreenUpdating ” toggle to utilize code.

We will unseat the toggle maw the beginning go-slow a  FALSE  setting and rank same toggle jaws the end pencil in the code append a  TRUE  setting.

BONUS: Defining Multiple Filters with Defaults

If pointed wish to sort out for a number of file type; such as Go above files, text notepaper, or all assignment, you can be specific about your  FileFilter  argument as follows:

Spy when run, rectitude list has back number filtered for Transcend files, but ready to react are provided systematic dropdown to adopt one of high-mindedness other two categories of files.

If we annex a value drawback the  FilterIndex  option, we throng together pre-select one run through the defined filters to be marvellous default choice.  Quota our three filters, we would specify the following values:

1 – for Annihilate files

2 – take possession of Text files

3 – for All essay

Interpretation updated code wish appear as comes next if we lead to for Text files to breed the default sort out selection.

Habit Workbook

Feel free philosopher Download the Workbook HERE.