Solved

VBA Unzipping a file

Posted on 2010-08-24
12
968 Views
Last Modified: 2012-05-10
Hi,

I found some code from http://www.rondebruin.nl/windowsxpunzip.htm about to unzip a file using VBA. The problem is with my code is that it debugs at the line "oAPP.Namespace(FileNameFolder).CopyHere oAPP.Namespace(Fname).Items". The message is object variable or with with block not set.

The code is as below & as always any help would be great!

Thanks
Mark

Sub UnzIpp()

'Unzip JPM Files
Dim FSO As Object
Dim oAPP As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
 
'JPM_File = ("G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Valuation Summary")

Fname = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Valuation Summary_20100823.zip"

FileNameFolder = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Unzipped\Valuation Summary_20100823.xls"

Set oAPP = CreateObject("Shell.Application")
    oAPP.Namespace(FileNameFolder).CopyHere oAPP.Namespace(Fname).Items

      On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.DeleteFolder Environ("Temp") & "\Temporary Directory*", True

    Set wbTemp = Workbooks.Open(JPM_File)
    wbTemp.SaveAs ("G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Unzipped\Valuation Summary_20100823.xls")
    'wbTemp = JPM_File

End Sub

Open in new window

0
Comment
Question by:mcs26
  • 5
  • 4
  • 3
12 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33508591
Noting your name is MArk ... have you edited the paths to your own system?, for example:

Fname = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Valuation Summary_20100823.zip"

Chris
0
 

Author Comment

by:mcs26
ID: 33508647
Hi Chris,

The G: drive is the shared drive at my workplace. The odd thing was that this code was running fine last week but not this. The date extension ie_20100823 changes everyday but it is always selecting the correct date.

I have also checked the zip file is in G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Valuation Summary_20100823.zip as well.

Thanks

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33508683
If it was running fine that begs a permissions issue ... any indication of changes in the last week?

Chris
0
 

Author Comment

by:mcs26
ID: 33508691
None that I can tell & there is only one other person who could have changed something, which they have told they haven't. I not really sure what to even try?
0
 
LVL 3

Expert Comment

by:baffy2000
ID: 33508722
This may sound weird, but try changing

oAPP.Namespace(FileNameFolder).CopyHere oAPP.Namespace(Fname).Items

to

oAPP.Namespace("" & FileNameFolder).CopyHere oAPP.Namespace("" & Fname).Items

0
 

Author Comment

by:mcs26
ID: 33508750
Hi baffy,

Just tried it that way and still the same error message Object variable or with block variable not set.

Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Accepted Solution

by:
baffy2000 earned 500 total points
ID: 33508805
Ok, well there does seem to be something weird going on here. Look at this:

http://www.codeguru.com/forum/showthread.php?t=443782

You might also try:

oAPP.Namespace((FileNameFolder)).CopyHere oAPP.Namespace((Fname)).Items

Other than this, the question comes down to "which object is not created?" There are two candidates for this: either oAPP is not set, which I would say is unlikely, or the Namespace(FileNameFolder) object is not set. You can find out which using the IDE at the point the code stops.

I would urge you to look *very* closely at those file paths - are you sure they're exactly the same? A digit out of order? An underscore where it shouldn't be?

Copy the zip file path from your code, i.e.

G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Valuation Summary_20100823.zip

and paste it into the address bar of explorer - it should open the zip file if the path is correct.

If none of these work I would try fiddling around with variations based on the link at the start of this email. *Try defining your filepaths as Strings instead of Variants (you should probably do this anyway), and then try the variations on the link, etc.
*Try abandoning the variables completely and just pass the hardcoded paths to Namespace().
*Try specifying the paths using UNC instead of drive mapping (i.e. \\<server>\<share>\Shared\High Yield\Chris\Daily Performance... etc.)

If the problem is indeed to do with the Namespace object, you may have to get creative to solve it.
0
 

Author Comment

by:mcs26
ID: 33508827
Think your right baffy about it being the namespace(FileNameFolder) or namespace(Fname) that is casuing the problem.

I just changed "FileNameFolder = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Unzipped\Valuation Summary_20100823.xls" to FileNameFolder = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Unzipped\" and it has worked?

Stupid question here but the oAPP.Namespace(FileNameFolder).CopyHere location is that where the zip file currently is or where we intend to paste it?

Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33508842
It copied it as a ZIP!!!!?

Chris
0
 

Author Comment

by:mcs26
ID: 33508938
It looks like oAPP.Namespace(FileNameFolder).CopyHere is where you wish to copy the unzipped file but you cannot specify the name it is called. The oAPP.Namespace(Fname).Items is the name of the zip file you wish to unzip.

So I should of (and did until someone changed it) of had the below,

Sub UnZip()

'Unzip JPM Files
Dim FSO As Object
Dim oAPP As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
 

Fname = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Valuation Summary_20100820.zip"
'FileNameFolder = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Unzipped\Valuation Summary_20100823.xls"
FileNameFolder = "G:\Shared\High Yield\Chris\Daily Performance\Daily JPM Prices\Unzipped\"

Set oAPP = CreateObject("Shell.Application")
    oAPP.Namespace(FileNameFolder).CopyHere oAPP.Namespace(Fname).Items
    'oAPP.Namespace("" & FileNameFolder).CopyHere oAPP.Namespace("" & Fname).Items

      On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.DeleteFolder Environ("Temp") & "\Temporary Directory*", True

End Sub

Thanks for the help!
0
 
LVL 3

Expert Comment

by:baffy2000
ID: 33508990
NameSpace(<Copies to this folder>).CopyHere <This is what it copies>

So to copy MyFile.txt to c:\Temp:  NameSpace("C:\Temp").CopyHere "MyFile.txt"

So, actually, what you did makes sense. Although, has it extracted the .xls or just copied the zip?
0
 
LVL 3

Expert Comment

by:baffy2000
ID: 33509010
Actually, looking at that code you've just posted, it looks as if Namespace is simply viewing your .zip file as a compressed folder, which is why it works. And is not that surprising actually (assuming you're using XP or above)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now