Vergel’s Excel and Visual Basic Blog

How to loop through files in a Folder

Here's a simple routine to loop through all the files in a particular folder. It places all file names in a collection object. You can then do whatever you want with the collection. This particular example lists the file names in column A of the active sheet.

How to Open Excel Files in a New Application Instance

UPDATE: The instructions below work if you are using Windows XP. If you are using Windows 7 or Vista, you can follow the instructions here.

By default, when you try to open an Excel file by double-clicking it in Windows Explorer, it will open in an already existing instance of Excel. But sometimes, you want it to open in a new instance and even make it the default behavior.

The following steps will allow you to do that.

Continue reading How to Open Excel Files in a New Application Instance...

How to Autofit Column Widths with VBA

Somebody asked me for VBA code that will automatically adjust column widths to fit the cell contents. It's actually very simple and all you need is one function call. You simply need to call the AutoFit method.

If you wanted to autofit the row heights, you'll need something like this

Just note that for autofit of rows to work, you'll need to have the "Wrap Text" setting turned on. To do that, you need to select the range you want to autofit and from the main menu, go to Format->Cells. Then in the Alignment tab, tick the checkbox for "Wrap Text".