Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject Issues
I'm getting an "invalid procedure call or argument" every time I try
to take control of a collection of files that I've grabbed. With this setup: Dim wkbk1 As Workbook, wkbk2 As Workbook Dim objFSO As FileSystemObject Dim objFiles As Files Set objFSO = New FileSystemObject Set objFiles = objFSO.GetFolder(strFolder).Files 'strFolder is some path Set wkbk1 = Workbooks.Open(objFiles(1)) Set wkbk2 = Workbooks.Open(objFiles(2)) I get an error the moment objFiles(1) or objFiles.Item(1) is called. I know there are two files in the proper folder. objFiles.Count returns 2. For Each f in objFiles Workbooks.Open(f) Next ^ This properly opens both files as I thought my original call should have. What am I doing wrong? On a side note, what is the proper way to open up a number of files in a specific directory and assign each of them to a variable, so that I can mess with them all simultaneously? In this case, I just happened to know there were only two files, and I thought this would be easiest. Thanks for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject Issues
On May 28, 11:40 pm, bgetson wrote:
I'm getting an "invalid procedure call or argument" every time I try to take control of a collection of files that I've grabbed. With this setup: Dim wkbk1 As Workbook, wkbk2 As Workbook Dim objFSO As FileSystemObject Dim objFiles As Files Set objFSO = New FileSystemObject Set objFiles = objFSO.GetFolder(strFolder).Files 'strFolder is some path Set wkbk1 = Workbooks.Open(objFiles(1)) Set wkbk2 = Workbooks.Open(objFiles(2)) I get an error the moment objFiles(1) or objFiles.Item(1) is called. I know there are two files in the proper folder. objFiles.Count returns 2. For Each f in objFiles Workbooks.Open(f) Next ^ This properly opens both files as I thought my original call should have. What am I doing wrong? On a side note, what is the proper way to open up a number of files in a specific directory and assign each of them to a variable, so that I can mess with them all simultaneously? In this case, I just happened to know there were only two files, and I thought this would be easiest. Thanks for the help. Hello bgetson, I assume you have set a reference in your VBA project to the Windows Script Host Object module, since you are using early binding. Instead of dimensioning objFiles As Files, change it to a generic object: Dim objFiles As Object. The Workbook.Open requires the file name string to be fully qualified if it is in a different directory than the current one. Use the Path property for this. Dim wkbk1 As Workbook, wkbk2 As Workbook Dim objFSO As FileSystemObject Dim objFiles As Object Set objFSO = New FileSystemObject Set objFiles = objFSO.GetFolder(strFolder).Files 'strFolder is some path Set wkbk1 = Workbooks.Open(objFiles(1).Path) Set wkbk2 = Workbooks.Open(objFiles(2).Path) Sincerely, Leith Ross |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject Issues
Leith, thank you for your suggestion, but that still hasn't seemed to
solve my problem. You're right, I added the reference to "Microsoft Scripting Runtime." However, in my process, the invalid call/procedure is being tagged on objFiles(1). It would give me the same error if I called: Set f = objFiles(1) or Set f = objFiles.Item(1). Dimensioning objFiles as a generic Object didn't seem to solve this issue. Any other ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject Issues
On May 29, 12:41 am, bgetson wrote:
Leith, thank you for your suggestion, but that still hasn't seemed to solve my problem. You're right, I added the reference to "Microsoft Scripting Runtime." However, in my process, the invalid call/procedure is being tagged on objFiles(1). It would give me the same error if I called: Set f = objFiles(1) or Set f = objFiles.Item(1). Dimensioning objFiles as a generic Object didn't seem to solve this issue. Any other ideas? Hello bgetson, I missed an important argument assigment when looking at the Item property, It takes a KEY value or in this case the Name of the File to work. Bit different from a regular Item property for a collection. The only way to access unknown files in the collection is to use For Each...Next. For Each f In MyFiles Workbook.Open f.Path Next f Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FileSystemObject help please. | Excel Programming | |||
FileSystemObject help please. | Excel Programming | |||
FileSystemObject help please. | Excel Programming | |||
Filesystemobject Help | Excel Programming | |||
Help with FileSystemObject? | Excel Programming |