Excel Load automation

Load data into Excel automatically using a script

                       

Overview

• References
• Import Data into Excel
  • Record chart making
• PowerShell
• Python

This article is about loading data into Excel (a part of the Microsoft Office package) using automated means.

This page provides PROTIPs that highlight hard-won experience not available elsewhere.

The versions of Excel:

• 1998/2003 has a different data model, so used .xls while newer versions went to .xlsx, which contains XML instead of a custom format.
• 2010
• 2013 on PC
• 2016 on Mac

References

The Excel 2010 Object Moodel Reference in MSDN

Import Data into Excel

1. Create a .csv (Command separate value) file. It’s a universally accepted format.

   CAUTION: Text cells containing a comma need to be between double quotes.

   PowerShell can create CSV files.

2. Double-click the .csv file for Excel to open it because the Excel program is registered to it during installation.

   The data becomes embedded into a tab inside Excel.

   See: Opening semi-colon delimited CSV file.

   http://www.homeandlearn.org/open_a_text_file_in_vba.html

   Record chart making

3. TODO: Set recording.

   VBA code.

   VB to PowerShell conversion Guide

4. Create the chart.

5. Change the Headings and Footings.

   See https://blogs.technet.microsoft.com/heyscriptingguy/2009/01/15/hey-scripting-guy-how-can-i-modify-the-footers-of-an-office-excel-spreadsheet/

6. Edit the script.

   This is a sample PowerShell script named GetWmiPerformanceDataCreateExcelChart.ps1 that creates Excel chart from Windows Diagnostic data.

   Others from the archive.

7. TODO: Run the script to create a new chart based on data using VBA.

http://blogs.technet.com/b/heyscriptingguy/archive/tags/microsoft+excel/windows+powershell/ using Windows PowerShell to manipulate Microsoft Excel.

### Change data

CHALLENGE: Change the data in a sheet already containing data and charts.

1. Open new data in a new spreadsheet.

2. Copy and paste the data from another sheet.

   To do the above automatically:

http://sitestory.dk/excel_vba/csv-file-import.htm

https://gallery.technet.microsoft.com/office/d2b1809b-e4f4-4688-bb11-9eb47a702ca0 This script (posted 8/10/2010) modifies an existing embedded chart in an existing Microsoft Excel spread sheet.

$chartType = "microsoft.office.interop.excel.xlChartType" -as [type] 
$strPath="C:\fso\test.xlsx" 
$objExcel=New-Object -ComObject Excel.Application 
$objExcel.Visible=$true 
$WorkBook=$objExcel.Workbooks.Open($strPath) 
$worksheet = $WorkBook.Sheets.Item(1) 
$chart = $worksheet.chartobjects(1).chart 
$chart.chartType = $chartType::xl3DLine 
$chart.HasTitle = $true 
$chart.ChartTitle.Text = "My Groovy Chart"
   

NOTE: Charts embedded in a Excel spread sheet require the chartobjects property to return the chart container from the spreadsheet. Then use the chart property from the chart container to retrieve a chart object. This is standard object oriented programing (oop).

https://blogs.technet.microsoft.com/heyscriptingguy/2010/09/09/copy-csv-columns-to-an-excel-spreadsheet-by-using-powershell/

https://www.datacamp.com/community/tutorials/r-tutorial-read-excel-into-r#gs.DDoO4rk

https://support.office.com/en-us/article/Update-the-data-in-an-existing-chart-06b76016-e9e3-49fe-8dfc-ca50beeed6e8

PowerShell

This blog provides this sample PowerShell 3.0 script:

$a = New-Object -comobject Excel.Application
$a.Visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Cells.Item(1,1) = “A value in cell A1.”
$b.SaveAs(“C:\Scripts\Test.xls”)
$a.Quit()
  

NOTE: PowerShell can create Excel files using its Out-GridView command. See https://blogs.technet.microsoft.com/heyscriptingguy/2014/01/10/powershell-and-excel-fast-safe-and-reliable/

Python

http://stackoverflow.com/questions/25235743/import-csv-file-into-an-existing-excel-spreadsheet-save-that-file-and-repeat-m

---