Button for Exporting Worksheets

I have been trying and searching the web and testing different macros for the last hours, no luck so far. Hope there is someone that can help me out!

I the worksheets that you want to turn into workbooks are in the file that contains the code then the below will split the sheets into workbooks and place them in your download folder:

Please Login or Register  to view this content.

Hi CheeseSandwich,

I am getting the error message:

Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed.

Any idea why this is the case? I originally thought it might be in the \ or / because I am using a Mac right now compared to most users of this Excel that use Windows. But changing it in xPath or in SaveAs did not change the error message.

If you run the below does it give you your download folder?

Please Login or Register  to view this content.

It does not open my download folder if that is what you are asking. It gives me a message "\Downloads"

If I run the code above I get the same pop-up as previously, just without the message. Only the OK button.

Sorry there was a mistake in the else part of the code above, the below has been rectified:

Please Login or Register  to view this content.

Still the same pop-up without any information in it..

Does the main code work if we put the files in the same place as the spreadsheet with the code?

Please Login or Register  to view this content.

Hi! Yes, it works. However, there are three issues. Issue one is that I had to remove "\" from the code otherwise I had the same error as before. Once I did that it worked but the file did not come into the folder my own file was in, but the folder where my folder is in. So the folder itself is called A&T, in folder A&T I have folder BOM, within folder BOM I have my BOM Excel file. But now the two Excel files that are created from this code are in folder A&T. Very odd. The third issue is that the name is now BOMBOMUPLOAD and BOMPRODUCTUPLOAD. So I assume it is reading the BOM from the path, but is not actually applying it... very weird.

Maybe that will change if we peplace all of the \ for / in the path

Please Login or Register  to view this content.

Unfortunately still the exact same. If I copy your code I get the error message. if I remove the "/" in the line SaveAs it works, but again with BOMBOMUPLOAD as title

Hi, I got some news. I got it to work! I went to search for my Download folder. It is "/users/'USER'/Downloads/" I found out that "USERPROFILE" does not work, but it has to be "USER" as than I got the right feedback. I am however scared that this will not work for Windows users, could you help me out there? Maybe somehow to make this compatible? As you could try this out on yours. Thank you so much so far!

Please Login or Register  to view this content.

Great news, just what we needed.

Below is some untested code, interested to see what response you get in the immediate window?

Please Login or Register  to view this content.

On the first try, it seemed to work and I got the message that it had downloaded. However, it had not. I thought I'd try to delete the ones I downloaded previously. Once I did and I ran it again, it did work. I don't think this has anything to do with what you added to the code, so I think that is successful, however, it does not replace or overwrite the previously downloaded file with the same name. Is there a way we can fix that?

I added a \ to the xPath for the windows part, was this included on your test. I added it about 2 minutes after i posted it?

Was it on Windows or Mac?

Oh no I did not see that, I just ran it and it works like a charm. But only if I remove the files first from my Download folder. All my testing is on Mac, do not have access to a Windows currently

Does it behave any different if we swap the / for : in the Mac xPath?

Please Login or Register  to view this content.

Yes, nothing happens. I get the error message but no files (even if I empty the download folder)

I still have a suspicion that the Mac bit is not saving in the right place as the code overwrites the files at my end using the windows version of the xPath.

The below includes a KILL command to delete the file before it saves the new version - theory is it will delete the file before saving the new one.

I am not a big fan of KILL though so run it with caution (tested my end and removes correct files)

Please Login or Register  to view this content.

Sorry I think I am having a moment, Application.DisplayAlerts = False is already in the code so thats why we are not getting the message as it is being suppressed by that line. If you comment them lines out you get the warning message.

I suppose it can be your choice now, have the message pop up (code will error if they press No/ Cancel though - would need handling) or keep the kill command.

Still does not explain why the files were not being saved over though..

When Application.DisplayAlerts = False is active on Mac it may have a different default option:
Windows - overwrite file as a default
Mac - don't overwrite the file as default
I am guessing at the above as i have nver worked on a Mac

Aha interesting, I just made the discovery that it does work without the KILL switch, but it just does not get added as a new file to my Download folder. So it stays in position at lets say 'Date Added: 11:40' but it is 'Modified by 11:41' if I re-run it. I don't know if this is a problem, if you have to keep searching for this file every few days and you may downloads many things, it might be a problem

That reply was still on the other. Okay, that is interesting. Weird that there is so much difference. I think I will keep the kill switch for now with my testing, but the primary users are Windows users so that should be fine there. Thank you greatly!!

Yes that is what it does on my machine, it only updates the timestamp for the file to state that it has been resaved.

I did spot that there is the ability to download a file that is hosted on the internet using FollowHyperlink, i think this makes the computer download the file as if you were downloading and file from the internet. For this though you would need your spreadsheets to be hosted somwhere to be downloaded. Not sure if this would be an option?

Ahh that would be great however that wouldn't work here, the BOM will get duplicated and thrown around many many projects, none of it is hosted anywhere

I see, well good luck with the code you have, let me know if you have any issues.