VBA: Zip file out put rename

Question

I'm trying to rename the output of my file when I unzip. I have tried renameing to test.xls But it throws a object variable not set error. Is there another way other then scanning .datecreated on all the files in the folder? If there is a way to rename the file on unzip that would be best.

Set oApp = CreateObject("Shell.Application")
    oApp.NameSpace("C:\Users\**\Downloads\TempFolder\test.xls").CopyHere oApp.NameSpace("C:\Users\**\Downloads\TempFolder\testzip.zip").Items
Set oApp = Nothing

Answer 1

The CopyHere method is only valid for folder objects (this includes zip files when zipping), not for regular files. Hence the error message.

See MSDN or here .

I would extract the zip to a folder that is guaranteed to be empty.
Then move the (single) file to your actual target folder, renaming it in the process.

Const Zipfile = "C:\Users\**\Downloads\TempFolder\testzip.zip"
Const EmptyFolder = "C:\Users\**\Downloads\EmptyFolder"
Const TargetFolder = "C:\Users\**\Target"
Dim strFile As String

Set oApp = CreateObject("Shell.Application")
' Unzip into empty folder
oApp.NameSpace(EmptyFolder).CopyHere oApp.NameSpace(Zipfile).Items
Set oApp = Nothing

' Get first and only file
strFile = Dir(EmptyFolder & "\*.*")
' Move and rename
Name EmptyFolder & "\" & strFile As TargetFolder & "\test.xls"