Excel VBA FileSystemObject

Topic

New Reply

I have the following code read a list of files from column A and then move them based on the new name in column B. The code works only when run from this particular file. When I try to use the same code in another file I get the following message:

Compile Error:
User defined type not defined

I am using Excel 2000 SR1. Why does it work in one place and not another? I’ve tried closing Excel and even rebooting with no success in making this work.

Sub MoveFiles()
Application.ScreenUpdating = False
Range(“A2”).Activate

Dim fs As New FileSystemObject
While ActiveCell “”
a = ActiveCell.Value
b = ActiveCell.Offset(0, 1).Value
fs.MoveFile a, b
ActiveCell.Offset(1, 0).Select

Wend
End Sub

Reply | Quote

Replies

Does the original file (i.e. the file that works ok) have a class module or some other procedure that defines the FileSystemObject. If so you need to copy it to your new workbook.

Andrew

Reply | Quote

This code in in the ‘This workbook’ section and there are no other modules.

Reply | Quote

I can understand that you get the error “User defined type not defined” if there is no class module present, but I do not understand that you can make this code run from a file where there is no class module with the name “FileSystemObject”.

Reply | Quote

As far as I know, you must have a class module named “FileSystemObject”, in which you have defined the “MoveFile” method. Can you please add this code, otherwise I cannot see what is going wrong.
On the other hand, I do not understand why you need a class module to move “names of files”, which have to be text (strings), from one column to another. Can’t you just select and copy them and then paste them into another column?

Reply | Quote

I have ‘c:dataexceltesttest1.xls’ in column A
and ‘c:dataexcelnewtest1.xls’ in column B
I am not moving the text. I am moving test1 file from folder ‘test’ to folder ‘new’.

Reply | Quote

Would it be possible to post a copy of the workbook that actually runs the code you described. There is no need to include any data in th eworksheets.

Andrew C

Reply | Quote

File is attached. Thanks! Also…
What is the difference in using FileSystemObject as
opposed to Scripting.FileSystemObject?

Reply | Quote

Thanks for the file. What you need to do is, in the VBA environment, go to Tools, References, and look for Microsoft Scripting Runtime, and make sure it is ticked.

I dont really know enough about this to answer your final question, but hope at least the above will sort out your original problem.

Andrew

Reply | Quote

Thanks, Andrew! This thread gave me exactly the solution I was looking for.

This post may be a bit of a waste of bandwidth, but I think it’s important to note the usefulness of the Lounge’s Search function.There are tons of posts addressing problems of Loungers past and present.

Cheers!

Reply | Quote