VBs code Export Chart from excel (sharepoint)

[Daves1]

hello everyone.
I am trying to create a script that extracts the chart (in jpg) from excel in sheet 2.
I need this to create a flow via power automate to populate a word document in sharepoint with the extracted chart.
I'm currently using this script but without results:

// Get chart 1 from Sheet2.
  const chart_1 = workbook.getWorksheet('Sheet2’).getChart(‘Chart2’); 
  const chartImage = chart_1.getImage()

Thanks

 

[xwb]

Try using variables instead of constants.

 

[Daves1]

same problem using "Dim

 

[strongm]

Probably because your code is not VBscript. Nor is it calling Excel automation methods ...

Looks like you may have copied some C# codde ...

 

[Daves1]

I would like to simply export chart 2 from sheet 2 by converting it to an image format and save it in the same sharepoint directory.

 

[strongm]

Assuming your VBScript has a variable [tt]Workbook[/tt] that contains a reference to an open instance of the relevant workbook in an Excel instance, then something like

[COLOR=blue]Workbook.Sheets("Sheet2").Shapes("Chart 2").Chart.Export "d:\examples\examplechart.jpg"[/color]

 

[Daves1]

thanks for the answer but I get the following error:

';' expected. Workbook.Sheets("Sheet2").Shapes("Chart2").Chart.Export

 

[strongm]

So - you clearly are not uising VbScript, as I earlier alluded. This is a VBScript forum. So we advise on VBScriot here, not other languages

 

[Daves1]

sorry unfortunately I don't have much familiarity with vbscript, but I'm trying to do it. I stopped here:

function main(workbook: ExcelScript.Workbook) {
  let Sheet2 = workbook.getWorksheet("Sheet2");
  var rang = Sheet2.Range("J2:Q15");
  var ch = Sheet2.ChartObjects().Add(rang.Left +
    rang.Width, rang.Top, 350, 220);
  ch.Chart.ChartType = 4;
  ch.Chart.SetSourceData(rang, 2);
  Worksheets("Sheet2").ChartObjects(1) _
    .Chart.Export _
  FileName:= "Chart2.gif", FilterName:="GIF"
}

 

[strongm]

>I'm trying to do it

Forgive my bluntness, but no, you are not. With you bigger example, it is now clear that you are in fact writing C# as I suspected

Whatever your programming environment is is also expecting C# (which is why is complained about the

 

[Daves1]

Ok I didn't mean to create confusion, however if it helps I am using office script from excel online.

 

[strongm]

Ok - so Office Script (which is in fact a variant of Typescript, essentially a superset of JavaScript, and syntactically very similar to C#). The point is that it isn't VBScript! And yes, it only works in Excel 365/online.

So, again - this isn't the right forum. Sadly I don't think tek-tips has either a dedicated Office Script forum, nor an Office 365 forum - but you might try forum68

That being said, your last example looks almost like it should work to me, except you've tried to add what appears to be a modification of a VBA example of Chart.Export to Office Script, which won't work, the JIT compiler will choke when it hits the VBA. So we need to go back to your original post. Unfortunately the Chart object in the Office Script object model doesn't appear to support the export method, and get Image returns the image as a Base64 encoded string. So you need to look at how to convert that into the relevant format to write it out to a file.

function main(workbook: ExcelScript.Workbook) {
  private fs = require('fs');
  let Sheet2 = workbook.getWorksheet("Sheet2");
  var rang = Sheet2.Range("J2:Q15");
  var ch = Sheet2.ChartObjects().Add(rang.Left + rang.Width, rang.Top, 350, 220);
  ch.Chart.ChartType = 4;
  ch.Chart.SetSourceData(rang, 2);
  chartImage = ch.getImage();
}

 

[Daves1]

thanks for the clarifications, but it gives me an error on line 2 could be changed to:

const fs = require('fs');

 

[Daves1]

I tried this way:

function main(workbook: ExcelScript.Workbook) {
  declare function require(name: string);
  const fs = require('fs');
  let Sheet2 = workbook.getWorksheet("Sheet2");
  var rang = Sheet2.Range("J2: Q16");
  var ch = Sheet2.ChartObjects().Add(rang.Left + rang.Width, rang.Top, 350, 220);
  ch.Chart.ChartType = 4;
  ch.Chart.SetSourceData(rang, 2);
  chartImage = ch.getImage();
}

but...
'require' implicitly has an 'any' return type, but a better type may be inferred from usage.

 

[strongm]

You can safely take out the require line out. It is a remnant of a more complex version I was trying for you involving file writing, but I ran out of time to explore that any further (particularly since I am not a TypeScript/Office Script/JavaScript programmer)

 

[Daves1]

Unfortunately I still get errors like: "Sheet2.ChartObjects is not a function
clientRequestId". Anyway you have been really helpful even if I was totally offtopic. thanks a lot!

 

[strongm]

>errors like: "Sheet2.ChartObjects is not a function

A quick glance at the OfficeScript documentation shows that ChartObjects is not a property of the Office Script Worksheet object

Possible the Office Script documentation for Excel might help you:

https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript?view=office-scripts