Wilson Mar bio photo

Wilson Mar

Hello. Hire me!

Email me Calendar Skype call 310 320-7878

LinkedIn Twitter Gitter Google+ Youtube

Github Stackoverflow Pinterest

Load data into Excel automatically using a script

Overview

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