Hello Dear StackOverFlowers,
My question may be trival but I'm currently out of option to think of after searching all afternoon
Context: I have a excel worksheet with 120 rows or so that I need to use to create files with.
Data is structured as follow: The A column contains destination file names
B column has the corresponding data that needsto be written in each file
Giving us the following general layout
So, to get data from B column written in each A column named files, I wrote the followin VBScript snippet:
Option Explicit Sub writeExportedMsgToXML() ' wrote that tiny script not to have to copy pate 117 messages by hand to have ops put them back on Q Dim currentRow As Integer ' modify to match your data row start and end For currentRow = 2 To 11 Dim messageID As String Dim messageitSelf As String messageID = Trim(ActiveSheet.Range("A" & currentRow)) messageitSelf = ActiveSheet.Range("B" & currentRow) Dim subDirectory As String subDirectory = "xmls" Dim filePath As String filePath = ActiveWorkbook.Path & "\" & subDirectory & "\" & messageID & ".xml" MsgBox (messageitSelf) ' for test purpose Open filePath For Output As #1 Write #1, messageitSelf Close #1 Next currentRow End Sub
The script does mostly what it's intended for Except , and this is the source of my question today, it enclose the file content between double quotes as you can see below:
So, in a case where a file named F1.xml should just contain
My script transform it as
What I tried
Replacing file writing part with the following
Dim objStream Set objStream = CreateObject("ADODB.Stream") objStream.Charset = "UTF-8" Dim subDirectory As String subDirectory = "xmls" Dim filePath As String filePath = ActiveWorkbook.Path & "\" & subDirectory & "\" & messageID & ".xml" objStream.Open objStream.WriteText messageitSelf objStream.SaveToFile filePath objStream.Close
With same outcome
Any clues on what I'm missing/Doing wrong ?
Should I declare
messageitSelf as a different type ?
Any help would be appreciated :)