Excel VBA Using FileSystemObject Problem

[dougcpa]

I have a workbook where I work with Excel files via VBA which involves defining a variable as an object, then setting that object as a new filesystemobject.

The code would look like this:

Sub Test()

Dim fso as Object

Set fso = CreateObject("Scripting.FileSystemObject")

End Sub

Of course there would be additional code after that working with the fso variable. Anyway, I've used this coding many times and the workbooks are stored on a file server share and has been working fine.

I was tasked with setting up a macro for another user in Excel and so I created a file on a separate file server share they use for their location. When I use this exact same coding I get an error message saying ActiveX can't create the object, error 429. The debugger highlights the set fso = CreateObject("Scripting.FileSystemObject") line of code.

The really odd part is that if I move that file back to my normal file server work area I'll still get that error message when trying to run the script. If I start a new file on our file server work directory and paste the code into it, then the code works just fine.

Initially I thought it was workbook related, but now I'm thinking there is some Windows wizardry going on with file and directory permissions.

From what i can tell looking at the file share permissions, my normal working directory shows me as having everything checked except for "special permissions." In the other directory my permissions appear to be everything except Full Control and Special Permissions.

Google searching has failed me so far.

Anyone have any ideas on what is causing this?

 

[CalcSux78]

Might want to look into the References loaded on the user's workstation. You could add a reference to the Microsoft Scripting Runtime and declare like this:

Set fso = New Scripting.FileSystemObject

 

[dougcpa]

Thing is, the testing I've done has all been done on my workstation. So if I try and run the script from a file saved at location A it works fine. If I then move the file to location B and try and run it, it fails with that ActiveX error message. And now, if I try and move that file back to location A and run the script from there it will continue to give the error message.

And when I get that error, all I have to do is copy the code, paste it into a new workbook and save that workbook to location A and it will run just fine.

I'm honestly completely dumbfounded by the behavior I'm seeing with this file, especially where once I move it to loc B the file becomes apparently irreparable.

 

[Kyle123]

Are there any missing references when you get the error?

 

[dougcpa]

Are there any missing references when you get the error?

When I first got the error message I checked references in the VBA editor and everything that should be checked was checked. However, it shouldn't matter because I can use my computer and pull up the file from location A and it runs fine. Copy that file to location B and on my computer I now get that error message. Copy the file back to location A and it continues to give the error message. In each three instances, the VB Reference options that are checked off remained the same. The only way I can fix the file after moving it back to Location A is to copy the code into a new workbook and delete the old file.

 

[Norie]

If you open the registery on the machine you are having the problem on is Scripting.FileSystemObject listed under HKEY_CLASSES_ROOT?