User-defined types 1

[tdfreeman]

Okay, I am fairly good at the basics of VBA and Access but I learned it on my own. I never took a VB class or any other OO class. Therefore, sometimes the terminology stumps me.

I am trying to create a user-defined type:

Public Type Input_Header
RecType As String * 3
HeaderDate As String * 8
FileName As String * 44
End Type


I want to assign a variant variable to that type, i.e:

Dim strHeaderString As Input_Header

strHeaderString = varLine

When I try to run this, I get the error "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions".

I have no idea was "public object modules" or "late-bound functions" means. Like I said, I have no OO training. So if anyone can explain this in simple terms I would appreciate it.

Just to give you an idea of what I am doing, here is my code (obviously there will be more. I just need to get this working before I move forward):

Public Sub ImportExtract()
On Error GoTo Err_ImportExtract

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Dim strLine As String
Dim varLine As Variant



strInputFile = "C:\My_Data\Extracts\extractBilling_121203.txt"
Set fs = CreateObject("Scripting.FileSystemObject&quot
Set f = fs.GetFile(strInputFile)
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)

Do While ts.AtEndOfStream <> True
varLine = ts.ReadLine
Select Case Left(varLine, 3)
Case &quot;000&quot;
Call imp_get_header(varLine)
..... (more case statements here)
End Select
Loop

Exit_ImportExtract:
Exit Sub

Err_ImportExtract:
Resume Exit_ImportExtract

End Sub
-----------------------------------------------------------
Public Sub imp_get_header(varLine As Variant)
On Error GoTo Err_imp_get_header


Dim strHeaderString As Input_Header

strHeaderString = varLine
MsgBox &quot;Record Type = &quot; & strHeaderString.RecType
MsgBox &quot;Header Date = &quot; & strHeaderString.HeaderDate
MsgBox &quot;File Name = &quot; & strHeaderString.FileName


Exit_imp_get_header:
Exit Sub

Err_imp_get_header:
Resume Exit_imp_get_header

End Sub



Thank you for your help.

Tammy

 

[vbajock]

Are you using a code module or is all this code going in the form code?

 

[tdfreeman]

Not sure I understand your question, but it is a code module. I will probably have a form that executes the code but the purpose of the code is to import various record types into different tables while doing error checking.


Thank you for your help.

Tammy

 

[vbajock]

This article looks like it applies to your problem.

http://support.microsoft.com/defaul...port/kb/articles/Q184/8/98.asp&NoWebContent=1

It is essentially saying that you are going to get errors on UDTs if you use late binding. An example of that would be
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot

It looks like the only fix is to use early binding, where you set references to the objects and then declare them to be of that object type. Are you familiar with how to do that?

 

[vbajock]

Here's some sample code on how to set up FSO for early binding:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20136928.html

 

[tdfreeman]

I read the links, but I am still a little confused. I have the following that I got from somewhere else but I didn't really understand how it was different from what I was doing. I guess this is early binding. After declaring the file, how do I open and use it?

Dim fso As New Scripting.FileSystemObject
Dim fldr As Scripting.folder
Dim file As Scripting.file
Dim ts As Scripting.TextStream


What is the difference between?
Set fso = CreateObject(&quot;Scripting.FileSystem&quot
and
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot



Thank you for your help.

Tammy

 

[tdfreeman]

Maybe it will help if I describe what I am trying to do this way.

I want to create record descriptions as types. I would then read a line of a file in, evaluate the beginning of the line to determine which record type it is and then assign it to the appropriate type.

For example, you know how in COBOL you can do the following (not sure I remember proper syntax so ignore syntax):

01 USER-TYPE
05 - RECTYPE PIC(XXX)
05 - HEADERDATE PICX(8)
05 - FILENAME PICX(44).

You can then assign the whole USER-TYPE variable in one easy swoop. Then you can access each field individually. I would like to be able to do something similar in VBA.

Thank you for your help.

Tammy

 

[vbajock]

Open Tools/References. Find Microsoft Scripting Runtime. Click it. (looks like you already have done this). This sets a reference to the FSO object &quot;early&quot;.

Once early binding is set, then this is the correct way to declare the objects early:

Dim fso As New Scripting.FileSystemObject
Dim fldr As Scripting.folder
Dim file As Scripting.file
Dim ts As Scripting.TextStream

&quot;CreateObject&quot; is late binding. You would do this if you did not set the reference as outlined above. Late binding is what you should avoid.

If you have this:
Dim fso As New Scripting.FileSystemObject

You don't need this:
Set fso = CreateObject(&quot;Scripting.FileSystem&quot

This is just redundent:
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
Your creating two separate copies of the FSO object. Do you need two copies for some reason? If you do, then use
Dim fs As New Scripting.FileSystemObject 'early bind
instead of.
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot 'late bind

 

[tdfreeman]

vbajock. Thanks for all your help and ideas. Unfortunately, this has not solved the problem. I am still unable to assign the variant to the new datatype. I still get the same error.

I have tried to declare the type at the top of the module, in a module where all my global variables are defined and in the procedure itself. In the procedure I get an error that you can't define a type there. In the other two places I get the error I first defined.

The following is my type declaration:

Public Type Input_Header
RecType As String * 3
HeaderDate As String * 8
FileName As String * 44
End Type

If anyone has any ideas I would greatly appreciate it.

Thank you for your help.

Tammy

 

[vbajock]

Are you putting the type declaration at the top of the module or within the procedure?

 

[tdfreeman]

Tried both.

In the procedure I get an error that I can't do it.

At the top of the module, I get the following error:

&quot;Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions&quot;.

It is 11:30 here and I am going to lunch. Will check this post when I get back.

Thank you vbajock for your help.

Thank you for your help.

Tammy

 

[vbajock]

After checking with our resident C programmer, I don't think you are going to be successful. The problem lies with Access VBA itself, which is unable to handle the kind of construct your trying to use. Essentially, although one thinks of the VBA modules as Public Modules, to the underlying C code they are not, they are private modules within the application, VBA. The Public Module the error is referring to is a DLL. The error is essentially trying to tell you that you have to code your UDT into a DLL and then refer to it in your code using an API.

In your input data:
varLine = ts.ReadLine
if you know the position the data occurs in the line, why don't you just parse the line into the UDT using mid$()?

 

[tdfreeman]

Thank you for that input vbajock.

As for your question, what I am doing is I have a file with 15 record types. The first 3 characters of each record indicate which record type it is. Each record type has a different layout.

I was trying to figure out a way to read in a line and assign it to a variable that was made up of several fields. Rather than using Mid for each value, if I assigned it once to a new datatype which has subtypes of the right length, I would in effect assign values to all fields in one line. I hope this makes sense.

I am beginning to see that this will not work, although I don't really understand why this functionality doesn't exist.

Thank you.

Thank you for your help.

Tammy

 

[CautionMP]

I know that this reply is a little late but I hope that this information will provide useful.

The solution to your problem (I think) is to look at your data source as a binary file and not a text file. By connecting in binary mode the VBA engine does not attempt to understand or interpret the data in the file, instead it relies on you UDT to do this which should get past the issues that you are having. Below are the two routines that test this and hopefully will provide enough information for you apply it to your application.

Option Compare Binary
Option Explicit

' Here is your original UDT
Public Type Input_Header
    RecType     As String * 3
    HeaderDate  As String * 8
    FileName    As String * 44
End Type

Public Sub fPutUDTData()
' This creates a "dummy" file for testing
Dim udtHeader As Input_Header
Dim intFile As Integer
intFile = FreeFile
Open "C:\bintest.txt" For Binary As intFile
udtHeader.RecType = "ABC"
udtHeader.HeaderDate = "12/20/03"
udtHeader.FileName = "file1"
Put intFile, , udtHeader
End Sub

Public Sub fGetUDTData()
' Here is the piece you should be able to incorporate
' into your application
Dim udtHeader As Input_Header
Dim intFile As Integer
intFile = FreeFile
' This opens the file
Open "C:\bintest.txt" For Binary As intFile
' This picks the first line of data and loads into
' your UDT, check the help files in Access for the
' syntax on how to move through the data file.
Get intFile, , udtHeader
' This was my test output in the immediate window
Debug.Print udtHeader.RecType
Debug.Print udtHeader.HeaderDate
Debug.Print udtHeader.FileName
End Sub

Hope this helps you.
Does knowledge unshared really exist?

 

[CautionMP]

P.S. Just a footnote, when you work with Binary data types you cannot rely on Line feeds and Carriage returns to designate the end of a record like you can with “text” methods so be sure to “pad” your UDT out to cover a complete line of text because the UDT will capture the exact number of bytes required to fill the UDT. You had said that you have 15 different record types so you may need to define several UDT’s and then scrutinize the first 3 characters of the next “record” to determine which UDT (and UDT length) to populate with the data. Also you move through the binary file using the following:

Get intFile,

PositionInFileToStartAt

, udtHeader

Where PositionInFileToStartAt will need to be a variable that keeps track of where the code is at in the document.

 

[tdfreeman]

Will try within the next week or so and post how this works. Thanks CautionMP.

Thank you for your help.

Tammy

 

[psemianonymous]

No, this is all wrong.

The COBOL way (and yes, I've had an excruciatingly painful class) allows you to take a record of strings (because everything is a string, yay) and dump one string into the record of strings. And it works, because everything is a string.

VBA/Access does not have this functionality. Everyone else (sorry) got it wrong--you were trying to take a text file and split it into fixed-width records. I think the Access developers now know what you need. The short answer is that Access does this an entirely different way than COBOL did. A few things:

1. VBA can actually do the splitting of strings into substrings, you can use the string manipulation functions Left(), Mid(), Right(), and optionally InStr(). Don't use these for now, read on--but know they exist.

2. Access has a very powerful text import facility. It allows you to specify a &quot;text import specification&quot; that you can remember, thus allowing you to automate the importing of standard-format files. With this in mind, you can now import your &quot;headers&quot; into an Access table.

3. Once you have the data in the Access table, you can either run multiple update queries to scrub the data, or you can use recordsets and loop through the data, one row at a time, and presumably do more complex algorithms.


I *hope* this is what you were looking for.


Pete

 

[xyxmt]

I dont think you can do this, this is something you can do in Cobol where you assgin a string to a type field and then reference the sub fields types. I VBA you have to assign value to each sub-type

Public Type Input_Header
RecType As String * 3
HeaderDate As String * 8
FileName As String * 44
End Type

dim myString as input_header

myString.recType = "ABC"
myString.HeaderDate = Date()
myString.FileName = "File1"