Shell.Namespace returns nothing and I get Error 91 with .CopyHere

I am trying to use the Shell.Namespace construct to copy files in a WinXP system. I'm using VBA in Excel 2007. In a nutshell, here's the code I'm using:

Please Login or Register  to view this content.

Now...I have read what seems like every single post on the internet about this subject and here's what I've tried so far: (none of which has worked...)

-Tried the source and dest as both strings and variants

-tried wrapping source and dest in double parenthesis like so...

Please Login or Register  to view this content.

-tried appending a blank string or Error$ to the variable like so (with both double and single parenthesis)...

Please Login or Register  to view this content.

-tried assigning namespace to a variable like so (trying all the tricks above)...

Please Login or Register  to view this content.

-I even tried assigning the source and dest as string literals like so (again trying all the tricks above)...

Please Login or Register  to view this content.

Absolutely NONE of this works. I have tried every possible permutation of the above tricks and I still get Error 91 on the .copyHere line. When I assign the namespaces to a variable, it returns nothing, so I assume it is the .Namespace command that fails, but I can find nothing that I haven't already tried. Please help!...this is driving me nuts!

hi dbiggied, welcom to ExcelForum, it works fine:

Please Login or Register  to view this content.

There is another way to do that: CopyFolder Method, check VB help file for fetails

Originally Posted by watersev

hi dbiggied, welcom to ExcelForum, it works fine:

Please Login or Register  to view this content.

There is another way to do that: CopyFolder Method, check VB help file for fetails

See...that's the problem...I'm 100% sure that this code works for you...but it does NOT work for me...I copied your code out into a nice fresh module, and ran it and it died at the same place mine always does...at the folder2copy.CopyHere line. it executes the line where it sets the folder equal to the shell.namespace, but when you hover over the folder name after stepping through that line, the folder variable = nothing...so it's okay with the namespace assignment even though it returned nothing, then fails at the CopyHere, because the folder isn't set to anything. This is why I'm crazy.

why not you post your code as it is and returns an error?

I can reproduce Error 91 only if you put a path to unexisting folder in Namespace line. It errors out on CopyHere as the object is nothing.

This would check if object exists:

Please Login or Register  to view this content.

I'll post a few different examples of code when I get back to work...and I wondered if it wasn't finding the folder, but I did some other checks (assigning a folder variable with fso.getfolder, etc) and it returns a valid folder path...it just returns nothing when I use the namespace function. I also tried it in a vbs script file just for kicks and got the same error.

Okay...I feel like a complete idiot, and I apologize if I committed some sort of thread etiquette violation, but it occurs to me that I forgot to mention I am trying to use this functionality specifically to copy files to a zip file. Everything I have read says that Windows treats zip files as folders, so I have been trying to use the CopyHere method on a zip file. So, after EXTENSIVE fiddling, I have been able to make the namespace method work with a regular folder, but it still will not work with a zip file...as if it ISN'T treating zip files like folders. I have tried everything here...http://www.rondebruin.nl/windowsxpzip.htm and it still won't assign a zip file as a folder. Is there some windows setting that I need to change so that I can treat a zip file as a folder?

Buwah Ha Ha!!!...Threadomancy!!!...anyway, I never figured out what was going on here, so I just got myself a copy of 7zip and use the command line functionality...would rather have made the Excel internals work, but whatever...

Thanks Arlu for the code tags. Appologies for the faux pas!

Thank you for your response Josie, however I have already tried that and still get the runtime error

Please Login or Register  to view this content.

The subroutine is being called like so:

Call Zip_All_Files_in_Folder(FileNameZip, FolderName)

where FileNameZip and FolderName are Strings, and are correctly set.

To be honest, this is not my code and it's not my language!... I've just been given the task to fix it.

When you say 'change the code to use variables for source and destination folders', what exactly do you mean? Sorry if that question makes me sound like a VBA numpty...

Whoops, missed your posted code... OK...

This is very odd...

Please Login or Register  to view this content.

Here FileNameZip = "N:\ANA\Anas\...\TUP22.\zip, and oDest is nothing (I've used ... just to avoid typing out the entire path)

Please Login or Register  to view this content.

Here FolderName = "N:\Dev\TUP", and oSource = "Tup"

The empty Zip file is created in the correct place (FileNameZip).

Both FileNameZip and FolderName are correct.

FolderName is short, 10 characters.
FileNameZip is longer, 60 characters, if I've counted correctly.

shoot - can't load zip file. see ya

Back to the original code... I have discovered that if I change FileNameZip to the temp file on my C:\ drive it works. I think there must be some issue with zipping to a networked drive. I am going to try zipping to the temp file on my C:\ drive, copying this file to the N:\ drive, and then deleting the temp file on C:\

Yeah, that seems to have worked

After a bit of googling I have read that a number of Windows 7 users have experienced problems zipping straight to a network drive.

Anyway, thank you for your help JosephP