Need help with Array with type, invalid qualifier & runtime er
Thanks! Joel, we did it! What i wasn't doing was "Public MyArray(10) As
CBTfilesArray" I also had to go back and change some of the code that ref to
CBTfilesArray to MyArray. There were some other coding issues but it it
running now. Thanks again.
"Joel" wrote:
Try this test code
Public MyArray(10) As CBTfilesArray
Type CBTfilesArray
ManagerName As String
CBTtrackingYear As Integer
CBTtrackerFileName As String
End Type
Sub test()
MyArray(0).ManagerName = 1
For Count = 0 To UBound(MyArray)
ListbManagerNameSelection.AddItem _
MyArray(Count).ManagerName
Next Count
End Sub
"Mr. GetRight" wrote:
I know what you mean, only reason I'm using it is because no one else knows
how to use Access and they like Excel. I tryied that but it is still saying
Invalid Qualifer on CBTfilesArray.
"Joel" wrote:
I HATE EXCEL!!!!!
This is another quirk of excel. Excel lets you fill a list box using anthe
array method when you use an array like
Listbox1.list = Array(1,2,3,4,5)
but doesn't allow you to fill it with a variable declared as an array
Dim MyList() as Variant
MyList(0) = 1
MyList(1) = 2
MyList(2) = 3
MyList(3) = 4
Listbox1.list = MyList ' this doesn't work
Instead
for Count = 0 to ubound(CBTfilesArray)
ListbManagerNameSelection.additem _
CBTfilesArray(Count).ManagerName
next Count
"Mr. GetRight" wrote:
I tried it but it still says "Invalid Qualifier" for that line. Also, on my
org code, that line should have just been:
ListbManagerNameSelection.List = CBTfilesArray.ManagerName
I forgot to delete the For loop from before. per book, the above code will
input array in list without having to loop. But either way i still get the
Invalid Qualifier Error when running it.
"Joel" wrote:
You need to put count as the index to the array in the code below
For Count = 1 To NumFiles
ListbManagerNameSelection.List = _
CBTfilesArray(Count).ManagerName
'Output manager's name in list box
'to be selected by user
Next Count
"Mr. GetRight" wrote:
Ok. Not sure what I am doing wrong so i will start off with what I want to do.
Below is my code that is to go out to location of workbook, look for other
workbooks saved in subfolders,save their location, then manager's name, and
year from the file name to an array. ex location: C:\Documents and
Settings\Guest\Desktop\cand be deleted\2008 CBT Tracker for DoeJane.xls
Problems:
1 I am getting an complier error for line starting off
"ListbManagerNameSelection". It is for the array. I was trying to make it
global because I will reuse them on another part of the workbook.
I removed that part out to make sure the rest would work but when it
attempts to save data to the first array i receive run time error 424. what
am i doing wrong?
Private Sub UserForm_Activate()
If (NumFiles = LoadFileDate(CBTfilesArray)) = True Then ' Goes to This
Workbook code screen and loads manger name, year, file path\name
For count = 1 To NumFiles
ListbManagerNameSelection.List = CBTfilesArray.ManagerName
'Output manager's name in list box to be selected by user
Next count
Else
MsgBox "There were no files found."
End If
End Sub
'Code below is saved in Module
Option Explicit
Public CBTfilesArray() As Variant
Type CBTfilesArray
ManagerName As String
CBTtrackingYear As Integer
CBTtrackerFileName As String
End Type
Function LoadFileDate()
Dim fs As Object
Dim i As Integer
Dim Mypath As String
Mypath = ActiveWorkbook.Path
Set fs = Application.FileSearch
MsgBox Mypath
With fs
'Change below to give the name of the Directory you want to search
.LookIn = Mypath & "\"
.SearchSubFolders = True
.FileName = "*.xls"
If .Execute() 0 Then
For i = 1 To .FoundFiles.count
ReDim Preserve CBTfilesArray(i - 1)
CBTfilesArray(i - 1).ManagerName = Mid(.FoundFiles(i),
InStrRev(.FoundFiles(i), " ") + 1) 'Get Manager Name and adds it to array
CBTfilesArray(i - 1).CBTtrackingYear = (Mid(.FoundFiles(i),
InStrRev(.FoundFiles(i), "\") + 1, 4)) ' Get the year of the CTB Tracker
CBTfilesArray(i - 1).FileName = (.FoundFiles(i)) 'Get file
address and addes to array
Next i
LoadFileDate = .FoundFiles.count
Else
LoadFileDate = False
End If
End With
If LoadFileDate Then
MsgBox (LoadFileDate + 1) & " files found. Managers are " &
CBTfilesArray(1).ManagerName
Else
MsgBox "No files were loaded into Array"
End If
End Function
|