Solved

VBA Unzipping a file

Posted on 2010-08-24
12
967 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
Comment Utility
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
Comment Utility
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
Comment Utility
If it was running fine that begs a permissions issue ... any indication of changes in the last week?

Chris
0
 

Author Comment

by:mcs26
Comment Utility
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
Comment Utility
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
Comment Utility
Hi baffy,

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

Thanks
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 3

Accepted Solution

by:
baffy2000 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
It copied it as a ZIP!!!!?

Chris
0
 

Author Comment

by:mcs26
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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

11 Experts available now in Live!

Get 1:1 Help Now