I have come to a problem that I had kind of sorted, but it came back to haunt me.
I have a set of Excel files with information (origin) that feed an aggregator/wrangler/chewer in Excel (workbook) and then I have a set of files, one for each subsidiary (output), that have the same structure (sheets, hyperlinks, headers) but only get the relevant info for that subsidiary.
Right now I have everything automated, I download, prepare the information and update the origin files, then the only thing I need to do is open and save all the other documents (aggregator/subsidiary exports) and I do it with VBA.
In order to distribute the subsidiary files I have to come up with something that can have the same structure as an Excel workbook, but doesn't have the links to the aggregator, or the subsidiaries won't be able to see the information as there will be #REF errors everywhere (even on some sheets headers).
The solution that I put in place was exporting the files as MHTML (Single File Web Page), as it managed to keep the sheets and hyperlinks and everything as intended, while only needing to "open [subsidiary file].xlsx & save as [subsidiary file].mht", but then I came to learn that it is only possible to open this file type in IE, which not everyone has access to.
Then my problem is that I need an alternative to this solution while using only "plain" solutions as the IT department won't allow me to use anything more than standard administrative tools (standard MS Office suite).
I tried to replicate the subsidiary files by copying and pasting every sheet, but then I cannot come up with a way to keep every formatting (mainly header width) as intended when pasting as "values", or as "values and source formatting", and this approach doesn't escalate well.
Do you know of a way of saving the file as is but without the formulas, or preventing them from updating when the file reopens (even if you say no to "update links" prompt sometimes some cells ate updated, don't know why), or some different approach?
The idea is to generate a file that no matter who the user is, and does, is able to see the information, and cannot screw the file/info in it, and if possible keeping it "easy" to update with a "simple" macro.
Kind regards, Flávio
