Daily Archives: 22 August 2012

More Dynamic Excel King

Posted on by
Reply

Tips from Excel King

Make your macros more dynamic!

Today's tip is about how to easily create a macro that allows user select the Excel file / text file / ASCII file etc to be opened. You may also examples of how you can jump between multiple open files.
When you create macros that perform some more advanced operations, it is always good to think dynamically – ie how outset builds flexibility into your macros.

A clear advantage of this approach is that the Excel files as the macro import not need to have the same name on each run of the macro.

The procedure is as follows:

1. Take in the name of what file.
The method GETOPENFILENAME in VBA open dialog “Open File” or “Open File”. This box allows the user to easily point to a wise file.

2. Read in the name (path) in a variable.

3. Use variable to open file.

4. Carry it as desired with the file.

All of the above is managed via simple VBA code that you can easily include in your macros.

Maybe you get to experiment with the code a few times before you fully understand the procedure, but once you've learned the example, you will surely have a significant benefit in the future.

You can find the sample code in the section on GetOpenFilename on Excelkungen.com.

He is truly the king of Excel…Excel king..
Be Careful Out There
Anders