Automate import of data into datasheet using a csv

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I automate the import of data into datasheet using a csv file

I have a version working that populates data cell by cell in the datasheet. However I want to import the entire file at once in cases where the data is quite large ( 7 rows by 1030 columns)

Can I do this in code (c#)

thanks

jmv
 
How do I automate the import of data into datasheet using a csv file?

I have a version working that populates data cell by cell in the datasheet.
Click to expand...
However I want to import the entire file at once in cases where the data is quite
large ( 7 rows by 1030 columns).
Can I do this in code (c#)?
Click to expand...

I think there may be some way of transferring data directly from an array to the
datasheet, but can't find it at the moment.

Failing that, you can parse the CSV data into an array and use something like
this:

Sub ArrayToDataSheet()

Dim oGraph As Graph.Chart
Dim oData As Graph.DataSheet
Dim i As Integer, j As Integer
Dim oSld As PowerPoint.Slide
Dim oShp As Shape
Dim rayData(1 To 6, 1 To 6) As Double

' note that we don't use the first index in the array
' because the first row/column in the graph data sheet
' is the row/column label; don't want to overwrite that
'
' You could get fancier and use an array of strings, include the labels
' and convert the strings to doubles when populating the datasheet from the
array

' blank the array
For i = 2 To UBound(rayData, 1)
For j = 2 To UBound(rayData, 2)
rayData(i, j) = 0
Next j
Next i

' Put some dummy data in the array to test with
rayData(2, 2) = 23.4
rayData(2, 3) = 44.3
rayData(3, 4) = 12.2
rayData(3, 5) = 44.2

Set oSld = ActivePresentation.Slides(1)
Set oShp = oSld.Shapes("TheChart")

oShp.OLEFormat.Activate
Set oGraph = oShp.OLEFormat.Object
Set oData = oGraph.Application.DataSheet

For i = 2 To UBound(rayData, 1)
For j = 2 To UBound(rayData, 2)
oData.Cells(i, j).ClearContents
oData.Cells(i, j) = rayData(i, j)
Next j
Next i


oGraph.Application.Update

End Sub
 
Thanks

However this is what I am doing now for my 7 X 1000 2d array of data

This takes over four hours to load into the datasheet

I have the same data in a CSV file and I just thought that maybe it would be quicker to import the file into powerpoint chart datasheet

I can't seem to locate the correct syntax or else the automation of this will not work because it is looking for me to answer questions regarding the file delimiter etc. in the background
 
Thanks.

However this is what I am doing now for my 7 X 1000 2d array of data.

This takes over four hours to load into the datasheet.

I have the same data in a CSV file and I just thought that maybe it would be
Click to expand...
quicker to import the file into powerpoint chart datasheet.
I can't seem to locate the correct syntax or else the automation of this will
Click to expand...
not work because it is looking for me to answer questions regarding the file
delimiter etc. in the background.


This came up a week or two ago; I wasn't able to find any exposed methods for
doing this or creating a link, and nobody else posted anything about either. I
suspect there are no exposed methods.

I'd look a bit more for a way to go from an array to the datasheet, though.
Reading the CSV into an array shouldn't take more than a few seconds even on a
slow box.

Then try minimizing the PPT window or locking it while the code runs; window
updates can slow everything way down.
 
Thank you. Yes that makes sense. I am currently running it with the object displaying as it updates.
 
Back
Top