Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    open For Random As (word97/sr2)

    Don't ask me any questions about this. I think it works. I'm not sure why. Random Vs. Binary is not my strong suit.
    I'm not all that fond of the VBA Help on Get/Put, either.



    I had a need to build an array at some expense, and wanted to preserve it in a file, so that I could easily reload an array from the file, rather than rebuilding the array from the original document. That's sound thinking.

    I believed that VBA would be good enough to let me pass it an array, file it, and manage the recovery of it. In the end I'm proven correct, but I'm a slow learner, (Moderator: "I wish you'd stop saying that") and it took me a while.


    I had the divil of a job getting the LoadArray code to read back the array. With its dimensions intact.


    You will see in the sample code below that I have commented out the "Open ... Binary" and replaced them with Random. At that point the code appeared to work, so I left it at that.

    I tried Binary with a Variant type (<font color=red>Dim strAr() </font color=red> rather than <font color=448800>Dim strAr() As String</font color=448800> but that did me no good. I dislike Variant anyway; I believe it takes execution time, and I have to test elements for "Empty" instead of an empty string.

    You'll see too that I have commented out a REDIM in the <font color=red>TESTLoadCodeArray()</font color=red>. Having that REDIM in action shows that the data can be read back without loss in any case, but it's not a valid solution, because in general I never know what size array I have written. I know how many dimensions there are (2), but not necessarily how many columns or rows.

    <font color=red> C A U T I O N </font color=red> I've not performed rigorous testing on this code.

    <pre>Option Explicit
    '
    Public Const strcFileName As String = "c:temperaseme2"
    '
    Public Function LoadCodeArrayFromFile(strCodedFileName As String, strAr() As String)
    Dim intFile As Integer
    intFile = FreeFile
    ' Open strCodedFileName For Binary As intFile
    Open strCodedFileName For Random As intFile
    Get intFile, , strAr
    Close intFile
    End Function
    '
    Sub TESTLoadCodeArray()
    Dim strAr() As String
    ''''ReDim strAr(2, 8)
    Call LoadCodeArrayFromFile(strcFileName, strAr)
    End Sub
    '
    Public Function WriteCodeArray(strFileName As String, strAr() As String)
    Dim intFile As Integer
    intFile = FreeFile
    ' Open strFileName For Binary As intFile
    Open strFileName For Random As intFile
    Put intFile, , strAr
    Close intFile
    End Function
    '
    Sub TESTWriteCodeArray()
    Dim strAr() As String
    ReDim strAr(2, 8)
    strAr(0, 0) = "00"
    strAr(1, 1) = "11"
    strAr(1, 7) = "17"
    strAr(2, 8) = "28"
    Call WriteCodeArray(strcFileName, strAr)
    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: open For Random As (word97/sr2)

    Chris,

    The easiest way to write a record to a file or read the records back into an array is to use a custom datatype to hold the pieces of each record. That gives you a fixed length for each record. Then when you use the Random, you can pass it the length of a record and use that to determine both how many records already exist in the file, but also where you want to insert the record you're writing, at beginning or end or to overwrite an existing record in the middle somewhere. The most straightforward approach is to create an array of your custom datatype, which would allow you to read your values directly into the array.

    Here's an example from an old VB project of mine:

    <pre>'public array to hold data
    Public gudtSurvey() As HouseholdIncome

    'user-defined type for household income
    Public Type HouseholdIncome
    IDCode As String * 5 'holds ID
    Income As Currency 'holds household income
    Persons As Integer 'holds number of members
    End Type 'HouseholdIncome


    Private Sub LoadArray(strDataFile As String)
    'created by Charlotte Foust
    'populate global array of HouseholdIncome
    Dim strPath As String 'holds the current path
    Dim intLoop As Integer 'controls looping through array
    Dim HIncome As HouseholdIncome 'holds each type object

    'get the current application path
    strPath = GetPath()

    'open the file and get the record count
    Open strPath & strDataFile For Random As #1 Len = Len(HIncome)
    mintCount = LOF(1) / Len(HIncome)

    'if there are records, load the array
    If mintCount > 0 Then

    'dimension the array to the current size
    ReDim gudtSurvey(1 To mintCount)

    'loop through the records in the file
    For intLoop = 1 To mintCount

    'get each record and store it
    'to the Survey object
    Get #1, intLoop, HIncome
    With HIncome
    gudtSurvey(intLoop).IDCode = HIncome.IDCode
    gudtSurvey(intLoop).Income = HIncome.Income
    gudtSurvey(intLoop).Persons = HIncome.Persons

    'update the total income aggregate value
    mcurTotalIncome = mcurTotalIncome + HIncome.Income

    'increment the counter for below poverty level
    If HIncome.Income < _
    PovertyLevel(HIncome.Persons) Then
    mintBelowCnt = mintBelowCnt + 1
    End If 'HIncome.Income < _
    PovertyLevel (HIncome.Persons)
    End With 'HIncome
    Next intLoop '= 1 To mintCount

    'close the data file
    Close #1

    End If 'mintCount > 0

    End Sub 'LoadArray()</pre>

    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    >fixed length for each record.

    I think this may be valid when one is dealing with fixed-length items. The VBA help gives a pretty example using Integer, where each item in the array takes 2 byytes, so 2 bytes * 5 cols * 10 rows is 100 bytes. Sure!

    In my case I have a 2-dimensional array (rows & columns) of variable-length strings.

    It's fatal to assume a maximum length for any one string, because (1) the day after you do that the user will employ a longer string and (2) you end up assigning WayMore space than you need 99% of the time.

    I have modified my original code slightly to cater for my not believing the help files. The code is now working again.

    I suspect that if I were going to generalise this, I'd need a few slave routines that could analyse, internally, the type of data, its dimensions, and size elements.

    I can't see a LenVar() function that would tell me, simply, how many bytes of storage a data variable was using. Armed with that I'd be happy enough adding a loading factor of 10% and nutetring "close enough for government work".

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    I've added a LENGTH calculation. See also my reply to Charlotte's post regarding the length of a variable:

    The length calculation isn't accurate; it is a tad longer, I think, than it needs be. But at least it doesn't fall short.

    I've attached the Help file text and a complete copy of the function.

    <pre>(snip)
    Dim lngFilelength As Long
    lngFilelength = 2 + 8 * 2 + ((1 + UBound(strAr, 1)) * (1 + UBound(strAr, 2)))
    lngFilelength = 2 + 8 * 2
    ' This calculation is excessive by a small portion
    Dim i As Integer
    Dim j As Integer
    For i = 0 To UBound(strAr, 1)
    For j = 0 To UBound(strAr, 2)
    lngFilelength = lngFilelength + 2 + Len(strAr(i, j))
    Next j
    Next i
    Open strFileName For Random As intFile Len = lngFilelength
    (snip)
    </pre>

    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: open For Random As (word97/sr2)

    The point is that when you use Random, you have to know the fixed length of a record. Otherwise, you have no way of determining the end of a record. You might be able to write the text, but you wouldn't be able to read it again since Random uses position to determine the end of record.
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    >

    Right. But in this case I can't know the length of the record when I'm reading it back. I can calculate the length(as in my example posted earlier), but the next day, when I want to fire up the application and obtain my 2-dimensional array of strings, I have no way of knowing how long the recrod is.

    I think.

    Let's see, I switched from Binary to Random when I couldn't get Binary working. Mebbe I should take another look.


    I understand the concepts of random, been using them for years, but I'm never sure about MSoft's use of terms.


    My big puzzle remains: Why, since VBA knows all about the data item (in this case a 2-D array of strings), doesn't it provide the option to write a variable out to disk, and to read it back again?

    I can write my own package-a-variable routine for simple variables. In my specific example, I do know it's a 2D array of strings, so I can store values in sequence that indicate:
    <pre>type=string
    dimensions=2
    1st dimension=8
    2nd dimension = 15
    data = ( here it comes ......)
    </pre>


    What a shame that VBA doesn't support this. Especially for variants, where the line seems to be "MSOFT/VBA will take care of what your data looks like, effect translation etc").

    I don't think I'm complaining. It just seems very odd that there appears to be no easy way of storing mediocre chunks of data on disk without getting into convulted calculations that ought to be the bailiwick of the VBA engine.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: open For Random As (word97/sr2)

    Because text files are *not* arrays. A typical text file created using Open for Write just keeps concatenating text to the file. You have to provide the structure when you read it. This isn't new to VB/VBA. The reason for a fixed length is so you control where the beginning and ending of a record is. If you aren't sure, create a structure that holds the maximum size and pad the strings to fit. That is essentially what my example did.
    Charlotte

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: open For Random As (word97/sr2)

    Charlotte, I think we should try to convince Chris to bite the bullet and use ADO for storing his array, as demonstrated in the attached .bas file. What do you think?

    My only frustration with this method is having to declare a maximum field length, rather than having the flexibility to write strings of any size. I think it is possible to have truly variable length strings if they are null-terminated (adBSTR), but then there's that messy null to deal with, and one reference source says it is Unicode...
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: open For Random As (word97/sr2)

    If I were doing it in Access, I would just open/build an ADO recodset and the persist it to either a recordset or an XML file. Then all you have to do is open it:

    Set rst = New ADODB.Recordset
    With rst
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open strRecSetName, "Provider=MSPersist", , , adCmdFile
    .ActiveConnection = CurrentProject.Connection
    End With
    Charlotte

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    >Because text files are *not* arrays. A typical text file created using Open for Write just keeps c

    Right.


    > so that I could easily reload an array from the file,

    Now, back to my original foray: How does one easily write and read back string arrays from disk, without a lot of programming?

    I still feel that there's something missing

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    >as demonstrated in the attached .bas file.

    Thanks for this. I will fire it up and see what happens.

    I'm always tempted to write a real neat function to do something, but I'm trying to see if first there isn't an inbuilt method.

    I can write a function to build a descriptor, even of my own design, for a variable-rank, variable dimension, variable type, variable item length without too much trouble. I grew up that way.

    More and more I see that just when I think I need to write a function, VBA has a built-in method.

    That's the thrust of this enquiry.

    If truly VBA provides no way to dump an array of variable length strings to disk, and to read it back correctly into a dynamic array, then a separate function it has to be.


    Being a purist, of course, I'll have to take a week off and write a fully-generalised function with brass plates and red trim.


    > the flexibility to write strings of any size

    Yes; here's another sticking point with me. Clearly VBA has the ability to deal with this situation in RAM memory - we slide strings and dynamic arrays of variable length strings around all the time. Deep in the bowels of VBA there is a "package" that represents my dynamic array.

    It seems to me that instead of being able to lay my hands on this package and deliver it over to the I-O routines, I have to build my own version of what VBA already has - disassembling the array to build the array.

    I don't mind doing that ("neat function"), but am trying to see if there's a way to avoid writing code that duplicates what exists in VBA.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    Thanks. I'll check this out with Jscher's code.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: open For Random As (word97/sr2)

    Well, your problem is you are using VBA 5 and not VBA 6, as in Office 2000. In VBA 6, you would do this:

    strStringRecord = Join(strArray, strDelimiterOfChoice)

    and write strStringRecord to disk. You then could rebuild your array with the Split function,

    strArray = Split(strStringRecord, strDelimiterOfChoice)

    and you're off and running. Just like that.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    Oh. Like this or these strsplitstringat. Sure (grin).

    I was trying to avoid doing housework.

    My premise is still not "how to program around this", but "why doesn't VBA take care of it since it already has the stuff it needs?".

    If I do go the way of parsing and rebilding the string array, so be it, but my prime motive for wanting to dump an entire array and to read it back was to avoid re-processing of any kind, saving time (maybe) but also inadvertent changes by the user.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open For Random As (word97/sr2)

    Here's where I'm at with this problem.

    The test routine below demonstrates retrieval of a string array of variable-length strings from a file.

    I have commented out the lines that would normally, one-time only, create the file from a document obtained through "ObtainCodeArray(strAr) "; I have attached the created file "myVar.txt" from my c:temp directory.

    If you were to drop the attached file "myVar.txt" in your c:temp directory and run the procedure below, you should find that the string array strBr is loaded with 33 rows of 9 elements each. The front of the file when examined with a hex-viewer reveals the twin 3-byte strings from the "Format(UBound(strAr, 1), "000")" lines.

    In creating the file, designed specifically for the storage of a 2-dimensional string array, I decided to use 3-byte string forms of the dimensions - 3 bytes because it made it easier for me to see what is going on with the hex view; 5-byte strings would make more sense ("32767" the limit?).

    Anyway, I make the call to ObtainCodeArray to parse a largeish table in a Word document, discard the stuff I don't really need today, then store the strAr() in a file for fairly fast recall.

    When you consider that parsing, for me, can include testing to see if nominated styles exist and so on, parsing in advance makes sense, and being able to call up a file of pre-checked commands as I process each of thousands of documents ought to make sense.

    I could probably generalise this routine, using Integer Constants to describe the length of the formatted strings etc. Maybe it ought to be a Class .....

    <pre>Sub TESTBuildRulesFile()
    ' Obtain a rules table
    Dim strAr() As String
    ReDim strAr(8, 0)
    ' Call ObtainCodeArray(strAr) ' creation

    ' Save the table to disk/RAM
    Dim intFile As Integer
    intFile = FreeFile
    ' Open "c:tempmyVar.txt" For Binary As intFile ' creation
    ' Put intFile, , Format(UBound(strAr, 1), "000") ' creation
    ' Put intFile, , Format(UBound(strAr, 2), "000") ' creation
    ' Put intFile, , strAr ' creation
    ' Close intFile ' creation

    ' Reload the table to a new variable
    Dim strBr() As String
    Open "c:tempmyVar.txt" For Binary As intFile
    Dim strUbound1 As String
    Dim strUbound2 As String
    strUbound1 = String(3, " ")
    strUbound2 = String(3, " ")
    Get intFile, 5, strUbound1
    Get intFile, 12, strUbound2
    ReDim strBr(Val(strUbound1), Val(strUbound2))
    Get intFile, 15, strBr
    Close intFile

    End Sub
    </pre>

    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •