Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel needs to open a fixed length format file

Posted on 2011-02-16
8
Medium Priority
?
1,022 Views
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.

Thanks
0
Comment
Question by:happylife1234
  • 2
  • 2
  • 2
6 Comments
 
LVL 17

Accepted Solution

by:
inthedark earned 2000 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    


Next

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:

Do

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

    Sleep 1000 ' Wait for a moment
    DoEvents
Loop


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:~)






0
 
LVL 47

Expert Comment

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

Expert Comment

by:inthedark
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)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 47

Expert Comment

by:aikimark
ID: 35178517
I think the inthedark comment http:#34914521 is complete and answers the question
0
 
LVL 101

Expert Comment

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

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

606 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