0

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?

jisner
  • 13
  • 3
  • I believe if you replace `Write` with command `Print` you will succeed – Daniel Sep 08 '22 at 04:28
  • 1
    **Because it is supposed to**. *Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file. You don't have to put explicit delimiters in the list. Write # inserts a newline character, that is, a carriage return-linefeed (Chr(13) + Chr(10) ), after it has written the final character in outputlist to the file.* https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/writestatement – Lundt Sep 08 '22 at 05:26
  • And it's the same format that Input# expects. *Data written with Write # is usually read from a file with Input #.* – Lundt Sep 08 '22 at 05:35

1 Answers1

1

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)

GWD
  • 3,081
  • 14
  • 30