Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA FileSystemObject

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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?

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

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

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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'.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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".

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

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

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA FileSystemObject

    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!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •