Back in 2009, I published Convert CSV files to Excel, a vbscript which converts csv files to xlsx format. Although there is not good way to directly support drag and drop with PowerShell, it can be done with a form. Convert-DroppedCSVsToExcel.ps1 was mostly written as an exercise for me to work with drag and drop…
Tag: Excel
Combine CSV Files to XLSX File with Worksheets
Three years ago, I posted CombineXLSheets.vbs, a vbScript which allows you to drop and drag Excel spreadsheets onto it and have them combined into a single workbook file with multiple worksheets. Now that I am working in PowerShell, I have found the need to consolidate CSV files. Parts of Combine-CSVFilesToWorksheets.ps1 come from code ported from…
Export to XLSX without Excel: Export-XLSX.ps1 Revisited and Tweaked
I often look at the code of others with respect and admiration. Peter Kriegel (Germany) wrote Export-XLSX.ps1, an amazing script which lets you export data into a real XLSX file without Excel being installed. The script also enables you to directly append worksheets to XLXS files. His website, http://www.admin-source.de, hasn’t been updated in a while,…
Minimize File Size Bloat with Excel Pivot Tables
I often work with very large spreadsheet, often more than 30 MB in size. I am an Office user, not an Office expert. A few months ago one of my teammates taught me how to use pivot tables to summarize and present the large data set in a way better than just using filters. The…
Get Holidays for Any Year and Any Country as PowerShell Object
This script started out as a project to figure out how to automate Excel web queries in PowerShell, so MS Excel is a prerequisite for this script. If you have Excel, but have never used query tables, it is accessible by going to the Data tab, and then choosing “From Web”. An overview from TechRepublic…
Combine Multiple Excel Spreadsheets
I create a lot of Excel audit reports in a multi-domain environment. When they go out, I need to combine the reports from each domain into a single Excel workbook with multiple worksheets. I found some VBA code on mrexcel.com which was pretty easy to port to vbscript. I put the script on the desktop…