Why is VBA script adding double quotes at start/end of files

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

data file 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:

file content enclosed in double quotes

So, in a case where a file named F1.xml should just contain <foo><bar>Baz</bar></foo>

My script transform it as "<foo><bar>Baz</bar></foo>"

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 :)

Thank you



Read more here: https://stackoverflow.com/questions/66344075/why-is-vba-script-adding-double-quotes-at-start-end-of-files

Content Attribution

This content was originally published by Laurent Morissette at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: