PDA

View Full Version : Reading and Writing to dat file via forms



gint32
08-15-2017, 11:39 PM
Hi All/Gurus.
I have inherited an fairly complex excel (2010)system. The spreadsheet in Excel basically via VBA writes and reads data to various “.Dat” files in various sub folders via Texboxs and Userforms (only).
My issues is , I am not accustomed to writing vba in this way and I have stepped through the vba behind and find it really too complex for me presently (I am actually doubting that it was developed with just the standard MS VB for applications that comes with an office install). I thought I have a searched the Internet but I can’t find any examples of reading and writing via a variety of textboxes and Buttons on Userform’s and Buttons although I did find other examples but just not using userforms.
So I hoping someone out there has some experience in this area and able to cast some light or better yet provide me with a simple example of replicating this so I can expand on and begin the new learning curve.
Here is a sample of the VBA that runs from a form button somehow


Open C:\folder\file.dat" For Random Shared As 1 Len = 100 ' Also, whats the random and Len doing?

For Num_OF_Records = 1 To LOF(1) \ 364
Get #1, Num_OF_Records, Ent
EDate = Val(Format(EDate.A_Date, "yymmmdd"))
If ShowAll_Flag = 0 And EDate >= CurrentDate Then
GoSub Add_Row
Else
If ShowAll_Flag = 1 Then GoSub Add_Row
End If
Next
Close #1

Add_Row:
If Trim(Ent.)TmpID) = Range("EmpID") And Trim(Ent.mrk) <> "DELETED" Then
Row_Num = Row_Num + 1
With Ent
Range("'OReg'!A" + CStr(Row_Num)) = Trim(.A_Date)
Range("'OReg'!B" + CStr(Row_Num)) = Trim(.P_Prefer)

End With
End If

Return

SamT
08-16-2017, 06:53 AM
Are you sure that was copied correctly, because Trim(Ent.)TmpID) is a syntax error.

I am assuming that Ent is the name of a UserForm, or one of its Container Controls, and TmpID is the name of a TxtBox in that Form or Container.


writes and reads data to various “.Dat” files in various sub folders via Texboxs and Userforms
VBA code to R/W text files doesn't care where the data comes from. For example, this bit of your code, which is really writing to a Worksheet, not a *.Dat file,

With Ent
Range("'OReg'!A" + CStr(Row_Num)) = Trim(.A_Date)
Range("'OReg'!B" + CStr(Row_Num)) = Trim(.P_Prefer)

End With would work the same if you replaced ".A_Date" with "Jul 4th, 1776", and replaced ".P_Prefer" with "HotDogs."

When you see a reference to a Control on a user Form in the code, you can usually think of that Control as just another variable. Usually, the value of a control is its default Property and the Property Named "Value" does not have to be used in code. These two line are equivalent

X = TextBox1.Value
X = TextBox1

snb
08-16-2017, 08:29 AM
It would be nice:

- if you uploaded a .bat file
- if you told us what the code is supposed to do

- it would be terrific if you copy/pasted code, since the text you posted can't be running at all.
Open C:\folder\file.dat" is lacking a ": Open "C:\folder\file.dat"

Leith Ross
08-16-2017, 02:02 PM
Hello gint32,

Perhaps this annotated version will help you. This does not address your issue of placing this on a UserForm. As snb mentioned, we would need to see both the workbook and full code to provide you with a solution.



Sub TestMacro()


' Random access file records are User Defined Type structures with a fixed number of bytes.
' The record length must be included in the Open statement else VBA will not know how many bytes to read or write.
' If the file specified by pathname doesn't exist, it is created when a file is opened in Append, Binary, Output, or Random modes.

Open "C:\folder\file.dat" For Random Shared As #1 Len = Len(Ent)

' Divide the number of bytes in the file by 364, using Integer math, to determine the number of records.
For Num_OF_Records = 1 To LOF(1) \ 364

' Read a record from the dat file. 'Put' is used to write a record using the User Defined Type (UDT) data, 'Get' is used to read a record and copy the data into the UDT.
Get #1, Num_OF_Records, Ent

' Format the date from the dat file.
EDate = Val(Format(EDate.A_Date, "yymmmdd"))

' Determine if a row is to be added.
If (ShowAll_Flag = 1) Or (ShowAll_Flag = 0 And EDate >= CurrentDate) Then

If Trim(Ent.TmpID) = Range("EmpID") And Trim(Ent.mrk) <> "DELETED" Then
' Increment the row number
Row_Num = Row_Num + 1

' Copy the dat file values to the worksheet tanges.
With Ent
Range("'OReg'!A" & Row_Num) = Trim(.A_Date)
Range("'OReg'!B" & Row_Num) = Trim(.P_Prefer)
End With
End If
End If

Next Num_OF_Records

Close #1


End Sub

gint32
08-16-2017, 03:23 PM
Thanks for that piece of info, it's much appreciated, any idea or examples of what the following piece of vba does?


Open Folder_L + "Register.lst" For Random Shared As 1 Len = 100

Specifically "Random" and "len = 100" as I don't know how they are being used within this line of code.

gint32
08-16-2017, 03:26 PM
Apologies I Just read your comments, before I posted below, 6:30 am here and leaving for work :(

Leith Ross
08-16-2017, 03:39 PM
Hello gint32,

No problem. I don't function very well at that time of day either.