How to organize images automatically in an Excel file using a macro?

If you add multiple images at once to an Excel file, Excel will try to squeeze them in once cell. It will probably look like the screenshot below.

Instead of manually resizing and relocating these images, you can use the macro below to automatically organize all images in milliseconds.

Sub organizeImages()
     lastTop = 0
     staticHeight = 500

     For Each pic In Worksheets("Sheet1").Pictures
          pic.Top = lastTop
          pic.Height = staticHeight
          lastTop = lastTop + pic.Height + 15
          pic.Left = 0
     Next pic
End Sub

Images resized and relocated automatically:

How to create custom shortcuts to jump on the first, last or a specific worksheet in Excel by using macros

Imagine you have dozens of worksheets in an Excel file. In order to view a specific one, you will need to scroll right until finding that worksheet. Let’s say you finally find it. In order to come back, you will have to spend a similar amount of effort.

By using the macros below, you can navigate between worksheets easily. Just press the shortcut and Excel will take you there.

Shortcuts

  • Ctrl + R: Go to first worksheet
  • Ctrl + L: Go to last worksheet
  • Ctrl + T: Go to a specific worksheet (It will ask you a number)

Macros

Sub JumpRequirements()
    Sheets(2).Activate
End Sub

Sub JumpLast()
    Sheets(Sheets.Count - 2).Activate
End Sub

Sub JumpSpecificTab()
    On Error GoTo NotValidInput
        number = InputBox("Requirement Number:")
        Sheets(number + 4).Activate
    NotValidInput:
      'MsgBox ("Invalid value")
End Sub

In order to map shortcuts with macros, click “Options” button in “Excel ribbon > Developer > Macros” window and define the shortcut you want to use for that particular macro.

You don’t see “Developer” toolbar in Excel? Go to “File > Options > Customize Ribbon” and select “Developer” on the right-hand side list:

Solved: “XSLT compile error. ‘wsp’ is an undeclared prefix.”

If you upgrade an XSLT file to a newer version manually (by changing meta tags), you may run into these error messages when you try to open corresponding XML file:

XSLT compile error
‘wsp’ is an undeclared prefix. Line 27, position 12
‘wx’ is an undeclared prefix. Line 558, position 44

Root Cause

Microsoft announced a new XML format called Office Open XML (OOXML or OpenXML) in Microsoft Office 2007 products. Some XML keywords such as wsp and wx were depreciated with this new format. Therefore, when you upgrade your XSLT file, you’ll probably run into some syntax issues.

Solution

You need to replace those depreciated keywords with their descendants. It takes some time to find newer keywords. I had to deal with wsp and wx in my file. I replaced them with w. For example:

  • w:rsidTr instead of wsp:rsidTr
  • w:rsidP instead of wsp:rsidP
  • w:sz instead of wx:bdrwidth

Recommendation

The solution above might be exteremly tedious if you’re dealing with a big XSLT file. You may want to find an easier way to upgrade your XSLT files. I’d recommend this approach:

  1. Copy your content into a Microsoft Office version newer than 2003
  2. Save it as XML
  3. Open this XML file in XML Notepad
  4. Save it as XSLT

You will have an upgraded XSLT after these steps. You don’t need to upgrade your files manually any more.

References