Need help with Array with type, invalid qualifier & runtime error
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 |
Need help with Array with type, invalid qualifier & runtime error
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 |
Need help with Array with type, invalid qualifier & runtime er
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 |
Need help with Array with type, invalid qualifier & runtime er
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 |
Need help with Array with type, invalid qualifier & runtime er
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 |
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 |
All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com