topic Re: How to embed picture in an existing excel file as a separate tab/worksheet in Alteryx Designer Desktop Discussions

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

xiaoyan — Wed, 29 Dec 2021 05:53:51 GMT

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!

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

csmith11 — Tue, 04 Jan 2022 17:23:14 GMT

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-File/td-p/674245

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

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

csmith11 — Tue, 04 Jan 2022 17:23:58 GMT

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-another-file

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.

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

apathetichell — Mon, 03 Jan 2022 17:49:59 GMT

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-xlsxwriter

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.

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

csmith11 — Tue, 04 Jan 2022 14:53:10 GMT

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()'

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

xiaoyan — Wed, 05 Jan 2022 02:12:06 GMT

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?

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

csmith11 — Wed, 05 Jan 2022 02:23:38 GMT

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()'

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

xiaoyan — Wed, 05 Jan 2022 02:45:45 GMT

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

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

csmith11 — Wed, 05 Jan 2022 14:21:29 GMT

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()'

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

csmith11 — Thu, 06 Jan 2022 15:09:47 GMT

@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.

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

xiaoyan — Fri, 07 Jan 2022 01:43:19 GMT

Hi csmith11, I can't resolve this.

1) When i tried your method and run directly, i gotten the below error (see screenshot),

2) but when i tried to change the path location to my local drive, i couldn't get the result which i am expecting (see 2nd screenshot), which is the image in the "preexisting file". Did i miss out on anything?

I attach the vbs script below for your reference. Thanks!

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

Satty12345 — Thu, 18 May 2023 18:00:03 GMT

Need further help on the how to embed the image on an existing excel file post.

Hi CSmith11,

I would like to first say thanks alot to providing a solution the subjected issue. I downloaded the working zip WF however not able to tie it to my scenario.

Few clarity required as following :

how exactly you are using the text input tool in the example . I am not able to follow that. what is the preexisting path & temporary path here.

I understand the given solution by you can be included on the original main workflow & hence will ultimately embed the image on the existing excel output the on the same time so the same final output can be uploaded on sap posting along with image on other tab as an backup/supporting doc.
How to use VBA on my existing workflow . never used it so not sure on this. pls guide.
Is there a way we can connect over call . my mobile # 91 9953081287. that will be easy to discuss this and sort out.

Regards,

Satendra Vishwakarma

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

Satty12345 — Thu, 18 May 2023 18:16:44 GMT

Need further help on the how to embed the image on an existing excel file post.

Hi CSmith11,

I would like to first say thanks a lot to providing a solution the subjected issue. I downloaded the working zip WF however not able to tie it to my scenario.

Few clarity required as following :

how exactly you are using the text input tool in the example . I am not able to follow that. what is the preexisting path & temporary path here.

I understand the given solution by you can be included on the original main workflow & hence will ultimately embed the image on the existing excel output the on the same time so the same final output can be uploaded on sap posting along with image on other tab as an backup/supporting doc.
How to use VBA on my existing workflow . never used it so not sure on this. pls guide.
Is there a way we can connect over call . my mobile # 91 9953081287. that will be easy to discuss this and sort out.

Regards,

Satendra Vishwakarma