UDT parsing error. RRS feed

  • Question

  • I am having problems reading a User defined variable from disk.

    Sometimes (not always) Excel gives me a run-time error.

    I decided to investigate further, this is what I did.

    Step 1

    I put "on error resume next" before the offending Get statement and "on error goto 0" after the Get.

    Clearly Excel does not report the run-time error, and the program runs to completion.

    The UDT has 23 elements. The first 19 are read correctly and the last 4 not read at all.

    I believe this implies that Excel has read form disk and transferred the data to a buffer and that the error is in the parsing of the buffer.

    Step 2

    The UDT in question has a length of 223 bytes.

    I created a new UDT with a single element - a fixed length string of 223 characters / bytes.

    I then read this from disk using Get with the same record number and same open file.

    I wrote a procedure to parse this "home made buffer". (I found some of the Hex2xxx functions with the help of the internet)

    The results were correct. Therefore the file on disk is correct.

    Step 3

    To ensure the partial data (first 19 elements) was actually being read I filled the UDT with false information before each Get.

    This table summarises the information read from disk.

    UDT_Desc Before Get - This is false information After Get My Parse
    Record Number 3 3 3
    CadName As String * 10 Empty Ian Ian
    CadDate As Date 01/01/01 24/11/17 24/11/17
    EditName As String * 10 Empty Ian Ian
    EditDate As Date 02/02/02 30/11/17 30/11/17
    Atv As String * 3 xxx Sim Sim
    Empresa As String * 10 Company Simtec Simtec
    OSNo As Integer 12345 2404 2404
    ClNo As Integer 11222 88 88
    Fantasia As String * 30 Any Name Azul - Galeão RJ Azul - Galeão RJ
    Cidade As String * 40 SomeWhere Galeao Galeao
    UF As String * 2 XX Ri Ri
    PedClient As String * 30 Order Number 123456789 123456789
    InsCid As String * 30 SomeWhereElse Galeao Galeao
    InsUF As String * 2 YY Ri Ri
    DtStart As Date 03/03/03 01/12/17 01/12/17
    DtEnd As Date 04/04/04 30/11/18 30/11/18
    QtMod As Integer 9 1 1
    QtAr As Integer 8 1 1
    QtOut As Integer 7 1 1
    LocMods As Single 99                 unchanged> 99    (Not Read) 1000   (Correct)
    LocAr As Single 88                 unchanged> 88    (Not Read) 105     (Correct)
    LocOther As Single 77                 unchanged> 77    (Not Read) 250     (Correct)
    LocVenc As Integer 66                 unchanged> 66    (Not Read) 15       (Correct)

    Its clear that the 19th element (QtOut) is being read, interpretted and put into UDT.QtOut. It is clear that the 20th to 23rd elements are not.

    Either the Get command read a reduced amount of data to it's buffer or the Excel parsing did not work.


    The UDT Type declaration has the 23 element (LocVenc) in twice. On of them has an apostrophe in front - i.e. its a comment.

    If I change the comment to the other entry and Debug / Compile  then the problem is temporarily solved and the Get reads without error.

    I have Tools/options/general/complie on demand turned off. I understand that this forces excel to compile the ENTIRE code before execution begins.

    I also understand that Debug/compile is nothing more than a Sintax check - it does not "save" an executable code.


    Given this : How can the above solution make any difference?

    The Truth

    The run-time error referred to above is Error 59 - Bad Record Length.

    I posted a question about this on the 5th October 2017. See "Spurious Error 59" on Microsoft Community. Please have a look.

    I added two further comments but despite 88 views received no suggestions.

    I am no expert programmer but I do know that this is not a problem that a trip to Microsoft Help will resolve.

    There are two possibilities that I can see.

    1/ I am missing some simple command in my program to alter how Excel uses/controls/alocates memory.

    2/ There is something wrong with the Microsoft complier.

    Looking forward to some ideas, solutions or, who knows, a comment from microsoft.

    Tuesday, December 5, 2017 6:10 PM

All replies

  • Sounds like it's either an error in your parsing routine reading the record from file, or the original record was not correctly written to file. Or maybe you are not using unique separators.

    It's hard to guess what you're doing but never use 'on error resume next' unless you want to ignore anticipated errors which you know can safely be ignored.

    Tuesday, December 5, 2017 7:08 PM
  • Peter, Thanks for the reply,

    My parsing routine works fine, and gives the correct values.

    This proves what is written in the file is as it should be.

    There are no separators in the data when a UDT i written with Put. Just a continuous series of bytes. The "separation" is carried out in accordance with the element sizes in the Type declaration.

    The 'on error resume next' is ONLY there so that I can try and find out what is going wrong. I would never use it this way in normal programming.


    Tuesday, December 5, 2017 9:41 PM
  • Hi lan_Brazil,

    This forum(Excel for Developers) is for development issues related to Excel Object Model and your problem is more related to Visual Basic for Application, so I would move this thread into Visual Basic for Application.

    Thanks for understanding.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Wednesday, December 6, 2017 6:37 AM
  • Terry,

    I apologize for posting in the wrong forum, I am new here.

    Looking at the other posts it appears that Chenchen Li has already moved it - Note at end of first post.

    Do I need to do something else ?


    Ian Simmons

    Wednesday, December 6, 2017 12:52 PM
  • Have a look at this post from megastream on 27 June 2017.

    I'm afraid I don't have an answer - but I can confirm that it is a VBA bug which has haunted me from Office 200 to 2007. I get it with Microsoft Access. As the original respondent said, the VBA code is perfectly OK, and normally runs without a problem. But sometimes, when you re-compile it (due to some other change), up pops the bad record length message when you attempt to execute the fixed length record code. The solution, as described by the original respondent is to force a recompile - to do this, comment out part of the record structure so that a recompile fails with an error message - then remove the comment and recompile (the original code). It now usually works. Sometimes during the recompilation with the commented-out data, the compilation fails with an Internal Error.I don't know if it persists with more modern Office Versions - since my clients have not yet updated from 2007.I suspect the original respondent is the person I corresponded with many years ago, and I'm sure both of us would be delighted if Microsoft were to fix this bug.

    Tuesday, June 27, 2017 6:53 PM

    Wednesday, December 6, 2017 1:21 PM
  • To further detail my problem.

    I am developing a system for a (my) small business. I have about 20 data files (Clients / suppliers / stores items / fixed assets / rentals / employees ...etc.) Each record of these files is defined using a Type statement and written or read using a Put or Get statement.
    Each data file is maintained or incremented with a separate Workbook.
    I also have separate workbooks to control the day to day processes of the company. (Sales / Rentals / store movement etc.) These 'operational' workbooks rely heavily on the records from the data files. They also produce further data files for daily movements.
    The system is controlled by one Workbook called Menu.xlsm which allows the user to select the desired workbook. Menu.xlsm contains all the type statements, general procedures, functions and Forms. It is referenced in all other workbooks and is always open. The user is restricted to two open workbooks - Menu and one other.
    The system is on a network server and written in such a way that the user can only open the workbooks 'read-only'. The user NEVER save a workbook, they always save the data to a data file.
    Basically I have a database system and am using Excel as the interface.

    My Type statement is

    Public Type CLocDesc
       Atv As String * 3
       CadName As String * 10
       CadDate As Date
       EditName As String * 10
       EditDate As Date
       Empresa As String * 10
       OSNo As Integer
       ClNo As Integer
       Fantasia As String * 30
       Cidade As String * 40
       UF As String * 2
       PedClient As String * 30
       InsCid As String * 30
       InsUF As String * 2
       DtStart As Date
       DtEnd As Date
       QtMod As Integer
       QtAr As Integer
       QtOut As Integer
       LocMods As Single
       LocAr As Single
       LocOther As Single
       LocVenc As Integer
    End Type
    Public CLoc As CLocDesc  ' This appears at the top of the module.

    I know with absolute certanty that Len(CLoc) = 223
    This particular file controls the company's rental contracts. We rent to our clients. I am english but have made Brazil my home. Thus some of the element names are Portuguese.
    Whenever a user opens the Rental Workbook this file (Rental.rnd) is loaded automatically by a standard module procedure(LoadData()) called by workbook_open().
    This is the LoadData procedure. Some non relevant code is omitted.(Conditional load / % load indication / table sizing)

    '                                                      LOAD  DATA  .
    Sub LoadData()
    Open Range("MDP") + "Rental.rnd" For Random As #1 Len = Len(Cloc)
    Nitems = LOF(1) / Len(Cloc)       ' Number of records
    J = 0                             ' Line counter for data table
    With Range("DataTable")
       For I = 1 To Nitems
       '                      On Error Resume Next
       Get #1, I, Cloc               ' This command  : Error 59 - Bad record length.
       '                      On Error GoTo 0
       J = J + 1
       .Cells(J, 1) = I
       .Cells(J, 2) = Trim(Cloc.CadName)
       .Cells(J, 3) = Cloc.CadDate
       .Cells(J, 4) = Trim(Cloc.EditName)
       .Cells(J, 5) = Cloc.EditDate
       .Cells(J, 6) = Trim(Cloc.Atv)
       .Cells(J, 7) = Trim(Cloc.Empresa)
       .Cells(J, 8) = Cloc.OSNo
       .Cells(J, 9) = Cloc.ClNo
       .Cells(J, 10) = Trim(Cloc.Fantasia)
       .Cells(J, 11) = Trim(Cloc.Cidade)
       .Cells(J, 12) = Trim(Cloc.uf)
       .Cells(J, 13) = Trim(Cloc.PedClient)
       .Cells(J, 14) = Trim(Cloc.InsCid)
       .Cells(J, 15) = Trim(Cloc.InsUF)
       .Cells(J, 16) = Cloc.DtStart
       .Cells(J, 17) = Cloc.DtEnd
       .Cells(J, 18) = Cloc.QtMod
       .Cells(J, 19) = Cloc.QtAr
       .Cells(J, 20) = Cloc.QtOut
       .Cells(J, 21) = Cloc.LocMods         ' Bad read starts here
       .Cells(J, 22) = Cloc.LocAr
       .Cells(J, 23) = Cloc.LocOther
       .Cells(J, 24) = Cloc.LocOther + Cloc.LocAr + Cloc.LocMods
       .Cells(J, 25) = Cloc.LocVenc
       Next I
    End With
    End Sub

    When the error does not occur the data loads correctly.
    When the error does occur I uncomment the On error commands and rerun the program. The program finishes normally and the data in the table indicates that the data has been correctly read upto Cloc.QtOut and susequent elements not read.
    It would appear that the 'Error 59 Bad record length' is a result of the 'VBA parsing code' being unable to interpret the data in bytes 210 to 213 of the CLoc buffer data read by the Get statement. 
    To verify this I added this code :

    Type AllClocDesc
       StAll As String * 223
    End Type
    Dim AllCloc As AllClocDesc
    ...and ...
    Get #1, I, AllCloc

    Thus I have a 223 byte string (AllCloc.StAll) identical to the buffer read by the offending Get #1, I, Cloc. I then wrote a procedure to parse this string and verify the data on disk. I can post the code if you wish). The data on disk is CORRECT.

    If I close and reopen the workbook the error persists.

    As I said above the type statement and public decalarion of CLoc is in Menu.xlsm. The LoadData code and hence the error producing code is in a workbook called Rentals.xlsm.
    So, I close Rentals.xlsm. In Menu.xlsm i cut 'Public CLoc As CLocDesc' and paste it in a slightly different place. Then debug/compile and Save, but do not close, Menu.xlsm.
    As if by magic LoadData() completes normally, with the correct data.

    The saved copy of Menu.xlsm should be identical to that which just ran correctly.
    Close Rental.xlsm, Close Menu.xlsm. Reopen Menu.xlsm, Reopen Rental.xlsm.
    FAIL !!  Error 59 Bad record length.

    I stated above that the users open the workbooks "read only", thus two users can open the workbook (almost) simultaneously. It is common for one user to receive error 59 and the other not. The same workbook and the same data!

    I have around 30 random access files in all. About 10 of them have in the past or are currently giving identical problems. I have 22 workbooks totaling 4.04 MB. I have stopped adding more simply because the users are no longer able to use the system.

    I have thought about using class modules for the data. But 30 class modules instead of 30 type statements. Talk about a sledgehammer to crack a nut.
    When I first started I used print/write and delimiters. I gave up very quickly when the users started including comas, semicolons and quotation marks in their texts. 
    I believe that Microsoft deliberately created the UDT/Get/Put for the purpose for which I am using it.

    There is something very very very strange happening here.

    I truly apreciate your help and the time you have taken,

    Ian Simmons

    Thursday, December 7, 2017 6:09 PM
  • Looks like you been trying to resolve this issue for a number of years. I don't know whether this is a bug in VBA, or whether there's something going on under the covers that isn't readily apparent. Have you tried running the code in another VBA platform such as Microsoft Access or Word? If you had access to an old copy of Visual Basic (version 6 or earlier), that would enable you to compile the code into an executable COM DLL that could be called from Excel.

    But I think what I would have done by now, given the different types of data you are working with, is converted all of this data into an embedded DBMS (like Access, SQLite or Firebird) that is much easier to work with. As a matter of fact, since you are working with Excel, you could store the data in Excel Worksheets.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, December 7, 2017 9:45 PM
  • Paul,

    Thanks for your comments.

    I have been thinking for some time about different options but there is a very large amount of code to alter if I change now. I am stubborn!  In service to other Excel users I believe this problem needs a solution.

    Another very interesting development:

    As stated in an earlier post, the code which gives rise to the problem is in a loop in Rental.xlsm. (which references Menu.xlsm) and loads the full set of records. Sometimes I need to access just one record, so I have a procedure in Menu.xlsm designed to do just that: 

    Sub ZPGRental(N As Integer, PutGet As String, CLoc As CLocDesc)
    Dim Fnum As Integer: Fnum = FreeFile
    Open Range("DataPath") + "Locacao\Rental.rnd" For Random As Fnum Len = Len(CLoc)
       If PutGet = "Put" Then Put Fnum, N, CLoc: Close Fnum: Exit Sub
       If PutGet = "Get" Then Get Fnum, N, CLoc: Close Fnum: Exit Sub
    End If
    Close Fnum
    End Sub
    Immediately after the "Get #1, I, Cloc" (which gives error 59) I inserted Call  ZPGRental (I, "Get", Cloc)  which runs perfectly.

    Now I am completely confounded!


    Sunday, December 10, 2017 12:18 AM
  • OK I misunderstood, parsing normally means dealing with strings and in this context splitting the record with separators.

    In a quick test with Put and Get with your Type construct it Get seemed to worked fine, though perhaps I missed something in what you explained(?)

    Sub PutRec()
    Dim i As Long, tp As CLocDesc, ff As Long
        With tp
            .Atv = "atv"
            .CadName = "CadName"
            .CadDate = Date - 10
            .Cidade = "Cidade"
            .ClNo = 11111
            .DtEnd = Date - 9
            .DtStart = Date - 6
            .EditName = "EditName"
            .EditDate = Date
            .Empresa = "Empresa"
            .Fantasia = "Fantasia"
            .InsCid = "InsCid"
            .InsUF = "IU"
            .LocAr = 123.456
            .LocMods = 456.321
            .LocOther = 222.4444
            .LocVenc = 4444.222
            .OSNo = 12345
            .PedClient = "PedClient"
            .QtAr = 555
            .QtMod = 666
            .QtOut = 777
            .UF = "UF"
        End With
        ff = FreeFile
        Open "TESTFILE.txt" For Random As #ff Len = Len(tp)
        For i = 1 To 10
            tp.QtMod = i
            Put #ff, i, tp
        Close #ff
    End Sub
    Sub GetRec()
    Dim k As Long, tp As CLocDesc, ff As Long
        ff = FreeFile
        Open "TESTFILE.txt" For Random As #ff Len = Len(tp)
        Get #1, 5, tp
        Close #ff
    Debug.Print tp.QtMod
       ' Alt-v, s and inpspect tp in locals
    End Sub

    Sunday, December 10, 2017 6:42 PM
  • Do you have a sample file or some sample data that will reproduce this issue?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, December 11, 2017 4:14 PM
  • Peter,

    You have identified the problem. I can find no reason to explain why it works sometimes and other times does not. Sometimes I save the program "in working order"; when I reopen it, it fails. Surely this cannot be a question of code.

    Yesterday somebody suggested a Race Condition. I used msconfig to reduce the processors used to one (of 4). Same problems, just slower.

    I see the (internal to Excel) sequence of events linked to the Get statement like this:

    Step 1 The 223 bytes of data are read from the file and put in a buffer.

    Step 2 The bytes are separated and evaluated in accordance with the UDT type description.

    Step 3 The parsed results are put in each element of the UDT.

    Sometime during step 2/3 there is an error, probably deep inside windows / excel. This error is reported back to the VBE which has no idea of the real cause of the error. Hence it reports error 59 - possibly a catch all for unknown errors in a Get statement.


    Tuesday, December 12, 2017 7:06 PM
  • I don't follow what you're doing. In Step-2 what do you mean "the bytes are separated", is that your code? In Step3 what do you mean by "parsed elements are put into each element of the UDT".

    Compare with my example, no "separation of the bytes" or "parsing" is done with my code or behind the scenes.

    Tuesday, December 12, 2017 8:10 PM
  • This is an update to the posts above.

    As my company has a subscription for Office 365 I decided to invoke the help of M microsoft.

    The first problem was to find the registered user – who had permission to open a support ticket. It turned out to be the retailer who sold us the subscription.(Not my IT guy??). The promised 4 hour return ended up taking 3 days. Finally we had a conference call – myself / a microsoft engineer / analyst and somebody from the retailer. Both tried to explain to me that since the problem was with my code they (microsoft) were unable to help.

    Ticket : SUP86188 - LATAM-BR-MSFT-O365-Solicitação Eng microsoft

    To open the ticket, I had to submit details of the problem to the retailer and I included a list of the posts that I had made. The conference call failed several times, finally the microsoft engineer/analyst called me directly and admitted that after consulting the posts, he too was convinced that this was a BUG and suggested that I report it to microsoft.  I asked why HE could not report it and he replied that he was NOT ALLOWED to.

    I wish I had recorded that conversation!

    Later I received an email from the retailer stating that the ticket was RESOLVED and closed.

    This is disgusting behaviour from a multinational.

    I have deliberately omitted names from this post - the ticket number is sufficient should anyone from microsoft be interested.

    Any suggestions?

    Wednesday, January 3, 2018 5:32 PM
  • How did the support engineer conclude there is a bug? From the information you have given in this thread I have not been able to reproduce any bug. There might be other reasons why it might apparently fail, but without more information that's only a guess.

    On balance I suspect more likely there isn't a bug involved, though if there really is I'd be interested to know about it!

    Wednesday, January 3, 2018 10:32 PM
  • Peter,

    Thanks for your reply and for your interest.

    The key point in all the posts is that sometimes the program runs normally and sometimes it gives an error. Last night I left the program running normally. When I rebooted today I received an error. The program and data is absolutely identical. Upset that it had failed I checked my emails, looked at the BBC website and then went back to my program. It ran normally. I am not an IT specialist - I am however certain that a given program and data set should produce consistent results. It would appear that the random error depends on which  part of memory Excel is allocated. 

    My Cloc UDT has 223 bytes. I have defined a new UDT as AllCloc which contains one member - a string of 223 bytes.

    When "Get #1,I,Cloc" fails the error is trapped and I call a subroutine which reads the same record with "Get #1,I,AllCloc", interprets this 223 byte string and fills the elements of Cloc. On return from the error subroutine the main program lists the values of Cloc to a worksheet table. (Yes - I have written functions to covert endian Hex to binary and then to decimal)

    I am therefore CERTAIN that the data on the HD is correct, the file length is 223 bytes and the UDT Cloc is correctly defined.

    I believe that the operation above is roughly what the "Get" normally does. Reads a series of bytes to a buffer, parses the values according to the UDT type statement and attributes the values to each element of the UDT.

    The error (incorrectly reported as 59 - bad record length) must be in the parsing carried out by Excel/VBA.

    How did the support engineer conclude there is a bug? There was about an hour between our first and last conversation. He told me that he had consulted the posts and further researched the matter on the internet. He said that it mightbe a bug, although by the tone of the conversation I believe he was almost convinced. It is interesting that no mention of the bug was made during the conference calls (which included the retailer); it was only mentioned when he rang me directly.

    Bug or program error?  If it is a program error then it is not a simple error solvable by consulting Microsoft Help! I have about 30 different UDT's and they work. I have had genuine cases of error 59 and solved then all - usually a simple miscalculation.

    It could be that I am missing some simple command / statement that alters the way excel handles UDT's / Memory / Compilation etc. If this is the case then after two or threehundred hours googling  "Error-59 " I should have found some mention of it. I would also have expected an enlightened forum member to have pointed it out to me.


    Thursday, January 4, 2018 7:01 PM
  • It would be helpful if you could upload a test file that replicates the "bug", or at the very least post your *full* write and read code including sample data for the UDT that you know potentially might fail, I think you've been asked a few times!

    Other things - clarify if your text includes any non ASCCII text or even extended ASCII in the range 128-255. Also, does it fail with write/read all on the same system, or only when reading on a different system, and if so is the local code page the same in each. If yes to any of that have you tried writing to file as Binary (though more even that that might be required to cater for different code pages).

    Thursday, January 4, 2018 7:55 PM
  • You should be receiving a link to the complete set of programs & data via gmail.

    When I saved it, it was in "error mode!"

    Start the program by opening Menu.xlsm, Then select "Locacoes" which will open the Worksheet for the Rental Contracts.

    The text elements of the UDT are all printable characters. The Hex representation of Integer / Single / Date will contain ASCII in the range 128 - 255.           <<<<<<<<<<<< How do I edit the format of this line ? >>>>>>>>>>>>>>>>>>>

    It never fails on Write (Put), only on read (Get).

    All systems are Windows10 Pro / Office 365. The error occurs on randomly on all systems.

    The workaround I have sent you reads a 223 byte string and converts the ASCII characters to Hex as required. 

    Yes, I tried reading the 223 bytes to an byte array(223). It works fine - but I still have to convert the Int/Single/Date values from Hex to Decimal....and the bytes to ASCII in the case of text.


    • Edited by Ian_Brazil Friday, January 5, 2018 12:16 PM
    Friday, January 5, 2018 3:15 AM
  • I got your link. When I said please give the full code I meant show routines to populate the UDT, write to file, read the file and compare with the original. Perhaps 4-6 routines in total and maybe some sample data you know has not been written correctly previously.  However 5,500 files in 32 folders some 95mb really is not what I had in mind!

    I had a go but after spending a looong time trying to follow your instructions and code I gave up, with an error that didn't appear to have anything to do with the issue.

    "The text elements of the UDT are all printable characters. "

    It wouldn't matter if they weren't, what I asked is are any characters in Strings ever non ASCII (or perhaps extended ASCII), for example do your strings ever contain Portuguese characters. I also asked are files always written and read in the same system?

    "I tried reading the 223 bytes to an byte array(223)"

    If 223 is Len(UDT)  I'm surprised it worked, need to size as LebB(UDT). Also note array(223) by default is 224 bytes (unless you use Option base 1).

    "but I still have to convert the Int/Single/Date values from Hex to Decimal....and the bytes to ASCII in the case of text."

    No need for any of that. When writing to file use the CopyMemory API to copy the UDT to the byte-array and when reading copy the byte-array to the UDT.

    I notice in your open file routines you use #1 for file-number. A long shot but that might be what's leading to your errors, always get a file-number with FreeFile (which will typically be #1 if no other files are open or not correctly Close'd)

    I'll email off-line with a couple more things later

    Friday, January 5, 2018 12:55 PM
  • Peter,

    I am sorry if the upload was too big, please understand that there is a lot of integration between the modules and it would be almost impossible to separate out a subset of modules / data that would allow the program to operate normally. Please note that there only 22 workbook files - the rest is all data. Only two of these are really important : Menu.xlsm and Locacoes.xlsm.

    I really would like you try and reproduce the error. If you can you will appreciate the problem.

    I tried to "unblock" the system so that you could run it  - please let me know what error you are getting.

    I was using FreeFile / Fnum - I changed to #1 to be eliminate a possible problem. Look at module H_PutGet in Menu.xlsm.

    I actually used byte array(222)  - 0 to 222 = 223 bytes

    Some characters in the text are accentuated - "ó" = 243 for example. Once again, when the program works the accentuated characters work fine. I understand that extended ASCII are two byte characters developed principally for non roman scripts. There are none in my data. The data file (SPMK2016/Data/Locacao/Rental.rnd) has 892 bytes. I "Get" four 223 byte strings which I then parse to the correct data.  4 x 223 = 892. There are no extended characters.

    I tried using the CopyMemory API - without much success. (Maybe a little beyond my comfort zone)

    I look forward to your off-line comments.


    • Edited by Ian_Brazil Friday, January 5, 2018 1:47 PM
    Friday, January 5, 2018 1:45 PM
  • I look forward to your off-line comments.

    I followed up off-line a short while later but I didn't hear back from you, did you receive it?
    Monday, January 8, 2018 3:35 PM
  • Peter, 

    Yes, I received your information, thank you. I am studying it carefully and trying to construct a reduced set of files which reproduce the error. Unfortunately when I reduce the size / quantity of files the error disappears.

    I still cannot make CopyMemry work. The resulting Cloc contains mostly garbage. 

    One approach that does return the correct results is to define a new Type (Cloc2Desc), Dimension Cloc2 as Cloc2Desc, Get fnum,recno, Cloc2 and then Lset Cloc = Cloc2.

    This seems to confirm that the data on the disc is correct and the that the UDT definition of CLoc is correct This only leaves Excel's internal parsing as a source of error.

    I took the weekend off, which is my I did not reply earlier. Give me a couple of days and I will send more detailed information.


    Tuesday, January 9, 2018 5:21 PM
  • "Unfortunately when I reduce the size / quantity of files the error disappears."

    That might suggest the problem is something else perhaps hard to see in your large complex arrangement of things(?)

    "I still cannot make CopyMemry work"

    Send me simply an example of your UDT populated with typical values, ideally with at least some characters you might expect to fail.

    "This only leaves Excel's internal parsing as a source of error."

    Excel, or rather VBA, doesn't do any internal parsing. The 'record' from file is read as a continuous serious of bytes into memory, then offsets calculated from first byte into each element of the UDT according to the defined lengths of the UDT elements. 

    However there are other issues with strings which can throw things as I mentioned, which is why might be an idea to save as binary even if only for testing.

    Tuesday, January 9, 2018 7:44 PM
  • Peter,

    I have sent you some more files which should enable you to understand the problem. 


    Wednesday, January 10, 2018 9:54 PM