[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1018
  • Last Modified:

Excel needs to open a fixed length format file

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
happylife1234
Asked:
happylife1234
  • 2
  • 2
  • 2
1 Solution
 
inthedarkCommented:
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
 
aikimarkCommented:
You should be able to open the file with a fixed field format.  Can you open the file correctly?
0
 
inthedarkCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aikimarkCommented:
I think the inthedark comment http:#34914521 is complete and answers the question
0
 
mlmccCommented:
Thanks Mark.  I thought so also but want a second opinion and was hoping it would be the asker.

mlmcc
0
 
mlmccCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now