Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

FileSystemObject Issues

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
bgetson - 29 May 2008 07:40 GMT
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.
Leith Ross - 29 May 2008 08:30 GMT
> 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
[quoted text clipped - 28 lines]
>
> 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
bgetson - 29 May 2008 08:41 GMT
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?
Leith Ross - 29 May 2008 08:46 GMT
> 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
[quoted text clipped - 4 lines]
>
> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.