Unzip Folder with VBA Code 1

[hext2003]

I have a file that I need to download daily. I save it in the same spot everyday. It saves it as S:\ERETR\Downloads\00 Year-to-date.xml.zip\00 Year-to-date.xml

First I check to see if the process file exists? if so kill it so I can recreate it from the new download

Dim FSO
Dim file as String

file = "s:\ERETR\ProgramFiles\ToProcessOPN.xls"
Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FileExists(file) Then
Kill "s:\ERETR\ProgramFiles\ToProcessOPN.xls"
End If

the above works fine.

Next I need to unzip this file?
I need this....
S:\ERETR\Downloads\00 Year-to-date.xml.zip\00 Year-to-date.xml

to become this...
S:\ERETR\Downloads\00 Year-to-date.xml

the last thing I tried is this..
Set FSO = CreateObject("Shell.Application")
FSO.Namespace("S:\ERETR\Downloads\").CopyHere FSO.Namespace("00 Year-to-date.xml").items

Any Suggestions? am I sorta on the right track? I get an object variable is not set error message

TIA

 

[PWise]

how acout

Set FSO = CreateObject("Shell.Application")
Set FSO.Namespace("S:\ERETR\Downloads\").CopyHere FSO.Namespace("00 Year-to-date.xml").items

 

[hext2003]

Tried that then I get a syntax error - and it won't compile

 

[PWise]

How About

FSO.Namespace("S:\ERETR\Downloads\00 Year-to-date.xml.zip").CopyHere "00 Year-to-date.xml"

 

[hext2003]

Good Though get the error

Object doesn't support this property or method

 

[hext2003]

I take it back... the error message is gone. The code goes thru but it appears to not have done anything.

I still have
S:\ERETR\Downloads\00 Year-to-date.xml.zip\00 Year-to-date.xml

I need it to become this...
S:\ERETR\Downloads\00 Year-to-date.xml

or else it copied the 00 Year-to-date.xml file someplace unknown.. I am doing a search right now to find it.

 

[PWise]

How About

FSO.Namespace("S:\ERETR\Downloads").CopyHere "00 Year-to-date.xml.zip\00 Year-to-date.xml"

 

[PWise]

sorry SB
FSO.Namespace("S:\ERETR\Downloads").CopyHere "S:\ERETR\Downloads\00 Year-to-date.xml.zip/00 Year-to-date.xml"

 

[hext2003]

Thank you! I modified your last code just a bit and it worked

FSO.Namespace ("S:\ERETR\Downloads").CopyHere "S:\ERETR\Downloads\00 Year-to-date.xml.zip\00 Year-to-date.xml"

 

[hext2003]

Thank you!! I see I was just getting it as you were reposting. Thank you!! Thank you!!

 

[hext2003]

can I add another ?? here

What if I want to Delete the zip file folder after I have moved the copy and done what I need to

I want this gone
S:\ERETR\Downloads\00 Year-to-date.xml.zip\00 Year-to-date.xml

Leaving Just
S:\ERETR\Downloads\00 Year-to-date.xml

 

[PWise]

how about

kill S:\ERETR\Downloads\00 Year-to-date.xml.zip\00 Year-to-date.xml

 

[hext2003]

Thank you

I did

Kill "S:\ERETR\Downloads\00 Year-to-date*"

this takes out the Zip and the Copied one after I have done stuff to it.

Thank you again for your help!