As part of a BI solution I'm developing I need to export ranges and PivotTables to image files for inclusion in emails and on webpages.

There are many code snippets out there on the web that allow you to do this by copying the range to a new chart and then exporting the chart as an image (Excel won't allow you to export to an image directly).  These work fine, but the quality isn't all that great. 

The trick is to use '.CopyPicture xlScreen xlPicture', then you can resize your chart and export a larger image with a better resolution.  xlPicture copies the range as a vector/metafile image so keeps the quality when resizing (as Jan Peltier explains here).

Function PNGRange(oRng As Range, fName As String) As Boolean

'Function to create a PNG from a selected range or pivottable

Dim chtNm As String

oRng.CopyPicture xlScreen, xlPicture 'Use xlPicture to copy as vector 

Sheets.Add
chtNm = ActiveSheet.Name

'Create a chart on the new sheet and resize it to the dimensions of your copied range.
With ActiveSheet
.Shapes.AddChart.Select
.Shapes("Chart 1").Width = oRng.Width
.Shapes("Chart 1").Height = oRng.Height
End With

ActiveChart.Paste

'This sorts out the quality by making the pasted image larger before exporting.  Change the value from 3 to whatever you like. 1 will not improve quality, 10 will make the output filesize huge.
With ActiveSheet
.Shapes("Chart 1").ScaleWidth 3, msoFalse, msoScaleFromTopLeft
.Shapes("Chart 1").ScaleHeight 3, msoFalse, msoScaleFromTopLeft
End With

'You can export to any image format here.  Ensure the file extension matches the filtername.
ActiveChart.Export Filename:=fName, filtername:="PNG"

Application.DisplayAlerts = False
Sheets(chtNm).Delete
Application.DisplayAlerts = True

PNGRange = True

End Function

Call the Function using:

Sub imgcreate()

Dim imgcreate As Boolean
imgcreate = PNGRange(Sheets("Sheet1").Range("A1:C4"), "C:\exportfile.png")

End Sub

NB. To select a whole PivotTable as the Range use:

Sheets("Sheet1").PivotTables("PivotTable1").TableRange2

to select the range.

As you can see below, the image quality is much improved now.  Click the image below and you can switch between the two qualities.

I'm building an Excel-based BI tool using PowerPivot and needed to automate the changing of some slicer values to produce different report contexts.

The values in the slicer are all simple integers (in the source SQL database too) and when used in code need be formatted in Scientific notation, but without the +...

The following VBA code converts my integers to the correct form to enable modifying the Slicers e.g. 146 to 1.46E2 or 270 to 2.7E2.

StrNum = Format(StrNum, "#.###E+0")
StrNum = Replace(StrNum, "+", "")
If Left(Right(StrNum, 3), 1) = 0 Then
StrNum = Replace(StrNum, "0", "")
End If

One small point: the "#.###E+0" formatting does the job on a worksheet, aside from the + symbol. However, the VBA code keeps a "0" in the string just before the E if the Integer ends in a zero (10, 270, 640 etc.). The If statement strips that out.

I thought it might be a good idea to turn an old XP installation into a Virtual PC, just in case I ever wanted to go back and have a look at something, or use some of that old XP software...

Whilst that seems like a great a idea, there isn't a simple way to do it.  It's fairly easy to create a VHD copy of the original system using Disk2VHD, but there are problems if the disk is over 32GB in size...

When I finally got it all up and running - it appears that Microsoft won't let you create VHDs from OEM copies of their operating systems, so I can't activate it...  Oh well, just use the free XP mode system that can be downloaded for Windows 7 (Professional or Enterprise only) and reinstall the stuff I need as and when.

In a previous post, I mentioned problems I was having with a backup program I have running for clients.  As it turned out, the issue wasn’t with my LFTP build, but something more tricky to track down...

I’ve installed FTP 7.5 onto my server (Windows Server 2008 R2 32-bit) and have numerous sites and users that connect and transfer data perfectly well. 

The Issue:
The issue arose when uploading large files (>150 MB) to the FTP server...  After 60 minutes, the upload would restart from the very beginning without warning and effectively enter a loop, chewing bandwidth and resources on both client and server. 

Having checked the logs (nothing conclusive there), I spent a while Googling for some help.  The Windows firewall came under suspicion, but I couldn’t find anything there that looked likely.  Other posts suggested that the issue lay with timeout setting for the TCP connection.  The problem certainly feels like its something to do with the control and data channels.  Although the data channel is very active, the control connection appears to timeout after 60 minutes.  Despite an exhaustive search of the registry and the FTP settings, I have been unable to find the correct setting and besides, what should it be changed to?  2 hours, three, four?  I’m not comfortable making such wild changes to my configuration...

On my trawl through the FTP side of the server setting, I noticed an option that allows for partially uploaded files to be kept or deleted on disconnects.  Under Advanced Options for the FTP site, select Advanced Options.

Within the settings page, find the 'File Handling' control and change 'Keep Partial Uploads' from False to True

It's resolved the problem for me and now everything's back to normal!

I've been using a version of the excellent LFTP (v3.7.6), command line FTP client for Windows as part of a more complex backup program I've written.  

Recently, my program stopped working as expected, so I began investigating.  One line of investigation took me down the route of updating the LFTP version I had installed - not as easy as I'd hoped!  In fact, it looks like there's no-one who maintains a Windows build of LFTP anywhere on the net (I couldn't even find the version I'd been using again), so I had to build my own using CYGWIN.  This is not exactly complicated, but it was a waste of time that I didn't have, so I'm posting my build here which'll hopefully save someone else an afternoon!  It is built with SSL support and you will need to extract all of the files in the archive in order to use it.  It's compressed using the increasingly popular 7-Zip.

You can add all of the files to the %windir%\system32 folder to use the program from any folder on the system, or simply run it directly from any folder you choose.

For help and usage instructions, see the LFTP homepage where there's plenty of support and links to instructions available.

[UPDATE v423 build updated with config file - December 2011]

Download Link:  lftp423win.7z (Dec 2011)

Download Link:  lftp407win.7z

ehcanadian's 4.3.5 Build: http://ham-tech.net/tools/lftp_v4.3.5_win32.zip
md5: 213eeb88e8478ef012ed06c4f33ee27f