Automatic Backup of Table Object’s Values

Assume that you have a large and complex Excel workbook. It has the following key characteristics:

  • There is a table object with many formulae based on several worksheets. The object is a single object on a separate worksheet called Summary
  • The worksheets the object is based on are connected to external CSV files/databases and are reloaded (refreshed) on opening the workbook
  • These worksheets contain both historical data (that already are in the table object) and recent data (that are automatically added to the table object after refresh)
  • Historical data may change as a result of a refresh, so there’s a need to do a scheduled backup of data
  • The backup should contain only the table object, without any other worksheets it references in the original file, so the object should be stored only as values but keeping the proper formatting and column widths

As we’re talking about a scheduled back-up, the most logical choice is creating a PowerShell script that’s then called through Task Scheduler in the required time intervals.

Here is a complete script that does that:

# creating an Excel object

$excel = New-Object ComObject Excel.Application

$excel.Visible = $false

$excel.DisplayAlerts = $false

 

# defining file names

$fnamesrc = “SourceFile.xlsm”

$fnamedst = (Get-Item -Path (“./ + $fnamesrc)).BaseName + ” Backup.xlsx”

 

# opening the source workbook

# full path is required

# assuming that the source file is located in the working directory of the script

$wbsrc = $excel.Workbooks.Open((Get-Location).Path + ‘\’ + $fnamesrc)

 

# creating the destination workbook

# it automatically adds a single worksheet

$wbdst = $excel.Workbooks.Add()

# renaming that worksheet

$wbdst.Worksheets(1).Name = “Summary”

 

# there’s a single table object on the Summary worksheet of the source file

# table objects are stored in the ListObjects collection of a worksheet

# we have to select it and copy to clipboard

$wbsrc.Worksheets(“Summary”).ListObjects(1).Range.Copy()

# now pasting as values

# it’s a method of Range object, so first have to define the target range

# we’ll be copying it to the top left cell, A1

$wbdst.Worksheets(“Summary”).Range(“A1”).PasteSpecial(-4163)

# repeating the same steps to paste formats…

$wbsrc.Worksheets(“Summary”).ListObjects(1).Range.Copy()

$wbdst.Worksheets(“Summary”).Range(“A1”).PasteSpecial(-4122)

# …and column widths

$wbsrc.Worksheets(“Summary”).ListObjects(1).Range.Copy()

$wbdst.Worksheets(“Summary”).Range(“A1”).PasteSpecial(8)

 

# saving the destination file, overwriting if necessary

if (Test-Path $fnamedst) {

    Remove-Item $fnamedst -Force

}

$wbdst.SaveAs((Get-Location).Path + ‘\’ + $fnamedst)

# closing the workbook

$wbdst.Close()

 

# closing the source file without saving

$wbsrc.Close($false)

 

# quitting Excel and freeing resources

$excel.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Remove-Variable excel

 

You can set  $excel.Visible = $true  if you want to see all that in action.

It’s assumed that there’s a macro that updates the worksheets based on external data and adds new data from these worksheets to the table object, and that the macro is written in the Workbook_Open() procedure and, thus, is executed automatically whenever the workbook is opened by Excel. PowerShell uses Excel to open the workbook, so the macro is executed and all data are correctly updated before proceeding with the backup.

If you’re wondering why you need to execute the last two lines of the code after asking Excel COM object to quit, read this post.

Advertisements

2 thoughts on “Automatic Backup of Table Object’s Values

  1. Magnificent web site. Lots of useful information here. I’m sending it to some buddies ans also sharing in delicious. And of course, thanks for your effort!|

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s