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)
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LINQ return type for nested group query 6 71
Reading the Contents of a Directory In Access VBA 5 58
How to measure sizes and angles in scanned images ? 3 53
vbModal 12 35
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now