VBA: Tweak Export to HTML code to create folder on desktop and save there?

Hi all,

I am using the following code to export a range of cells (with HTML content) to one consolidated HTML file.
This used to work perfectly until I moved all of my data to OneDrive. Not it seems like VBA doesn't have permissions anymore to write to the same path as the .xlsm is in (which sits in OneDrive)?

I'm wondering, is there a way to tweak this code to use the filepicker instead so I can choose a non-OneDrive folder to save the HTML?

At the moment, the below code only works if the .xlsm is saved in a non-OneDrive location (e.g. Desktop).

The code is:

Please Login or Register  to view this content.

Hope someone can help this VBA newbie here?
Thanks!

Please Login or Register  to view this content.

Hi Kenneth,

Thanks so much! This seems to give me a message with the path to the desktop, but could you help me understand how I can tweak the code to use the file picker to create/select a folder (that's not on OneDrive) to save my file there?
At the moment, the code saves in the same location as the .xlsm, which doesn't seem to work (as that sits in a shared environment).

Appreciate the help!
Thank you!

Please Login or Register  to view this content.

Hey Kenneth,

Great, thank you! This does not use file picker, but can also work with creating a folder directly on desktop Thank you!
I tried to apply the updates to a similar code (which is to export individual files rather than one, consolidated file) and I got it to work, however, it's only creating 0kb files without a file extension at the moment (file names are correct though)...do you have any idea what I might be doing wrong? Tried a few things without luck, sadly...

Please Login or Register  to view this content.

Thanks so much!

I have not used that method. Can you attach a simple example to test?

If you did Debug.Print filename, does it show the right filename string in Immediate Window?

Hey Kenneth,

Sure thing! Attached - hope this helps?

As said, it seems to be creating files, but just not writing anything to them :-(

Thanks so much for your help!

Hi Kenneth,

Ahhh, good spot! I did not think about that! Thanks so much! Changed the file name and this works now Yaaay!

Thanks again and have a great day!