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 saves 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
Public CLoc As CLocDesc ' This appears at the top of the module.
I know with absolute certainty 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 proceedure. Some non relevant code is omitted.(Condicional load / % load indication / table sizing)
' LOAD DATA .
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
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
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 up to Cloc. QtOut and subsequent 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 :
StAll As String * 223
Dim AllCloc As AllClocDesc
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 proccedure 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.
How can I solve my problem ?
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.