Archive

Archive for February, 2012

XLNS was added to the Spreadsheet Page from John Walkenbach.

February 16th, 2012 admin No comments

Just found out that my blog was added to the Spreadsheet Page from John Walkenbach.

I could not resist making a screenshot with the 10 post from my Blog …

Categories: Uncategorized Tags:

Combine csv’s into one Workbook

February 2nd, 2012 admin No comments

Lately I was looking into the issue of combining all csv files in a folder into a single workbook.
One could do this in vba but it needed to be a scheduled overnight process.
As I recently discovered PowerShell I decided to give it a fair chance.

This is the script I came up with and to be fair it works like a charm.
Some minor tweaks need to be done but I couldn’t resist the post because it has been a while.

$files = get-childitem 'D:\Data\'

$datafiles = $files | where {$_.extension -eq ".csv"}

$Excel = New-Object -Com Excel.Application

$Excel.ScreenUpdating = $false
$Excel.DisplayAlerts = $false

$WorkBook = $Excel.Workbooks.Open('D:\Data\'+$datafiles[0].name)

$mv = [System.Reflection.Missing]::Value

for ($index = 1; $index -lt $datafiles.Count; $index++)
{
 $WorkSheet = $WorkBook.Sheets.Add($mv,$mv,$mv,'D:\Data\'+$datafiles[$index].name)
 $WorkSheet.Cells.EntireColumn.AutoFit()
}

$Excel.Visible = $True
$Excel.DisplayAlerts = $True
$Excel.ScreenUpdating = $True
Categories: CSV, Excel, PowerShell Tags: , ,