Solved

Excel needs to open a fixed length format file

Posted on 2011-02-16
8
1,007 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
8 Comments
 
LVL 17

Accepted Solution

by:
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    


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 45

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 45

Expert Comment

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

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 100

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

758 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

17 Experts available now in Live!

Get 1:1 Help Now