Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Unzipping a file

Posted on 2010-08-24
12
Medium Priority
?
984 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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
 
LVL 3

Accepted Solution

by:
baffy2000 earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

926 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