Why is Excel vba adding quotation marks when I append to a file?

jisner 0
Viewed 47

This is the first time I have tried to write a file from Excel using Open. I normally use FSO, but this code has to run on both Windows and MacOS.

Dim myFile As String
myFile = Application.ActiveWorkbook.Path & "\" & "CGATS.txt"
Open myFile For Append As #1
Write #1, "NUMBER_OF_FIELDS"
Write #1, "BEGIN_DATA_FORMAT"
Write #1, "SAMPLE_ID"
Write #1, "END_DATA_FORMAT"
Write #1, ""
Write #1, "NUMBER_OF_SETS" & vbTab & numRows
Write #1, "BEGIN_DATA" & vbTab & "LAB_L" & vbTab & "LAB_A" & vbTab & "LAB_B"

The code runs, but every line of output is surrounded by quotation marks

"NUMBER_OF_FIELDS"
"BEGIN_DATA_FORMAT"
"SAMPLE_ID"
"END_DATA_FORMAT"
""
"NUMBER_OF_SETS 52"
"BEGIN_DATA LAB_L   LAB_A   LAB_B"

Why is Excel doing this, and how do I get rid of the quotation marks?

vba excel
1 Answers

There are several things that stand out about your code.

  1. First of all, you are just using filenumber:=#1, this will throw an error if this filenumber already happens to be in use. Better is to use the FreeFile function to find a free file number.

  2. Also you are not currently using a close statement. This is exactly a reason why the potential error mentioned in 1. might appear.

  3. I assume, you want to base the file path of the created file on the workbook in which this macro is contained. Therefore, you shouldn't use Application.ActiveWorkbook to refer to this but rather, Application.ThisWorkbook, specifying Application is unnecessary, so you can instead just write ThisWorkbook to refer to, well, this workbook.

  4. Since you are explicitly stating that your code should work on MacOS, you can't just use "\" as your path separator. Instead of that, use Application.PathSeparator.

  5. And lastly, your actual question has of course already been answered in the comments by @Daniel and @Lundt. To avoid adding quotation marks around every line, just use Print instead of Write.

The fully refactored code might look something like this:

Sub Test()
    Dim fileFullName As String
    fileFullName = ThisWorkbook.path & Application.PathSeparator & "CGATS.txt"
    
    Dim fn As Long
    fn = FreeFile()
    
    Open fileFullName For Append As #fn
        Print #fn, "NUMBER_OF_FIELDS"
        Print #fn, "BEGIN_DATA_FORMAT"
        Print #fn, "SAMPLE_ID"
        Print #fn, "END_DATA_FORMAT"
        Print #fn, ""
        Print #fn, "NUMBER_OF_SETS" & vbTab & numRows
        Print #fn, "BEGIN_DATA" & vbTab & "LAB_L" & vbTab & "LAB_A" & vbTab & "LAB_B"
    Close #fn
End Sub

Note:

If your Workbook is located in a synced SharePoint or OneDrive folder, this code will still fail because ThisWorkbook.Path will then return the OneDrive Url, which the open statement can not deal with. In that case you have to copy the code from this answer and convert the path using the function by using GetLocalPath(ThisWorkbook.path)

GWD0
Related