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

[dbiggied]

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!

[shg]

There are examples at http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx and http://books.google.com/books?id=wlL...page&q&f=false

[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

[dbiggied]

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.

[watersev]

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

[watersev]

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.

[dbiggied]

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.

[dbiggied]

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?

[dbiggied]

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

[NickAustin]

Did this ever get solved? It says solved at the top of the page, but the answer does not seem to exist.

I am also trying to copy files from a folder to a compressed zip file, and I also get a Runtime Error 91: Object variable or with block variable not set.

Please Login or Register  to view this content.

I have tried all the solutions in all the threads I have come across online. I've added double parenthesis, "" &, Error$ &, changing strings to variants and back again, putting it in a With block, swearing at it, praying to the Gods,... nothing works, and it's making me cross!

It used to work, until we upgraded to Excel 2010.

The full code is here:

'Modified from code by Ron de Bruin
'Windows Zip

Please Login or Register  to view this content.

'Ron de Bruin

Please Login or Register  to view this content.

Any new ideas would be greatly appreciated!

Thanks,
Nick

[NickAustin]

Here is the code again, this time with irrelevant bits from previously tried out 'fixes' taken out... oh, and I've triead casting with CVar too.


'Modified from code by Ron de Bruin
'Windows Zip

Please Login or Register  to view this content.

[arlu1201]

NickAustin,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[NickAustin]

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

[JosephP]

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[NickAustin]

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

[JosephP]

please post the exact code you tried-that correction makes your last code work perfectly in 2010 for me

[NickAustin]

Please Login or Register  to view this content.

[JosephP]

ok-that exact code works perfectly for me-how are you calling it?

if you change the code to use variables for source and destination folders are they both Nothing at the point of error?

Please Login or Register  to view this content.

[NickAustin]

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

[JosephP]

I mean use the code version I just posted ;-)
then when the error occurs use the Locals window in the VBEditor to see if oSource and oDest are Nothing or correctly set

[NickAustin]

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"

[JosephP]

couple questions then
-can you see the empty Zip file in explorer?
-are you 100% certain that the path in the code is correct?
-how long is the path (# of characters) to the zip file?
(I assume the TUP22.\zip is a typo?)

[NickAustin]

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.

[JosephP]

let's get a little wordier-what message do you get from this

Please Login or Register  to view this content.

[NickAustin]

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:\

[NickAustin]

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