In Excel VBA (2019) I am trying to write out a bridge deal file format of type .bri The specifications for .bri require repeated (depending how many boards in the total deal) sets of

1. The 39 ASCII codes for cards in each board
2. 10 normally ASCII spaces
3. 22 further ASCII spaces
4. 18 further bytes of null-value (all bits set to 0) as one continuous line in the file.

An example sample of code that I have tried writes just 1 board via:

Type BriType
  boardVar As String
  byteArr(17) As Byte
End Type
Sub TestBriWrite()
Dim i, j, k, l, m As Integer
Dim FileName As String, fileNo As Integer
Dim FileLoc As Variant
Dim filler As String
Dim BriVar As BriType
  BriVar.boardVar = "010203040506070809101112131415161718192021222324252627282930313233343536373839                                "
  For m = 0 To 17
    BriVar.byteArr(m) = 0
  Next m
  FileLoc = Application.GetSaveAsFilename(Title:="Save the Deal File as xxxxx.bri")
  fileNo = FreeFile
  Open FileLoc For Binary As #fileNo
  Put #fileNo, 1, BriVar
  Close #fileNo
End Sub

This seems to be close to what I want but each board entry is preceeded by "n " in the output file. Can anyone suggest how I can achieve what I am trying to do?

Sorry, this is my first post on the board so I may not have followed all of the rules.

CodePudding user response：

Since BriVar.boardVar is declared as a variable length string, the first two bytes are being used to keep track of its length for subsequent reading.

In your example, you're assigning your variable a 110 character string. So it uses two bytes to store that value in your file. The first byte has the value of 110, and the second byte has the value of 0.

And so when you open your file in a text editor, you're seeing the character representation of the ASCII values 110 and 0, which are the letter 'n' and the null character.

You can avoid these two bytes by declaring a fixed length string instead. For example, let's say that your string will always be 110 characters in length. Then you would declare your string as follows...

Type BriType
  boardVar As String * 110 '<--- fixed length string
  byteArr(17) As Byte
End Type

By the way, you should amend your code so that it exits the sub when the user is prompted to save the file, and clicks on Cancel....

FileLoc = Application.GetSaveAsFilename(Title:="Save the Deal File as xxxxx.bri")

If FileLoc = False Then Exit Sub '<-- user cancelled

So here's your macro amended as above...

Type BriType
  boardVar As String * 110
  byteArr(17) As Byte
End Type

Sub TestBriWrite()

    Dim i, j, k, l, m As Integer
    Dim FileName As String, fileNo As Integer
    Dim FileLoc As Variant
    Dim filler As String
    Dim BriVar As BriType
    
    FileLoc = Application.GetSaveAsFilename(Title:="Save the Deal File as xxxxx.bri")
    
    If FileLoc = False Then Exit Sub
    
    BriVar.boardVar = "010203040506070809101112131415161718192021222324252627282930313233343536373839                                "
    
    For m = 0 To 17
      BriVar.byteArr(m) = 0
    Next m
    
    fileNo = FreeFile
    Open FileLoc For Binary As #fileNo
        Put #fileNo, 1, BriVar
    Close #fileNo
  
End Sub