Thursday, May 26, 2011

How do you create folders with an Excel Spreadsheet macro?


At work I often find myself having to create multiple folders before beginning a Project. Many others may take the time consuming method of doing this by hand but you can actually save yourself a lot of time by using a simple VBA macro in an Excel Spreadsheet. One method of doing this is to start a new spreadsheet and save it as a macro-enabled workbook in the location where you want to create the multiple folders (such as C:\Work Directory\Parts List)

Next, in column A list all the names of the folders you want to create. Now, hold the "Alt" key down and press "F8" to open the Macros window. Enter "CreateFolders" and click the Create button which will open the VBA editor. You can copy and paste the following code:


Sub CreateFolders()

'create the folders where-ever the workbook is saved

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1

Loop

Next c

End Sub


 

Now all you have to do is highlight the cells and run the macro. Your folders are automatically created just like that! Save the macro and you can use it over and over again, saving you lots of time and impressing your fellow employees.

Wednesday, May 25, 2011

Apartment Search Spreadsheet and Shared Expenses Template Download

Update 6/11/12: Check out our new ULTIMATE Apartment Search Spreadsheet and Guide.

I'd like to apologize as I was having a little dispute with the site which hosts all of my Excel spreadsheet template downloads. The issue has been peacefully resolved but in the process it looks like all of the files I uploaded have been deleted. This means all the download links are probably broken. I have started the long process of re-uploading all of the files today but please be patient as it is going to take some time to get them all back up.

I decided to upload my two most requested files first, the Apartment Search Template and the Shared Living Expenses Spreadsheet. Let me know what other files you would like right away.

Apartment Finding Spreadsheet.xls download


Shared Apartment Expenses for 5 Roommates Template.xls download

Tuesday, May 17, 2011

How do you fix large file sizes in Microsoft Excel?

Are you wondering why your Excel file size is so huge? Is there a way to make large file sizes into smaller, more manageable sizes? As a matter of fact, there is! One method is to examine each separate object in your spreadsheet files so that you can see how it contributes to the objects' overall size. Here's how to do it:

  • Save your file
  • SaveAs a completely new name so that you can mess about with it
  • SaveAs Web page (choose 'Other Formats' in Excel 2007)
  • Open Explorer
  • Navigate to where you saved the file
  • Open the folder with the same name as your file
  • Sort descending by the file size
    • The largest objects will be named and at the top

This works best when you are dealing with multiple sheets. If you only have one sheet, the most likely cause is file formatting. My advice is to recreate the sheet with no formatting, then add the formatting to entire sections such as columns or large ranges.

  • Do not format a range of cells, and copy that formatting to other cells.
  • You can find out exactly how small your file could be by copying only the data to a new sheet and saving that.

Why does the file size get so huge? It could be due to a corrupt spreadsheet, which I will tell you how to fix in an upcoming post!

Tuesday, May 10, 2011

How do you open two Microsoft Excel sessions independently of each other?


Being able to open two session of Microsoft Excel indecently of each other is a very useful feature. What do I mean by this? Let's say I have "Book1" file currently open. Now I open another file namely "Book2". Although, the "Book2" opens up but I don't see it until I go into excel window menu----go at the bottom --- and there I see options for both files i.e. "Book1" &"Book2". So, every time I need to switch back and forth between these two files, I have to go window menu and from there choose which file I need to work with. In addition, if I do ALT+TAB to switch between Book1 & Book2, it does not work.

So basically, I need to find out how can I open both files i.e. Book1 & Book2 such that Both files are visible on the Window STATUS Bar ( the bar right next to "START" button on windows). This way I can easily click on any of the file I need to open. Or else I can use ALT+TAB to switch between any of the files. Currently, I don't see files visible on my status bar and I have to go to window menu to select the file I want.

There are a few methods to accomplish this.
Open Excel then open one workbook. Open Excel again then open other workbook. It works for me in 2003 to get two separate instances of Excel going that are independent of one another.

 

Open Excel twice and then open Book 1 in one Excel and Book 2 in the other. Then go to the desktop toolbar and right click. Select tile windows vertically and the two versions should be side by side.
I was able to get it done by going into Tools--Options--View --click on Windows Task bar.
In Excel 2007, Click the Office button -> Excel Options -> Advanced.
Under General, check 'Ignore other applications that use Dynamic Data Exchange'.
Excel 2010: Go to...
Excel Options | Advanced and under "Display" checkmark "Show all windows in the Taskbar"'


From Mr. Excel Forum


Monday, May 2, 2011

How do you resize Mircosoft Excel’s sheet tabs?

If your eyesight isn't what it used to be, you may want to increase the size of the sheet tabs displayed in your Excel workbooks.

This parameter is determined by a Windows system-wide setting. To change it, select Start, Settings, Control Panel and double-click Display. In the Display Properties dialog box, click the Appearance tab, and choose Scrollbar from the Item list. Adjust the sheet tabs until they're the size you want. Be aware that this setting affects the scroll bars in ALL OF YOUR APPLICATIONS.