How to embed picture in an existing excel file as a separate tab/worksheet

[xiaoyan]

Hi, I am able to embed an picture using the image and render tool as the output (into excel format). However, i wanted to embed this excel (with the picture) into my existing excel file as a separate worksheet. There are already other tabs in this excel file. When i tried to import from the render tool output into a Output tool (as my existing excel file), the image is gone. Does anyone has a similar scenario like mine? Thank you! 

[csmith11]

Unfortunately, I can't really think of a simple solution for this.

My first answer was to:

1.Read all existing Tabs into the Alteryx Workflow.

2. Write the Image out to the Excel File First (This clears everything else)

3. Write the Preexisting Info back to the Excel File.

Some other Alteryx Gurus weigh in on the same topic here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Exporting-Image-to-an-Existing-Excel-F...

There is a better answer further below with a working example.

[csmith11]

An after thought that would work VERY well:

1. Render the image to a totally separate excel file. 

2. Use the Run Command Tool to Copy the Rendered Sheet to the Pre Existing Excel File (This will involve Knowledge of VBS) 

This will get you started on the VBS script:

https://stackoverflow.com/questions/43459946/vbs-script-for-copying-a-whole-sheet-from-a-file-to-ano...

How to Run VBS in Alteryx:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Run-VB-script/td-p/64153

Alternative, You may be able to use a python script to copy the New Sheet over as well.

Edit:

I added a working example in another post.

[apathetichell]

I do not believe you can do this in Alteryx out of the box. Try Python or R.

https://stackoverflow.com/questions/40373678/insert-image-in-existing-excel-sheet-by-using-xlsxwrite...

your other option is to read in the existing excel file and recreate it as a rendered excel. Then you can add the image like you would any other reporting tool and using layout/sections set sheet names.

[csmith11]

Here's a working example of what you were trying to accomplish.

Please let me know if you have any questions. Here's the VBS I used.

'Dim xlapp
Dim WSx, WSy
Dim x, y
Set xlapp = CreateObject("Excel.Application")

Set x = xlapp.Workbooks.Open("' + [Engine.WorkflowDirectory]+ [Temp Path] + '")
Set y = xlapp.Workbooks.Open("' + [Engine.WorkflowDirectory]+ [Preexisting Path] + '")

Set WSx = x.Worksheets("sheet1")
WSx.copy Null, y.Sheets(y.Sheets.Count)
y.Sheets(y.Sheets.Count).name = "Image"

Set WSx = nothing
Set WSy = nothing
y.Save()
y.Close()
x.Close()
xlapp.Quit()'

[xiaoyan]

Hi csmith, thank you for your solution. As I'm not familiar with VBS, i meet difficulty in trying to figure out the workflow. I'm trying to understand your example so that i can try to build it into my own workflow. The workflow example is to copy the sheet from Temp path workbook (with image) to preexisting path workbook?

[csmith11]

That is correct. Let me break down the VBS real fast:

Creates Variables

'Dim xlapp
Dim WSx, WSy
Dim x, y

Creates Instance of Excel Application

Set xlapp = CreateObject("Excel.Application")

Sets X equal to the Temp Excel Workbook (This Contains the Image File)

Set x = xlapp.Workbooks.Open("' + [Engine.WorkflowDirectory]+ [Temp Path] + '")

Sets Y equal to the Preexisting Excel Workbook (This is the Original Excel File)
Set y = xlapp.Workbooks.Open("' + [Engine.WorkflowDirectory]+ [Preexisting Path] + '")

Sets Wsx Equat to the Sheet Created by the render tool, This is always called sheet1

Set WSx = x.Worksheets("sheet1")

Copies the Above Sheet From the Temp File to the Preexisting File
WSx.copy Null, y.Sheets(y.Sheets.Count)

Renames the Copies Sheet to the name Image
y.Sheets(y.Sheets.Count).name = "Image"

Clears all Variables

Set WSx = nothing
Set WSy = nothing
y.Save()
y.Close()
x.Close()
xlapp.Quit()'

[xiaoyan]

When i tried to re-link your example to my local drive, i got the below error. How do i resolve ?

[csmith11]

Can you add a browse tool right before the Run Command Tool. And show me the full script. The issue is likely related to the file Paths.

I used relative File Paths in the text input file:

These Path are made Absolute relative to where the workflow is saved: (This is accomplished using the Engine.WorkflowDirectory.)

If you change the file path in the text input tool to be Absolute paths then you'll need to remove the Engine.WorkflowDirectory from the VBS

script.

See below Engine.WorkflowDirectory removed from script:

'Dim xlapp
Dim WSx, WSy
Dim x, y
Set xlapp = CreateObject("Excel.Application")

Set x = xlapp.Workbooks.Open("' + [Temp Path] + '")
Set y = xlapp.Workbooks.Open("' + [Preexisting Path] + '")

Set WSx = x.Worksheets("sheet1")
WSx.copy Null, y.Sheets(y.Sheets.Count)
y.Sheets(y.Sheets.Count).name = "Image"

Set WSx = nothing
Set WSy = nothing
y.Save()
y.Close()
x.Close()
xlapp.Quit()'

[csmith11]

@xiaoyan Please let me know if you are able to implement this method successfully. 

Please considering marking as the solution to help other community members identify solutions faster.