Best way to extract a VBA Date out of a byte array and turn it back into a valid VBA Date?

Question

I have confirmed that the following VBA code works just fine.

Dim mydate As Date
mydate = Date
Open "C:\Windows\Temp\dlctest.data" For Binary Access Write As #1
Put #1, , mydate
Close #1
Open "C:\Windows\Temp\dlctest.data" For Binary Access Read As #1
Get #1, , mydate
Close #1
MsgBox "MyDate = " & mydate

But the actual file I want to work with was created by a separate product and has multiple entries in it--each entry with multiple date, time, and decimal fields and other single-byte character fields. The entries are only fixed length (329 bytes) after the header record--which is shorter (94 bytes).

I would like to read and save the header, then read each entry in its entirety--afterwards, parsing out the fields as needs be. But, that is my question...

What is the best way to take a binary date, e.g., out of a byte array, and turn it back into a valid VBA date?

For testing purposes, I tried creating Type blocks for the two record types.

Private Type schedulerHeader    ' reclen = 94
    HeaderVersion(25 - 1) As Byte
    HeaderFiller1(69 - 1) As Byte
End Type
Private Type schedulerEntry     ' reclen = 329
    EntryFiller1(6 - 1)   As Byte
    EntryName(100 - 1)    As Byte
    EntryEvent(8 - 1)     As Byte
    EntryBegStamp(15 - 1) As Byte
    EntryOptions(7 - 1)   As Byte
    EntryFiller2(14 - 1)  As Byte
    EntryRunStamp(15 - 1) As Byte
    EntryEndStamp(15 - 1) As Byte
    EntryNxtStamp(15 - 1) As Byte
    EntryFiller3(126 - 1) As Byte
    EntryTag(8 - 1)       As Byte
End Type

But after reading from the file, all byte arrays were empty. I even tried initializing all of the byte arrays before reading from the file.

    For i = 0 To UBound(myHeader.HeaderVersion)
        myHeader.HeaderVersion(i) = 32
    Next i

What is the better way to read this file--or what is wrong with the above method?

Answer 1 · 2025-11-18 23:57:33Z

Tim Williams

• Nov 18 at 23:57

Might be useful to include exactly how you read the file into the records?

Answer 2 · 2025-11-19 14:31:04Z

OK, I used these variables--with initialization to spaces.

Dim tmpString As String
Dim myHeader As schedulerHeader
Dim tempHeader(94 - 1) As Byte
    For i = 0 To UBound(tempHeader)
        tempHeader(i) = 32
    Next i
Dim myEntry  As schedulerEntry
Dim tempEntry(329 - 1) As Byte
    For i = 0 To UBound(tempEntry)
        tempEntry(i) = 32
    Next i

Then for testing purposes, I read from the file and examined the header and first three entries as follows.

    Open fileName For Binary Access Read As #fileNumber ' open the schedule file
'
    Get #fileNumber, , tempHeader
    CopyMemory myHeader, tempHeader(0), LenB(myHeader)  ' copy to my header structure
    tmpString = ""
    For i = 0 To UBound(myHeader.HeaderVersion)
        tmpString = tmpString & Chr$(myHeader.HeaderVersion(i))
    Next i
    MsgBox "Version = " & tmpString
'
    Get #fileNumber, , tempEntry
    CopyMemory myEntry, tempEntry(0), LenB(myEntry)     ' copy to my entry structure
    tmpString = ""
    For i = 0 To UBound(myEntry.EntryName)
        tmpString = tmpString & Chr$(myEntry.EntryName(i))
    Next i
    MsgBox "Entry1 = " & tmpString
'
    Get #fileNumber, , tempEntry
    CopyMemory myEntry, tempEntry(0), LenB(myEntry)     ' copy to my entry structure
    tmpString = ""
    For i = 0 To UBound(myEntry.EntryName)
        tmpString = tmpString & Chr$(myEntry.EntryName(i))
    Next i
    MsgBox "Entry2 = " & tmpString
'
    Get #fileNumber, , tempEntry
    CopyMemory myEntry, tempEntry(0), LenB(myEntry)     ' copy to my entry structure
    tmpString = ""
    For i = 0 To UBound(myEntry.EntryName)
        tmpString = tmpString & Chr$(myEntry.EntryName(i))
    Next i
    MsgBox "Entry3 = " & tmpString
'
    Close #fileNumber

No errors reported but, even with a Watch on the "temp..." variables, all that is returned is just binary zeroes.

Answer 3 · 2025-11-19 14:57:00Z

@TimWilliams sorry, nevermind the testing code. I found my error--it was a typo in the file name. I've also confirmed that I can read directly into my Type structures without the need for the temp... variables or pre-initialization. So, I'm getting data now and we are back to the original question...

What is the best way to take a binary date, e.g., out of a byte array, and turn it back into a valid VBA date? Just change the Type block to use Date datatypes? But if non-standard data lengths are used (e.g., 6 bytes, instead of 8), what then?

Answer 4 · 2025-11-20 16:59:43Z

Well, I guess I have my answer (after much research and testing). I'm using this structure, now--plus a custom function to handle null-terminated strings.

Private Type schedulerEntry     ' reclen = 329
    EntryName           As String * 100
    EntryEvent          As String * 10
    EntryBegDate        As LongLong ' 8
    EntryFrequency      As String * 1
    EntryOccurs1        As Integer ' 2
    EntryOccurs2        As Integer ' 2
    EntryRepOpts        As String * 12
    EntryRepOpt2        As String * 12
    EntryRunDate        As LongLong ' 8
    EntryEndDate        As LongLong ' 8
    EntryRetCode        As Long   ' 4
    EntryNxtDate        As LongLong ' 8
    EntryPid            As String * 10
    EntryStartSys       As String * 30
    EntryStartName      As String * 100
    EntryStartPid       As String * 10
    EntryPLength        As Long   ' 4
End Type

The Dates in the structure turned out to be C++ timestamps. I converted those to standard VBA Date format as follows--using a Windows API for the time zone offset.

    If GetTimeZoneInformation(LocalTimeZoneInfo) <> 0 Then
        zoneOffset = LocalTimeZoneInfo.Bias
    Else
        zoneOffset = 0
    End If
    tmpDate = (myEntry.EntryBegDate / SECONDS_PER_DAY) + UNIX_EPOCH_SERIAL
    tmpDate = DateAdd("n", -zoneOffset, tmpDate)

Thanks, all, for reading along.

Collectives™ on Stack Overflow

Best way to extract a VBA Date out of a byte array and turn it back into a valid VBA Date?

4 Replies 4

Your Reply

Collectives™ on Stack Overflow

Best way to extract a VBA Date out of a byte array and turn it back into a valid VBA Date?

4 Replies 4

Your Reply

Sign up or log in

Post as a guest