Join arrays in VBA script to write to file

Question

0.00/5 (No votes)

See more:

I have a piece of code that I need to basically put all contents of a huge buffer output to a file. So far I am able to get it working with a smaller buffer, but if I try to use a really large buffer, I get too many line continuation errors, which means I need to break it up? Is there any way I can have multiple Arrays possibly and join them to 1 file, perhaps through concatenation? I know the concept of using strings and byte Arrays but in this case, Im only able to use my Array as a Variant, not as Byte. I'm new to VBA scripting so I'd to learn from this example. Thanks.

VB

Sub WriteBinary(FileName, buf)

    Dim I, aBuf, Size, bStream
    Size = UBound(buf): ReDim aBuf(Size \ 2)
    For I = 0 To Size - 1 Step 2
        aBuf(I \ 2) = ChrW(buf(I + 1) * 256 + buf(I))
    Next
    If I = Size Then aBuf(I \ 2) = ChrW(buf(I))
    aBuf = Join(aBuf, "")
    Set bStream = CreateObject("ADODB.Stream")
    bStream.Type = 1: bStream.Open

    With CreateObject("ADODB.Stream")
        .Type = 2: .Open: .WriteText aBuf
        .Position = 2: .CopyTo bStream: .Close
    End With

    bStream.SaveToFile FileName, 2: bStream.Close
    Set bStream = Nothing

End Sub

Sub Test()

    Dim buf As Variant
    buf = Array(184, 97, 61, 32, 0, 4, ....)
    WriteBinary "C:\file.txt", buf

End Sub

My new code edited is below. I was able to get an array out of strings I suppose utilizing the net framework ArrayList but I am still having trouble with WriteBinary using it. I know that there is a difference between an Array(byte code) and Array "byte code". I just dont know how to make the latter work with my function.

VB

Sub WriteBinary(FileName, buf)

    Dim I, aBuf, Size, bStream
    Size = UBound(buf): ReDim aBuf(Size \ 2)
    For I = 0 To Size - 1 Step 2
        aBuf(I \ 2) = ChrW(buf(I + 1) * 256 + buf(I))
    Next

    If I = Size Then aBuf(I \ 2) = ChrW(buf(I))
    aBuf = Join(aBuf, "")
    Set bStream = CreateObject("ADODB.Stream")
    bStream.Type = 1: bStream.Open
    With CreateObject("ADODB.Stream")
        .Type = 2: .Open: .WriteText aBuf
        .Position = 2: .CopyTo bStream: .Close
    End With

    bStream.SaveToFile FileName, 2: bStream.Close
    Set bStream = Nothing
End Sub

Sub PrintToImmediateWindow(coll As Object)

    Dim I As Long
    For I = 0 To coll.Count - 1
        Debug.Print coll(I)
    Next I
    
End Sub

Sub PrintArrayToImmediate(buf As Variant)
        
    Dim I As Long
    For I = LBound(buf) To UBound(arr)
        Debug.Print buf(I)
    Next I
End Sub

Sub Main()

    Dim coll As Object
    Set coll = CreateObject("System.Collections.ArrayList")

    coll.Add "14, 99, 104, 0, 0, 0, 65, 112, 114, 105,"
    coll.Add "255, 255, 104, 76, 43, 32, 23, 56, 77,"
    'so on and so forth

    Dim arr As Variant
    buf = coll.ToArray
    
    'Print the array
    'Debug.Print vbCrLf & "Printing the array contents:"
    
    'For printing arrays as Variants
    'PrintArrayToImmediate buf
    
    'For printing arrays as coll objects
    'PrintToImmediateWindow coll
    
    WriteBinary "C:\file.txt", buf
End Sub

What I have tried:

So far I am able to get it working with a smaller buffer, but if I try to use a really large buffer, I get too many line continuation errors, which means I need to break it up

Posted 1-Jun-22 8:35am

Franz Schweizer

Updated 2-Jun-22 15:46pm

v3

Add a Solution

Comments

Dave Kreskowiak 2-Jun-22 0:37am

Are you saying that you want to make this:

buf = Array(184, 97, 61, 32, 0, 4, ....)

with as many values as you want to type into the array?

buf = Array(184, 97, 61, 32, 0, 4, 6, 54, 23, 34 _
        45, 34, 24, 65, 87, 45, 76, ..., _
        56, 26, 86, 65, 87, 9, 84, ..., _
        ... more values ad nauseum )

Franz Schweizer 2-Jun-22 0:47am

Exactly, Dave. Yes. I am not able to use more than 24 of those line separators and I have an enormous amount of values to enter and I'm just not sure how to go about doing this. If I could somehow append to the file, then I could call my function about a thousand times but if not, I'm not sure what else I could do. This VBA language is very limiting and makes no sense that I can't simply use a long line..

Dave Kreskowiak 3-Jun-22 0:08am

Yeah, you cannot expect the interpreter to allow unlimited statement length. That's what the line continuation character is doing. Actually, no language that I'm aware of will allow you to use an unlimited number of continuations.

I can't tell what you're really tying to do. In one example, you're writing a bunch of byte values to a file (and calling it a .txt file) and in your other example, you're writing a bunch of text that is NOT equivalent to your first example.

So what's the actual goal? What needs to be in the file? Is it bytes or text?

Franz Schweizer 4-Jun-22 21:32pm

any ideas?

Dave Kreskowiak 4-Jun-22 21:41pm

Since you didn't reply to one of my posts, I had no idea you posted anything.

Dim buf As String()
buf(0) = "This is a string"
buf(1) = "This is another string"

As for byte data, I have no idea how you're representing your data so it's difficult to say.

If the data is the same between all copies of the file, you can just create your data in an actual file and copy it to a new file when needed.

Franz Schweizer 4-Jun-22 21:47pm

Sorry i am new to this forum, i realized i did not reply to you. Basically, my goal is pretty simple. Create a binary file out of byte code. All those numbers are just decimal format of byte code of a file. It can't come from an external source, but must remain internal. So actually I have created an arrayList of strings because it was the only way to actually get the data into the window without Word crashing. 1700 lines each of 30 decimal numbers delimited by a comma. Basically I need to convert the entire array of strings to one large single dimension array. So

coll.Add "77, 90, 144, 0, 3, 0, 0, 0, 4, 0, 0, 0, 255, 255, 0, 0, 184, 0, 0, 0, 0, 0, 0, 0, 64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "
coll.Add "1700 lines"
...
...

Need to convert to buf = Array(77, 90, 144, 0, 3, 0, 0, 0, 4, 0, 0, 0, 255, 255, 0, 0, 184, 0, 0, 0, 0, 0, 0........ 100,000 bytes) or re-write the WriteBinary function to accept my arrayList and be able to process each element separately rather than each element as 30 numbers. Does that make sense?

Dave Kreskowiak 4-Jun-22 21:49pm

That doesn't really make sense. Why are you writing strings with WriteBinary?

Franz Schweizer 4-Jun-22 21:54pm

Well, WriteBinary is designed to just take buf = Array(77, 90, 144, 0, 3, 0, 0, 0, 4, 0, 0, 0, 255, 255, 0, 0, 184, 0, 0, 0, 0, 0...) but cannot use 100,000 arguments. Any other ideas?

Dave Kreskowiak 4-Jun-22 22:06pm

Oh, WriteBinary can write an array of 100,000 bytes, no problem.

What you cannot do is try to define an Array statement with an arbitrarily large number of line continuations. That has nothing to do with what WriteBinary supports. It's a limitation of the VB.NET complier. Frankly, every compiler has some limit like that.

You could represent your data in smaller blocks bytes, each block would being an array of bytes, then add each block to a List(Of Byte()):
VB.NETCopy Code
Dim buffer As New List(Of Byte()) buffer.Add(New Byte() {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}) buffer.Add(New Byte() {100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115}) buffer.Add(... )

Of course, you'll have to modify the code to write this data to the file. Just enumerate over the list and pass each array to your files WriteBinary method.
Whoops. I forgot, you're using VBA, not VB.NET.

Franz Schweizer 4-Jun-22 22:13pm

that explains it all, yes

Dave Kreskowiak 4-Jun-22 22:21pm

In VBA, the equivalent would be:

Dim buffer As Collection

buffer.Add Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
buffer.Add Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)


For Each buf In buffer
    WriteBinary "some filename", buf
Next

Mind you, I haven't tested that at all. It's what I would start with.

Franz Schweizer 5-Jun-22 3:02am

Well I can tell you right off the bat without testing it that it will overwrite the file with each write. It will not append to the file. That's the issue I am facing. Also i just tried this and it threw an error "Object variable or With block variable not set"

Dave Kreskowiak 5-Jun-22 12:28pm

Like I said, you have to rewrite the code that writes the file. You can include methods to open the file, your WriteBinary method, and a method or two to finish writing the file and close it.

Franz Schweizer 5-Jun-22 13:10pm

after doing research, you're just missing 'New' in front of Collection but that does work, but as expected, it only writes the 2nd part. I did further digging and found this Collection to Array function which seems to work perfectly fine but when I try to print or pass this array off to WriteBinary, i get a type mismatch:

Public Function CollectionToArr(myCol As Collection) As Variant

Dim result As Variant
Dim cnt As Long

If myCol.Count = 0 Then
CollectionToArr = Array()
Exit Function
End If

ReDim result(myCol.Count - 1)
For cnt = 0 To myCol.Count - 1
result(cnt) = myCol(cnt + 1)
Next cnt
CollectionToArr = result

End Function

Sub Main()
Dim buf As Variant

Dim buffer As New Collection
buffer.Add Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
buffer.Add Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

buf = CollectionToArr(buffer)

'For Each buf In buffer
'WriteBinary "C:\file.bin", buf
'Next
' For printing arrays as Variants
'PrintArrayToImmediate buf

WriteBinary "C:\file.bin", buf
End Sub

Dave Kreskowiak 5-Jun-22 13:19pm

That's because your CollectionToArr is returning a Variant, not a typed Array. A Variant is a "container" type that holds whatever you put in it, no matter its actual type.

The WriteBinary method is expecting an Array, not a Variant.

You seem to be hell-bent on writing the file in a single write. DON'T! Write it in chunks.

You just need to add the code to open the file, write the data in the collection as individual arrays being passed to a re-written WriteBinary that only writes the data and DOES NOT OPEN OR CLOSE THE FILE, and another method to finalize the write and close the file.

You're trying to do it the hard way.

Franz Schweizer 5-Jun-22 14:02pm

i agree. i have been trying to append as you are mentioning, to open and close. i know i can append to a text file since this works here. just trying to re-write this crazy WriteBinary function but you have helped tremendously.

Sub VBA_to_append_existing_text_file()
Dim strFile_Path As String
strFile_Path = "C:\test.txt"
Open strFile_Path For Append As #1
Write #1, "This is my sample text"
Close #1
End Sub

Franz Schweizer 5-Jun-22 14:15pm

the issue is the function has this whole stream crap involved which you can see it saves this stream. its complicated. tried removing that and just writing it but the data is incorrect

Franz Schweizer 5-Jun-22 17:17pm

well i got an update. i tried adding all the data using this method and it came back with Procedure too large. the only method that actually seemed to allow me to copy all the data was using the string method with the arraylist initially.

Dave Kreskowiak 5-Jun-22 19:31pm

From the Office documentation:
When compiled, the code for a procedure can't exceed 64K. This error has the following cause and solution:

Code for this procedure exceeds 64K when compiled. Break this, and any other large procedures, into two or more smaller procedures.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Franz Schweizer 6-Jun-22 2:55am

thanks. i have some work to do to think about how to go about getting this done, might have to take a crash course on VBA

Dave Kreskowiak 6-Jun-22 7:41am

Yeah, I didn't even know that was a limitation in VBA.

Franz Schweizer 6-Jun-22 13:18pm

believe it or not, im not even able to get 64k out of it either. in fact, im only able to pass off 2400 bytes before it barks at me with Procedure too large. The other method i had with the coll.Add "1,2,3,4,5,6,7,8" strings actually allowed me to add all the data no problem. So I may have to work with that. For that, I need to somehow be able to use Split method and be able to build an array out of that but not even sure if that will also not bark at me too.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2022-06-01T16:10:00

Solution 1

Quote:
Join arrays in VBA script to write to file

The solution: don't join.
your code is OK with unlimited memory, which is not the case with your computer.
Look at your code, you join elements of an array only to write it is a single go in a file, and then discard the joined variable.

This way of coding will work with any size of buf and is much faster

VB

Sub WriteBinary(FileName, buf)
   Dim I, aBuf, Size, bStream
    Size = UBound(buf)
    ' Open the file
    For I = 0 To Size - 1 Step 2

         Tmp = ChrW(buf(I + 1) * 256 + buf(I))
         ' Write Tmp to file
    Next
    If I = Size Then
        Tmp = ChrW(buf(I))
        ' Write Tmp to file
    End If
    ' Close file
End Sub

[UpDate]

Quote:
Join arrays in VBA script to write to file

Your problem is not about joining array.
Your problem is about source not compiling and you have been carefull to remove offending code.
Do not wonder why you get answer on wrong topic.

Posted 1-Jun-22 16:10pm

Patrice T

Updated 1-Jun-22 20:16pm

v2

Comments

Franz Schweizer 1-Jun-22 22:36pm

not able to use this function at all. missing half of it to even write to a file. also, even if my function is able to use any amount of buf, in vba, i physically cannot have a huge array. it just doesn't work, throws a compile error. whoever programmed it does not let you use an enormous array or string and i can only use 24 of those line separators which would not be enough. i need either a way to concatenate strings such as

buf = "97,97,97,97"
buf = buf & "77,241,45,54"
buf = buf & ...

and then turn this concatenation into a true Variant array that I can then pass off to my function or some way for me to keep calling the function and it append to the file. My functions works perfectly fine. I just have to bypass these vba limitations with the length of an array. Any ideas?

Patrice T 1-Jun-22 22:44pm

"missing half of it to even write to a file."
Comments should give you enough hints to write the missing code by yourself.

Patrice T 1-Jun-22 22:47pm

Use Improve question to update your question.
So that everyone can pay attention to this information.

Franz Schweizer 1-Jun-22 22:56pm

I added the bottom half, writing Tmp instead of aBuf but it only wrote 2 bytes of 0xff to the file.

Patrice T 1-Jun-22 23:07pm

Use Improve question to update your question.
Show your new code too.

Franz Schweizer 1-Jun-22 23:05pm

i think you may have missed the point here. my function works fine. the issue is that i cannot use a large massive array in vba. it won't compile. i am looking for a way to be able to either create an array without having to use a large one-line array or some other method, perhaps a modification to this function. but all you really did was replace by aBuf with Tmp but without the join, it doesn't quite work.

Patrice T 1-Jun-22 23:10pm

You forgot to say it is a compile time error, and your code do not show the problem.
So the problem is not the join.

Franz Schweizer 1-Jun-22 23:26pm

well i did mention everything in my original post? not sure if there was a mis-communication somewhere but my code works perfectly fine. its just the native use of any long lines in VBA throws a syntax error. its simply too long of a line, no matter what. and i have a very very long line. is there a way i can convert a string of bytes such as "61, 61, 61, 61, 76, 240..." to a Variant array? I heard about MID, just not sure what to do anymore. i need to basically pass off strings to another function and have it return this large array that i can then pass off to my WriteBinary function.

Franz Schweizer 2-Jun-22 2:56am

Patrice, there is no offending code. You can try to use any byte code you please. Try using a 100,000 byte array, it will not let you compile for some reason.