Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Unzipping a file

Posted on 2010-08-24
12
Medium Priority
?
977 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 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…
Suggested Courses

705 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