Reading and Writing with Macro Enabled Excel Files

Question

Hello,

I have been unable to find any community posts where someone's issue is like mine.

Here is what I am doing in Alteryx situation:

* I am reading in a folder of Macro Enabled files (they are password protected as well if that is significant) using file directory and dynamic input.
* I am modifying the values
* I write to specific cells on a sheet or two in the file by overwriting a cell range

Here is the issue:  If someone who enables macros opens up the document, they are able to see the changes.  I pass it to another group and they don't know how macro files work and so they do not have them enabled.  They see the old data, before Alteryx changed anything.  If I open the same file with macros enabled and save over it, the person without them enabled opens the file and can see the modifications.

Somehow the new data is saved somewhere but it is not visible until it is resaved by someone who has them enabled?  This may be caused more by my lack of understanding about macro enabled files but thought I'd ask here.

I have considered using the run command to build out something that just opens and saves the documents but I'd rather not considering there are a large amount of files.

Thank you!

OTrieger · Answer

You can use Run Command via Alteryx no need to write VBA code to do it. Alteryx will be able to open and save the excels. Use Run Command to do it for you.

447426 · Answer

Yeah, I was thinking it might be more of a Macro Excel issue... I believe the macro is creating password protection over the specific cells I am writing to.  But then isn't it odd I can read them in in the first place?  Not entirely sure though if that is what the macro is doing because I am a support team helping another team which supplied me these files.  I think they used a macro in the first place to create the files so that simply could be the reason they are Macro Enabled as well.

Now to go fumble my way through VBA to write a script to open and save everything haha.

Thank you!!

OTrieger · Answer

@447426 
It seems to be Excel related topic, not really related to Alteryx.
Do you know what the macros are doing and where Alteryx saving the data into the Excel file, as these might be macro related cells that impacting it.
Your suggested solution will solve it.