Monthly Archives: October 2009

Extracting hyperlink destinations from Excel (Document Library item URLs)

I recently needed the destination URL for several hundred items across layers of nested folders in a giant SharePoint Document Library.    Right clicking with “copy link location” just wasn’t going to work here.   The datasheet view, Excel, and Access all came to mind as possible tools.   In then end a quick Excel UDF (User Defined Function) helped get the job done.   This is easily done against SharePoint Document Libraries because the default Title column links to the file item URL.

  1. Choose “Export to Spreadsheet” from your document library
  2. Open the .IQY file when prompted
  3. Press Alt+F11, then Insert > Module
  4. Paste in this code:
  5. Function GetAddress(HyperlinkCell As Range)
        GetAddress = Replace _
        (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
    End Function
  6. Save the workbook.   Note:  You might be prompted to save as XLSM for a macro enabled workbook, change the file type, and save.
  7. Use the =getaddress(cell) function to see the URLs  (screenshots below)

 

 

clip_image002

clip_image004

clip_image006

clip_image008

clip_image010

clip_image012

clip_image014

clip_image016

clip_image018

clip_image020

 

Sources Cited

http://www.ozgrid.com/VBA/HyperlinkAddress.htm

Function GetAddress(HyperlinkCell As Range)

    GetAddress = Replace _

    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

End Function

MS Virtualization Quick Reference

For myself and friends working with Microsoft virtualization technologies I decided to write a quick reference guide comparing and contrasting the tools available today.  This area is changing so quickly it’s hard to stay current.  Hope you find it helpful.

Virtual PC 2007

Key Features: Simple, easy, and abundant.   Friendly GUI interface, drag and drop files across Host–>VM desktops.   With so many Windows XP desktops out there there is a favorite for learning and labs.

Host O/S: Windows XP mostly.  Older systems.

Guest Capability: 32-bit only

Keyboard Shortcuts: [Right Alt] to release (if lacking VM additions).   [Ctrl+Alt+Break] will toggle to/from full screen.

Download Link:

http://www.microsoft.com/downloadS/details.aspx?FamilyID=04d26402-3199-48a3-afa2-2dc0b40a73b6&displaylang=en#filelist

Virtual Server 2005 R2 SP1

Key Features: Web based interface, easy remoting.  More features (VHDMOUNT, VMC editing).  Dominant in data centers.

Host O/S: Windows XP / Server 2003.   Older systems.   Can also run on Server 2008 for backward compatibility and access to VHDMOUNT utility.

Guest Capability: 32-bit only

Keyboard Shortcuts: [Right Alt] to release (if lacking VM additions).    Full keyboard shortcut listing here.

Download Link:

http://www.microsoft.com/windowsserversystem/virtualserver/

Hyper-V in Windows Server 2008

Key Features: FAST!!   Direct access to bare metal.   Latest in core VM tech from Microsoft.   Easy snapshots, rich settings editor, VHD compression tool.

Host O/S: Windows Server 2008 only

Guest Capability: 64-bit and FAST!

Keyboard Shortcuts: [Ctrl+Alt+Left Arrow] to release (if lacking Hyper-V Integration Services).

Download Link:

(comes with Windows Server 2008)

Windows Virtual PC RC

Key Features: Only option for Windows 7 that I’m aware of.   Still in Release Candidate status.

Host O/S: Windows 7 (all editions)

Guest Capability: More than even Hyper-V?  64-bit with “boot as VHD” feature too?

Keyboard Shortcuts: Not sure, haven’t run yet.

Download Link:

http://www.microsoft.com/downloadS/details.aspx?familyid=4C3A0944-A2E6-4F01-9C57-5B55885875CC&displaylang=en

Favicon Generator

While not directly related to SharePoint I think “favicon.ico” is a nice tool for giving any website a stronger brand.   All modern browsers support this feature and keep the icon in your bookmarks to make the site easy to identify.   I’ve tried creating these several ways so I’ll save you the trouble and say that Dynamic Drive has the best.    http://tools.dynamicdrive.com/favicon/     Upload any common image file and it resizes, handles color palette , give you an instant download, and even a nice URL bar preview too.

How do I add this to my site?   I want my bookmark icon to be cool!

  • Add the “favicion.ico” to the root  (i.e.   https://www.spjeff.com/favicon.ico)
  • Add HTML in the page <head> that says  …  <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon" />
  • Bookmark to your heart’s content

Making of the Blog – Behind the scenes

I’ve been mulling over the idea of a fresh coat of paint for this website for a while now.   I finally took a quiet Sunday to focus and get it done.     I learned a few things in the process.   So in the spirit of collaboration I’d like to share those ideas:

  • Backup everything!   Think you’re an expert?   Think you’re good at computers?   Yeah, I did too until I accidentally deleted the root folder.  Yikes.   Confused
  • WordPress continues to amaze me.  The import/export to XML feature is fantastic.   I simply opened the XML in Notepad2 for a search/replace on the domain name URL and then loaded up without any hiccups.   All blog URLs now point to www.spjeff.com
  • Feedburner.    Enough said, I did a poor job before not using this and regret the lack of statistics.    Thankfull my new Green Park 2 theme comes with support for Feedburner and Google Analytics for all the numbers I could want.
  • Find other good blogs.   Imitation is the sincerest form of flattery, right?   To be successful find good role models first.    Borrow ideas, build upon them, try new things, and make them yours too.
  • More picture, less words.    Information overload is the order of the day.    Be kind to visitors and keep the interface light weight.
  • Permalinks. (ex:  /?p=192)  If you switch DNS domains but stay with hosting companies point the old DNS to the new home folder.   It prevents 404 errors for prior readers.

http://www.microsoft.com/web/media/gallery/apps-screenshots/wordpress-250x250.png

SPD Best Practice: Ctrl C+V Quick Backups

To quote Asif Rehmani (@asifrehmani) from www.sharepoint-elearning.com … “SharePoint Designer is like a really sharp knife.   It’s very good at what it does.   You might cut yourself.   But you don’t stop, just act more carefully.”

 

With that lesson well learned over my years with SharePoint customization I must say my favorite trick of all is the simple quick copy/paste backup.   Any time and every time I’m about to jump off a cliff to dive into SPD coding I perform a Quick Backup:

  • Highlight the file
  • Ctrl+C    (copy)
  • Ctrl+V    (paste)
  • Breathe a sigh of relief

Storage is cheap.   Mistakes are not.   Time is one of our most scarce resources.   Buy some insurance with a Quick Backup!   Hot

 

image

image

Why the /Forms/ folder is your friend

I’ve been working with custom views for a few years now and one thing seems to always come up … Where to put those pesky resource files?    Images, JavaScript, User Guide PDF, all of these I’ve used in one way or another.    Tweaking DispForm.aspx is a past time of many SharePoint developers but how many know of a proper place to drop their resource files?     The /images/ folder of the web is intriguing but leads to a disconnected management experience.   What if this list is deployed to another site with a duplicate file of the same name?  (naming conflicts)

 

I will submit the idea that the hidden system folder /Forms/ is a great place for these for four simple reasons:

  1. Easy to find – Yes, I get lost on my own sites.   Don’t you?   Having the resources nearby makes management a breeze.
  2. Relative URL hyperlinks – No need for server relative or hardcoded URL reference.    From DispForm.aspx (for example) just call “resource_file.ext”
  3. Protected from end users – They can’t delete what they can’t see.   Enough said.
  4. Bundled as .STP for list templates – When you save any SPList as STP it will automatically package up the entire contents of the /Forms/ folder.   How cool is that!?!    So easy to migrate, duplicate, and even control source version history by saving often.    This alone is reason to use this technique.

 

So the next time you’re adding custom files to a particular list please give the /Forms/ folder a second look.  Hot

 

image

 

image

 

image

The Ultimate SharePoint 2007 Virtual Machine (VHD)

I maintain a local virtual machine on my laptop to evaluate, explore, code, and learn.    It’s one of my best tools and I find it incredibly useful each day.    It also needs refreshing and upkeep like any other technology.    Patches, new features, and experience compel me to make adjustments as time goes forward.   

 

I’ve completed my latest build and wanted to lay out the specifications here in case it’s helpful to other people building a similar virtual machine for their own work.   Admins, developers, and even business analysts can benefit from having a local fully autonomous and fully featured SharePoint server.

 

image

 

VHD Format
Ready for immediate use on any VM system
WinRAR split across 20 files (700 MB) for a total 13.2 GB download with a built in EXE extractor.   No need to have WinRAR for extraction.
NoteNo integration services are pre-loaded.   If you’re using Microsoft Virtual PC, VMWare, or Hyper-V these come with an option to load integration for a smoother experience working inside of the running guest VM (moues pointer, disk drives, keyboard shortcuts, etc.)

Applications Installed
Adobe Reader 9.1.0
Adobe Flash for IE
IE 8.0
IE Developer Toolbar
Notepad 2.0
Performance Point Server 2007 – Planning and Monitoring Servers
Office 2007 Professional
Office Save as PDF Add-In
Office Groove 2007
Outlook 2007 with email profile configured (workflow / alert demos)
SharePoint Designer 2007
Visio 2007
Project Professional 2007
iFilter pack for Office 2007 formats
Visual Studio 2008 Team Suite Edition  (incl Architecture, Database, Development, and Test)
Visual Studio Tools for Office (VSTO)
Visual Studio Add-In for MS Chart 3.5
Visual Studio Add-In for WSS Extensions v1.3
SDK – WSS CHM Documentation
SDK – MOSS CHM Documentation
Exam Diff Pro with right click integration and plugins for comparing PDF, XLS, DOC, etc.
SQL Server 2005 Enterprise Edition – with Database, Analysis, Reporting, and Integration
SQL Server Reporting Servers – BOTH native and integrated instances
SQL Server Management Studio – Full
SQL Profiler
SQL Sample Databases – Adventure Works, Northwind, Pubs
SQLCMD
SharePoint Server 2007 Enterprise Edition
Windows SharePoint Services patched to August 09 Cumulative Update (12.0.6514)
STSADMWin – GUI for STSADM command line to avoid typos and speed up routine tasks
Filezilla – Free FTP client
MagicDisc – Can host ISO files as drive letters
ZoomIt – Great for presentations!   Zooms to one part of the screen for easy back-of-the-room reading
TreeSize Professional – Show disk consumption by folder branch
IIS 6.0 Resource Kit
IIS 6.0 Diagnostic Kit
SharePoint Manager 2007
Stramit CAML Viewer 2007
Enterprise Library 3.1
SpsDev.com ULS Log Reader
SharePoint Skinner

O/S Installed
Windows Server 2003 SP2 Enterprise Edition x86
Single C: partition expandable up to 30GB with 10GB free space
Defragged already
Active Directory
POP3 and SMTP email for local "training.corp" domain
"administrator@training.corp" is the default email address for TRAININGADMINISTRATOR
Netmon 3.3
Visual Round Trip Analyzer
Windows 2003 Server Resource Kit
CmdHere right click menu integration
Windows Automatic Updates are Disabled to avoid surprises and let you manage manually

 

image 

image

image

image

image

image

image

image

image

image

Return to Top ▲Return to Top ▲