April15Hater
Accountant
- Local time
- Today, 16:52
- Joined
- Sep 12, 2008
- Messages
- 349
Hi-
I am pretty new to class modules, properties, methods, arrays, etc etc. I'm trying to learn more and in doing so I've run into a snag that I just can not get past. Moreover, it's probably my unfamiliarity with arrays, when to use parentheses, when not to use them, how to dimension them, etc.
Form_frmTemplate is the calling module.
clsFieldData is a series of properties that I am trying to call upon from clsSTIData (and other classes as well) that will store values that other procedues can call upon.
clsSTIData is (as of right now) 2 methods that will combine the workbooks of a user defined location, and a few properties that aren't used yet.
So Form_frmTemplate calls upon clsSTIData which pulls various properties from clsFieldData. Now I know I can assign the values using a UDT, but what is the fun in that? I also read in Reddick's Coding Convention that classes can replace UDTs and are better coding practice.
I'm getting the following error on the following line in the clsSTIData.CombineExports method (Full code below):
"Object doesn't support this property or method"
If I remove parentheses, I get the following:
Metod or Data Member not found.
on
Form_frmTemplate:
clsFieldData:
clsSTIData:
I am pretty new to class modules, properties, methods, arrays, etc etc. I'm trying to learn more and in doing so I've run into a snag that I just can not get past. Moreover, it's probably my unfamiliarity with arrays, when to use parentheses, when not to use them, how to dimension them, etc.
Form_frmTemplate is the calling module.
clsFieldData is a series of properties that I am trying to call upon from clsSTIData (and other classes as well) that will store values that other procedues can call upon.
clsSTIData is (as of right now) 2 methods that will combine the workbooks of a user defined location, and a few properties that aren't used yet.
So Form_frmTemplate calls upon clsSTIData which pulls various properties from clsFieldData. Now I know I can assign the values using a UDT, but what is the fun in that? I also read in Reddick's Coding Convention that classes can replace UDTs and are better coding practice.
I'm getting the following error on the following line in the clsSTIData.CombineExports method (Full code below):
"Object doesn't support this property or method"
Code:
ReDim FieldInfo().Description(1 To .RecordCount) As StringIf I remove parentheses, I get the following:
Metod or Data Member not found.
on
Code:
ReDim FieldInfo.Description(1 To .RecordCount) As StringForm_frmTemplate:
Code:
Private Sub cmdExport_Click()
Dim objSTIExport As clsSTIData
Set objSTIExport = New clsSTIData
objSTIExport.CombineExports
End SubclsFieldData:
Code:
Option Explicit
Private pFieldID() As String
Private pDescription() As String
Private pStartColumn() As Long
Private pEndColumn() As Long
Private pDataType() As Long
Private pColumnWidth() As Long
Private pImportColumn() As Long
Public Property Let FieldID(Value() As String)
pFieldID() = Value()
End Property
Public Property Get FieldID() As String()
FieldID = pFieldID()
End Property
Public Property Let Description(Value() As String)
pDescription() = Value()
End Property
Public Property Get Description() As String()
Description = pDescription()
End Property
Public Property Let StartColumn(Value() As Long)
pStartColumn() = Value()
End Property
Public Property Get StartColumn() As Long()
StartColumn = pStartColumn()
End Property
Public Property Let EndColumn(Value() As Long)
pEndColumn() = Value()
End Property
Public Property Get EndColumn() As Long()
EndColumn = pEndColumn()
End Property
Public Property Let DataType(Value() As Long)
pDataType() = Value()
End Property
Public Property Get DataType() As Long()
DataType = pDataType()
End Property
Public Property Let ColumnWidth(Value() As Long)
pColumnWidth() = Value()
End Property
Public Property Get ColumnWidth() As Long()
ColumnWidth = pColumnWidth()
End Property
Public Property Let ImportColumn(Value() As Long)
pImportColumn() = Value()
End Property
Public Property Get ImportColumn() As Long()
ImportColumn = pImportColumn()
End PropertyclsSTIData:
Code:
Option Explicit
Private xlApp As excel.Application
Private plngBeginLocation As Long
Private plngEndLocation As Long
Private plngDataType As Long
Private Const mvarDefaultLocation As Variant = "[URL="file://ns-tampvfs01/Shared/Securities"]\\NS-TAMPVFS01\Shared\Securities[/URL] Financial Management" & _
"\Derivatives\Data Integrity\DAR to PRIME Rec\"
Private Sub Class_Initialize()
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Application.Visible = True
'.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
End Sub
Public Property Get BeginLocation(Header As String) As Long
plngBeginLocation = DLookup("BeginLocation", "tblSTIDataSchema", "Description = '" & Header & "'")
BeginLocation = plngBeginLocation
End Property
Public Property Get EndLocation(Header As String) As Long
plngEndLocation = DLookup("EndLocation", "tblSTIDataSchema", "Description = '" & Header & "'")
EndLocation = plngEndLocation
End Property
Public Property Get DataType(Header As String) As Long
plngDataType = DLookup("DataType", "tblSTIDataSchema", "Description = '" & Header & "'")
DataType = plngDataType
End Property
Property Get FolderLocation(DefaultLocation) As String
'Get workbook File Name and Extension
Dim strLocation As String
FolderLocation = FolderBrowser(mvarDefaultLocation)
ChDir FolderLocation
End Property
Public Sub CombineExports()
Dim intA As Integer
Dim intImportColumnCnt As Integer
Dim rsFieldData As New ADODB.Recordset
Dim wksImport As Worksheet
Dim FieldInfo As New clsFieldData
With rsFieldData
.Open _
ActiveConnection:=CurrentProject.Connection, _
Source:="SELECT ID, Description, BeginLocation, EndLocation, DataType " & _
"FROM tblSTIDataSchema;", _
CursorType:=adOpenStatic, _
LockType:=adLockReadOnly
ReDim FieldInfo.Description(1 To .RecordCount) As String
ReDim FieldInfo().ColumnWidth(1 To .RecordCount) As Long
ReDim FieldInfo().DataType(1 To .RecordCount) As Long
ReDim FieldInfo().ImportColumn(1 To UBound(FieldInfo().FieldID))
intImportColumnCnt = 0
For intA = 1 To .RecordCount
FieldInfo().Description(intA) = !Description
FieldInfo().ColumnWidth(intA) = (!EndLocation - !BeginLocation) + 1
If UBound(Filter(FieldInfo.FieldID, !ID)) < 0 Then
FieldInfo().DataType(intA) = 9
Else
FieldInfo().DataType(intA) = !DataType
intImportColumnCnt = intImportColumnCnt + 1
FieldInfo().ImportColumn(intA) = intImportColumnCnt
End If
.MoveNext
Next intA
.Close
End With
Set wksImport = OpenSourceWks(FieldInfo())
End Sub
Private Function OpenSourceWks(FieldInfo As clsFieldData) As Worksheet
Dim wksSource As Worksheet
Dim qtbSource As QueryTable
Dim rngLastRow As Range
Dim strFolderLocation As String
Dim strFileName As String
Dim intA As Integer
strFolderLocation = FolderLocation(mvarDefaultLocation) 'Set folder location
strFileName = Dir(strFolderLocation & "\*.dat") 'Set filename
Set wksSource = xlApp.Workbooks.Add.ActiveSheet 'Set worksheet
With wksSource
'Write description header and format columns for data type
For intA = 1 To UBound(FieldInfo().Description)
.Cells(1, intA).Value = FieldInfo().Description(intA)
Select Case FieldInfo().DataType(intA)
Case Is = 1
.Cells(, FieldInfo().ImportColumn(intA)).EntireColumn.NumberFormat = "General"
Case Is = 2
.Cells(, FieldInfo().ImportColumn(intA)).EntireColumn.NumberFormat = "@"
End Select
Next intA
'Loop files in user chosen directory
Do While strFileName <> ""
'Find LastRow
Set rngLastRow = .Range("A:A").Find(What:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
Select Case rngLastRow.Row
'No headers, therefore empty FieldInfo argument.
Case Is = 0
Exit Function
Case Is = 1
Case Is > 1
End Select
'Import text data into excel columns
Set qtbSource = wksSource.QueryTables.Add(Connection:="TEXT;" & strFolderLocation & strFileName, _
Destination:=wksSource.Cells(1, 1))
With qtbSource
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = FieldInfo.ColumnWidth
.TextFileColumnDataTypes = FieldInfo.DataType
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
strFileName = Dir
Loop
End With
End Function
