VBA Error on Namespace().CopyHere...and...Namespace().items

Question

I'm attempting to modify a VBA script from another post (26486871).

The script will download a Zip file, extract a text file and import the data to Excel.

I don't know VBA so I'll tackle each of the functions one at-a-time.

1. Create a temp directory with a randomized name................................Complete
2. Download a Zip file from a public server...............................................Complete
3. Extract the text file (20MB, tab-delimited)..............................................Error
4. Import the data into the open worksheet (overwrite the existing data)...Not Yet

On the Extract portion, I'm receiving a run-time error on the following line:

objOApp.Namespace(FileNameToUnzip).CopyHere objOApp.Namespace(varFileNameFolder).items, 256

"Run-time error '91: Object variable or With block variable not set."

When I hover my cursor over the variables while in Debug Mode, the directory and filenames are correct. I'm unsure what is not set. I appreciate any help.

Option Explicit
'Main Procedure
Sub DownloadExtractAndImport()

Dim url As String
Dim targetFolder As String, targetFileZip As String, targetFileTXT As String

Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
Dim newSheet As Worksheet

url = "http://www.example.com/data.zip"
targetFolder = Environ("TEMP") & "\" & RandomString(6) & "\"
MkDir targetFolder
targetFileZip = targetFolder & "data.zip"
targetFileTXT = targetFolder & "data.txt"

'1 download file
DownloadFile url, targetFileZip

'2 extract contents
Call UnZip(targetFileZip, targetFolder)


End Sub

Private Sub DownloadFile(myURL As String, target As String)

Dim WinHttpReq As Object
Dim oStream As Object
Set WinHttpReq = CreateObject("Msxml2.ServerXMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile target, 1 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If

End Sub

Private Function RandomString(cb As Integer) As String

Randomize
Dim rgch As String
rgch = "abcdefghijklmnopqrstuvwxyz"
rgch = rgch & UCase(rgch) & "0123456789"

Dim i As Long
For i = 1 To cb
RandomString = RandomString & Mid$(rgch, Int(Rnd() * Len(rgch) + 1), 1)
Next

End Function

Private Function UnZip(PathToUnzipFileTo As Variant, FileNameToUnzip As Variant)

Dim objOApp As Object
Dim varFileNameFolder As Variant
varFileNameFolder = PathToUnzipFileTo
Set objOApp = CreateObject("Shell.Application")
objOApp.Namespace(FileNameToUnzip).CopyHere objOApp.Namespace(varFileNameFolder).items, 256

End Function

Answer 1

Dim mainFolder As String
Dim zipFolder As String
Dim destinationFolder As String
Dim oShell As Object
Dim oMainFolder As Object
Dim oDestinatioFolder As Object
Dim oZipFolder As Object
Dim oZipItems As Object

replace with

Dim mainFolder As Variant
Dim zipFolder As Variant
Dim destinationFolder As Variant
Dim oShell As Object
Dim oMainFolder As Object
Dim oDestinatioFolder As Object
Dim oZipFolder As Object
Dim oZipItems As Object

Answer 2

Comintem is right, you should edit your old question with the added code rather than post a near identical new question. Perhaps keep this question and delete the old one.

To answer your question, it looks as if you're passing your arguments in the wrong order to your UnZip function. Try changing the line to:

Call UnZip(targetFolder, targetFileZip)

Update

It's difficult to diagnose the issues as your objects are being created and its properties/methods being called all on one line. Judging by the nature of your questions it doesn't seem as though your VBA knowledge is particularly vast and that you're trying to construct a working solution by tying various pieces of web code together. It's not my position to judge that kind of approach but my advice would be, if you take this approach, to create your objects one at a time and call its methods one at a time. This will make it far easier to diagnose your code.

I've tried to rewrite elements of your code to show you how this could be done. It might be a bit overkill but at least it'll help you identify the precise location of any problems. Obviously change the folder names to your own.

Dim mainFolder As String
Dim zipFolder As String
Dim destinationFolder As String
Dim oShell As Object
Dim oMainFolder As Object
Dim oDestinatioFolder As Object
Dim oZipFolder As Object
Dim oZipItems As Object

'Define the folder names
mainFolder = "C:\Users\User\Downloads\SO\" 'change to your own folder name
zipFolder = "sqlite-shell-win32-x86-3071700.zip" 'an old sqlite download = change to your name
destinationFolder = Left(zipFolder, Len(zipFolder) - 4) 'name of zip folder minus the '.zip'

'Create the new destination folder
MkDir mainFolder & destinationFolder

'Acquire the folder items
'create the shell object
Set oShell = CreateObject("Shell.Application")
'create the main folder object as Folder3 item
Set oMainFolder = oShell.Namespace(CVar(mainFolder)) 'argument must be a variant
'create the destination folder object as Folder3 item
Set oDestinatioFolder = oMainFolder.Items.Item(CVar(destinationFolder & "\")).GetFolder
'create the zip folder object as Folder3
Set oZipFolder = oMainFolder.Items.Item(CVar(zipFolder)).GetFolder

'Extract the zip folder items and write to desination folder
oDestinatioFolder.CopyHere oZipFolder.Items, 256