Picking up an Excel File with a Macro (.xlsm), writing my data into it and outputting

[Gandalf_NotGrey]

Hello,

I have a template excel file that has a Macro, which is basically a form that gets opened when the user clicks on that sheet.

I have a separate set of data that needs to be written in multiple tabs.

Is it possible for me to open this existing Template file, write my current data into it, I do not touch the tab which has the Macro/Form, and then output the file with a new name into a separate location??

Thanks

[DanielG]

@Gandalf_NotGrey -- look into Blob Tools.  They can make copies of the template without compromising the template file.  I will dig up some links for you to review

[DanielG]

https://community.alteryx.com/t5/Engine-Works/The-Blob-Reporting-That-Isn-t-From-a-Horror-Film/ba-p/...

Check this write up from @NicoleJ 

[Gaurav_Dhama_]

As mentioned by Daniel, you can use Blob tools for this purpose. Below is the sequence you need to follow.

Blob input > formula tool > blob output

in blob input select the xlsm file to read.

in formula tool create the location and file name to which you want to output as. Make sure extension is also mentioned along with the name, like new_file.xlsm.

in blob output select the destination as the field you just created, and select overwrite the path.

this way you will have your a new xlsm file created, you know the file path and name since you created it in formula tool, you can use this path further down your workflow to output the data to.

[griffinwelsh]

As others have said use a blob tool. I have attached a macro that I created for outputting in the format of a template. You just feed your data into the D anchor and your file paths into the F anchor.

[Gandalf_NotGrey]

Thanks so much @DanielG @Gaurav_Dhama_ @griffinwelsh for your responses.

I am looking at Blob tools to do this. 

One follow-up question please - Is it possible to include the Render tool into this flow? 

The workflow I have, has some conditional formatting using the Table Tool, but is it possible to render data into the blob output file? The workflow in this link uses the Output Data tool, that's why I have this confusion. Thanks. 

[griffinwelsh]

I am not aware of a native solution for this, but you can render your table to a temporary excel file and use a python script to combine it with the other excel output then delete the temporary file. If you are interested in this route below is a good article on the python side of it.

https://www.geeksforgeeks.org/how-to-merge-multiple-excel-files-into-a-single-files-with-python/

[DanielG]

@Gandalf_NotGrey -- I believe you should be able to render data into a named range/sheet. 

Personally in my past experience, what I ended up doing (because I always struggled with dropping data into named ranges/formatted sheets) and since I already had VBA written into my excel template, I would drop all the data as new sheets in the template then had VBA check when opening the file in "new sheet" exists run all this code to move data from the new sheets to the formatted locations then delete the "new sheet" so the next time it is open it doesn't re-process everything again.  

Excel VBA maintains the integrity of formatted sheets much better than Alteryx, IMO.  

[HenriHiebert88]

Yes, it's possible. You can write your data into the existing Excel template file, excluding the sheet with the Macro/Form, then save it with a new name and location.

[Gaurav_Dhama_]

If you are referring to just putting the data into the template, I guess best would be to go with the normal output.

You can use conditional formation to update the cells when they are not empty.

Correct me if wrong, render tool will overwrite the file and you might lose the data in it.