Change Many OLE Linked Charts in a PowerPoint deck

I have a PowerPoint deck with 60+ OLE/ActiveX/.Net linked Excel charts. I want to change the name/location of the Excel file. PowerPoint 2013 handles this very poorly. You have to go to File/Edit Links to Files and edit each link individually.

Here’s how to do this in VBA:

' *************************************************************************************
' You'll have to edit this function any time you want to rename your linked Excel file.
' Then run ChangeAllLinks.
' *************************************************************************************
Function TransformLinkedFilename(ByVal sourceName As String) As String
	' Today, I am renaming all .xlsx to .xlsm
	' Astonishingly, the file name appears more than once in the source name, so you
	' have to let the Replace replace multiple times.
	TransformLinkedFilename = Replace(sourceName, ".xlsx", ".xlsm", 1, -1)
End Function


' *************************************************************************************
' I have a PowerPoint with a million OLE linked charts in it.
' I want to rename/move the linked spreadsheet(s).
' The GUI method for doing this is labor intensive.
' *************************************************************************************
Sub ChangeAllLinks()
	Dim aSlide As Slide
	Dim aShape As Shape
	Dim oldName As String
	Dim newName As String
	
	For Each aSlide In ActivePresentation.Slides
		For Each aShape In aSlide.Shapes
			If (aShape.Type = msoLinkedOLEObject)  Or (aShape.Type = msoChart) Then
				oldName = aShape.LinkFormat.SourceFullName
				newName = TransformLinkedFilename(oldName)
				Debug.Print "From:" & oldName
				Debug.Print "To:  " & newName
				'Stop
				aShape.LinkFormat.SourceFullName = newName
			End If
		Next
	Next
	MsgBox "Done"
End Sub

PowerPoint and Automatic Update of Linked Documents

When I open some PowerPoint decks, I get a warning dialog about linked documents, and I don’t want to get it. (Or vice versa.)

The message title is: Microsoft PowerPoint Security Notice

It has a bolded title: Microsoft Office has identified a potential security concern.

It goes on to say: This presentation contains links to other files. If you update the links…

It has buttons: “Update Links” and “Cancel”

To change the behavior, you have to go to the Edit Links window. To get there, click File (on the Ribbon), and on the Info tab, click “Edit Links to Files”. You’ll see an “Automatic Update” checkbox. If this is set, you’ll get the dialog. If this is clear, you will not get the dialog. If you have eleventy-three links, you will have to clear (or set) every file individually. (e.g. Select the first link, clear/set the checkbox; select the next link and clear/set; select the next link and…)

If you clear it, you might want to create a VBA macro that includes:

ActivePresentation.UpdateLinks

… and put a button for that macro on your Quick Access Toolbar. If you customize the QAT, you’ll probably want to change a setting on the customize dialog to customize it for the current document only.

CAUTION: I have found that UpdateLinks works sometimes and other times it just returns without doing anything. The following code can be used to set ALL link types. Call it with either ppUpdateOptionAutomatic or ppUpdateOptionManual.

Sub SetOleLinkTypes(ByVal LinkType As Integer)
    Dim aSlide As Slide
    Dim aShape As Shape

    For Each aSlide In ActivePresentation.Slides
        For Each aShape In aSlide.Shapes
            If aShape.Type = msoLinkedOLEObject Then
                aShape.LinkFormat.AutoUpdate = ppUpdateOptionAutomatic
            End If
        Next
    Next
    MsgBox "OLE link types updated"
End Sub

PowerPoint Updates Part of a Linked Excel Chart But Not the Whole Chart

OLE/ActiveX/.Net in Microsoft Office is great demo-ware, but when you try to use it for real work, it is awfully ‘fiddly.’ I had a problem where I put linked Excel charts into a PowerPoint deck and when I told PowerPoint to refresh the charts from Excel, it refreshed only part of the chart.

I created the links by:

  • Going to Excel 2013 and copying the chart.
  • Going to PowerPoint 2013, right-clicking, and choosing the Paste Option “Keep Source Formatting & Link Data”.
    • This should paste the chart, and apply Excel’s formatting, and use “live” data from the spreadsheet. It uses live data (chart lines) but not chart title or formatting.
    • When you paste the chart, it pastes in the current chart title and formatting and it does use them – only after the paste. When you later change the formatting or the chart title, it continues to apply the Excel formatting that was in effect when you pasted the link.
    • In other words it amounts to “paste a link to the data” and “apply the current source formatting.”

If this happens to you, you’ll have to re-paste your charts – once – using a different method. After that, your source formatting (including the chart title) will be dynamically updated.

Here’s the magic way to paste/link and get it to do what you want:

  • Go to Excel and copy the chart.
  • Go to PowerPoint and select the Home tab on the Ribbon.
  • Find the Paste button on the ribbon. (I looks like a clipboard. It is still there! I had to look twice for it.)
  • Click the drop-down arrow on the Paste button.
  • Click the words “Paste special”.
  • Choose “Paste link”.
  • Choose “Microsoft Excel Chart Object”.
  • Press OK.

Related VBA code follows. It is handy to add this to your PowerPoint deck, and then customize the Quick Access Toolbar to add buttons for these subroutines.

Sub RefreshLinks()
    ActivePresentation.UpdateLinks
    MsgBox "Refreshed"
End Sub

Sub PasteSpecialExcelChart()
    Call Application.ActiveWindow.View.Slide.Shapes.PasteSpecial(DataType:=ppPasteChartObject, _
        DisplayAsIcon:=msoFalse, Link:=msoTrue)
End Sub


Sub PasteSpecialExcelChartAndPosition()
    Dim sr As ShapeRange
    Dim currentSelection As Shape
    Set sr = Application.ActiveWindow.View.Slide.Shapes.PasteSpecial(DataType:=ppPasteChartObject, _
        DisplayAsIcon:=msoFalse, Link:=msoTrue)

    ' The just-pasted item automatically becomes the selected item.

    Set currentSelection = ActiveWindow.Selection.ShapeRange(1)

    currentSelection.LockAspectRatio = msoFalse
    
    ' Dimensions are in points, at 72 points per inch
    currentSelection.Left = 18#

    currentSelection.Height = 72 * 3#
    currentSelection.Width = 72 * 9.5
    currentSelection.Linkformat.AutoUpdate = ppUpdateOptionManual  ' or ppUpdateOptionAutomatic

    answer = MsgBox("Put it on the top of the page (yes) or the bottom (no)?", vbYesNo)
    If answer = vbYes Then
        currentSelection.Top = 60#
    Else
        currentSelection.Top = 276#
    End If
End Sub

Image Transparency

I hate transparency in images. More accurately, I hate having to make it happen – I think it is a fine concept.

Turns out that Preview on the Mac handles this. From http://scripting.com/2014/11/27/aHiddenMacPaintProgram.html

  • Copy an image to the clipboard.
  • Launch Preview.app.
  • Choose New from Clipboard from the File menu.
  • When the image window opens, click on the toolbox icon to enable the editing features.

Things you can do:

  • Resize/scale an image.
  • Make the background transparent.
  • Draw an arrow on the image.
  • Save as either GIF, JPG or PNG.

Step-by-step with pictures is at http://osxdaily.com/2013/01/27/make-a-transparent-image-png-or-gif-easily-with-preview-for-mac-os-x/

Chrome: Click to Play Does Not Stop Flash

Q: I do not like videos to auto-play. I use Google Chrome. I went to Settings » Advanced Settings » Content Settings » Plug-ins and I set “Click to Play”, but Flash videos still start automatically. People in the library glare at me. What can I do?

A: Follow the same path, and go one step further – click “Disable individual plug-ins.” Find the entry for Flash and ensure that “always allowed” is NOT set.

It is somewhat curious that we find an option to ENABLE Flash even though it has otherwise been disabled, under the label “Disable individual plug-ins”, but that’s where it is.

Hide User From Windows 7 Welcome/Login Screen - Does Not Work

There are a million and one tutorials on how to hide an account from the Windows 7 (and Windows Vista and Windows 8) Welcome screen. They all say to create a DWORD under

HKLM\Software\Microsoft\Windows NT\CurrentVersion\WinLogon\SpecialAccounts\UserList

where the DWORD name is the account name to hide and the DWORD value is zero.

Sometimes it doesn’t work. The trick is that Windows (like Unix/Linux) has two names for an account. There is the ‘user name’, which is often something like billsmith, and the ‘user full name’ which is something like ‘William Smith, III’. The full name is really just descriptive text, and the actual user ID is billsmith (in this case).

Windows shows the user full name on the Welcome screen. If you make the DWORD name match the user full name (e.g. ‘William Smith, III’) it will NOT hide the account. You must use the user name (e.g. ‘billsmith’).

But… If you are using the Home versions of Windows 7, there is no way to edit the user name – just the user full name. Fortunately, there is a way to show the user name. Open a Command prompt, and type

net user

This will list all of the user names (not the user full names). Be sure that the DWORD name matches one of these names, not the names you see on the Welcome screen.

Download YouTube Videos

I don’t do this often, but once in a while I need to download a YouTube video.

(These are the steps for a Mac. Windows steps will be similar, but not identical.)

  • Open the YouTube page for the video in your browser.
  • Copy the page URL.
  • Start VLC
    • File » Open Network
    • Paste the URL and click Open.
    • Wait for the video to start playing. It takes a while to start.
    • Window » Playlist
    • Right-click on the record for the video in the Play List. Choose Media Information.
    • On the Codec Details tap, right-click and copy the Location field.
      • If the codec is VP8, you might want to save it as .webm. Chrome and VLC can play this. For Windows users, they’ll either need to install the VP8 codec or you will have to convert it to mp4 for them.
    • Paste the clipboard into the browser’s location field, press Enter, and start the video playing.
    • Right click on the video and choose Save Video As.

Obsolete content follows. I stopped using DownloadHelper because it looks like it omits the top quality stream.

Here’s how I get HTML 5 video from YouTube:

  • Use Firefox and https://addons.mozilla.org/en-US/firefox/addon/video-downloadhelper/
    • Browse to the YouTube page
    • When the icon for DownloadHelper animates (it looks like three color dots, spinning in a circle), click the down-arrow to the right of the color dots.
    • Choose the highest non-HQ value. For example, it showed the following and I chose 720p
      • HQ5 (I’ve read that this is mostly for AUDIO downloads.)
      • Mobile
      • Medium
      • 720p
      • HQ36 (I’ve read that this is mostly for AUDIO downloads.)
    • After choosing which format to download, you can choose the method of download. I went with “Download”. It downloaded an mp4 file.

TiddlyWiki - Hide Tiddler From Lists

I can never find this when I search for it. To hide a TiddlyWiki tiddler from lists such as the Timeline, add the tag “excludeLists”.

Keyword fodder: hide exclude “do not list tiddler”

Set Up an OpenStack Lab VM

I’m setting up a personal lab OpenStack server.

Install Ubuntu 14.04 Desktop in a VM [2014.10.31]

  • Download http://www.ubuntu.com/download/desktop
  • Using VMware Fusion 7
    • New VM:
    • Choose: Install from disc or image
    • More Options
    • Create a custom virtual machine
    • Linux, Ubuntu 64-bit
    • Create a new virtual disk (20 GB)
    • Do not auto-run it.
    • Mount the ISO on the virtual DVD and start the VM
    • Respond to the Ubuntu prompts:
      • Install Ubuntu
      • Choose “Download updates while installing” and NOT “Install this third-party software” and press Continue
      • Erase disk and install Ubuntu. Press Install Now.
      • Choose location = “New York” (or your own time zone) and press Continue.
      • Keyboard layout = English(US). I also chose the Dvorak layout. Press Continue.
      • Enter your name (kevin), your computer name (openstack-kpk), user name (kevin), password (my standard password), choose login automatically. Press Continue.
      • When it came time for it to restart, it locked up and I had to manually restart the VM.
    • Update Ubuntu to pick up all patches
    • sudo apt-get install git
    • From the Virtual Machine (vmware) menu, choose Install(Reinstall) VMware tools, and wait for the DVD to auto-mount on the Ubuntu desktop.
    • Copy the VMwareTools…gz to /tmp
    • cd /tmp
    • tar -zxvf *.gz
    • cd vmware-tools-distrib
    • sudo ./vmware-install.pl
      • Accept the default at all prompts.
    • sudo reboot
    • Confirm that clipboard sharing between the host and VM works. (Copy some text from a host window into a guest window.)

Install Devstack: [2014.10.31]

  • git clone https://github.com/openstack-dev/devstack.git
  • cd devstack
  • ./stack.sh
    • Be careful: for the FIRST password, the one you choose will become your mysql root password for the whole VM, not just openstack.
    • For the rest of the passwords, ‘nova’ is a fine password for a development environment.
    • (I inadvertently set up mysql root and all the openstack passwords to match my Linux login password, which would be a problem if I weren’t using a private, inaccessable VM.)
  • At the end of stack.sh, it will say something like the following. Be sure to capture the URLs.

    Horizon is now available at http://192.168.137.130/ Keystone is serving at http://192.168.137.130:5000/v2.0/

Interesting openstack commands:

  • ./stack.sh
  • ./unstack.sh
  • ./rejoin_stack.sh

I finished the setup, now I want to tear down devstack and start over:

  • If devstack is running:
    • cd devstack
    • ./unstack.sh
  • Just “rm -rf devstack” and repeat the “Install Devstock” instructions.
  • Note: Be sure to use the same mysql password you used the first time, because the first install ran your mysql install and set its root password.

Helpful URLs:

  • http://www.rushiagr.com/blog/2014/04/03/openstack-in-an-hour-with-devstack/
  • https://lists.launchpad.net/openstack/msg24529.html
  • http://www.rushiagr.com/blog/2013/05/27/cinder-on-devstack-quick-start/
  • http://devstack.org/

Some hints:

  • Passwords are saved in devstack/localrc
  • Default users are ‘admin’ and ‘demo’