Excel needs to open a fixed length format file

Posted on 2011-02-16
Last Modified: 2012-05-11
I have a fixed length format file that needs to be modified. I would like to open the file using excel and then edit it and then save it back in the same format.

Can anyone tell me what is the easiest way to do this.

Question by:happylife1234
  • 2
  • 2
  • 2
LVL 17

Accepted Solution

inthedark earned 500 total points
ID: 34914521
The easiest way depends on: the format of the data; how fast you want it to work; if other users are editing the file at the same time and how big the file is.

You have a number of choices:

Method 1 & 2 Use Random Access fixed length records which can be used for very large files or method 3 for use with variable length records or when you want to process the data very quickly but method 3 will only work for files where you can read the whole file into memory.

Method 1 - Using Fixed Length Records Using the Random Open file option

Using this method there are 2 types of records that you can either a record buffer based on a pre-defined record created using a UDT (user defined type) or using a string buffer. See method 2 for the string buffer.

Option Explicit
Private Type MyRecordDef
    MyField1 As String * 10
    MyField2 As String * 4
    MyField3 As Currency
    MyEndOfRecord As String * 2
End Type

Sub Macro1()

' Create a Record based on the record definition

Dim MyRecord As MyRecordDef

' create a file handle
Dim lfn As Long
lfn = FreeFile

' Get the record length of the data
Dim lRecordLength as Long
lRecordLength = Len(MyRecord)

' open the file
Open "C:\MyFolder\MyFile" For Random Access Read Write Shared As #lfn Len = lRecordLength

' Calculate how many records are in the file
Dim lRecordCount As Long
lRecordCount = LOF(lfn) / lRecordLength ' LOF returns the current length of the file

' Read All the records in the file

Dim lRecord As Long

For lRecord = 1 To lRecordCount
    Get lfn, lRecord, MyRecord

    ' Process The Record In the Way You Want

     Debug.Print MyRecord.MyField1
     Debug.Print MyRecord.MyField2

     ' If you are holding numeric values in string fields which is sometimes easier
     ' you need to convert to numeric before doing maths

     MyRecord.MyField2 = Cstr(Val(MyRecord.MyField2)+1)

     ' Now Save the record back into the file

     Put lfn, lRecord, MyRecord    


If multiple users are accessing the file you can optionally use record locking before you change a record.

Before you read:

On Error Resume Next
Lock Lfn, lRecord
If Err.Number<> 0 Then
    MsgBox "The record is locked"
End If

' After you write

UnLock lfn, lRecord

But in practice you need to build locks into a function so in you code you can ask if a record is locked before you go there:


    If Not IsLocked(lfn, lRecord) Then
        Exit Do
    End If

    Sleep 1000 ' Wait for a moment

Method 2 - Using Random Access File Using A string Buffer

Open The file in the same way as method 1, the difference is how you read the data

Dim sBuffer As String

sBuffer = Space(lRecordLength)

Get lfn, lRecord, sBuffer

Debug.Print Mid(sBuffer, 23, 10) ' Get at the individual fields using Mid using the position and length of the field)

' Save the data back to the file

Put lfn, lRecord, sBuffer

Method 3 Using Binary Access Open

Using Binary access you can read or write any portion of the file just one byte or the whole file in one hir which will save a lot of time and will work the fastest.

lfn = FreeFile
Open MyFile For Binary Access Read Write Shared As #lfn

' Get the file into memory like the fixed length but you have to set the length of the buffer you want to read

sBuffer =  Space(lRecordLength)

Get lfn, lPos, sBuffer ' this will read from byte position lpos for the number of bytes defined by the current length of sBuffer

You save the data in the same way:

Put lfn, lPos, sBuffer

You can get at individual records by using a formula to calculate the start byte position of each record:
(For example you want record lRecord).

lStartPos = ((lRecord -1) * lRecordLength) +1

sRecord = Mid(sBuffer, lStartPos, lRecordLength)

You can also use the fixed length records in the form of an array:

Dim AllRecords(lRecordCount) As MyRecordDef

Get lfn, 1, AllRecords

All your records are now in  one simple array

Debug.Print AllRecords(1234).MyField1

You can do the changes you need and then slap them back onto the disk

Put lfn, 1, AllRecords

Using this method because there are only 2 disk operations the speed will be awesome and you will earn a big bonus.

Hope this helps:~)

LVL 45

Expert Comment

ID: 34916977
You should be able to open the file with a fixed field format.  Can you open the file correctly?
LVL 17

Expert Comment

ID: 34918049
Revision to method 3:

The following should be corrected:

Dim AllRecords(lRecordCount) As MyRecordDef

As follows:

Dim AllRecords() As MyRecordDef

Redim AllRecords(lRecordCount-1)
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

LVL 45

Expert Comment

ID: 35178517
I think the inthedark comment http:#34914521 is complete and answers the question
LVL 100

Expert Comment

ID: 35181069
Thanks Mark.  I thought so also but want a second opinion and was hoping it would be the asker.

LVL 100

Expert Comment

ID: 35275414
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question