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:
Sub SaveAsUTF8HTMLall()
Dim Cell As Range
Dim filename As String
Dim Rng As Range
' ADODB.Stream file I/O constants
Const adCrLf As Long = -1
Const adSaveCreateNotExist As Long = 1
Const adSaveCreateOverWrite As Long = 2
Const adTypeText As Long = 2
filename = ThisWorkbook.Path & "\Export" & Replace(Sheets("Settings").Range("C31").Value, " ", "_") & ".html" 'cell C31 contains the file name of the consolidated HTML
Set Rng = Worksheets("Settings").Range("C33:C129") 'this is the range that holds the HTML content to consolidate
On Error Resume Next
With CreateObject("ADODB.Stream")
.Open
.Type = adTypeText
.Position = 0
.Charset = "utf-8"
.LineSeparator = adCrLf
For Each Cell In Rng
.WriteText Cell.Text
Next Cell
.SaveToFile filename, adSaveCreateOverWrite
.SetEOS
.Close
End With
Application.ScreenUpdating = True
End Sub
Hope someone can help this VBA newbie here? 
Thanks!
Bookmarks