PDA

View Full Version : [SOLVED] getting the error "91 object variable or with block variable not set"



Cinema
10-21-2016, 01:45 AM
Hi,

I tried to unzip zip files by Looping through the subfolders. I am getting the error 91 in Line "oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(My_FILE).items"
Can someone help???








Sub TestRun()
Dim My_DESTINATION As String
Dim My_LOCATION As String
My_DESTINATION = Range("B1").Value
For My_ROWS = 5 To Range("A" & Rows.Count).End(xlUp).Row
My_LOCATION = My_DESTINATION & Range("A" & My_ROWS).Value
My_FILE = My_FILE = My_DESTINATION & Range("A" & My_ROWS).Value & "\" & Range("B" & My_ROWS).Value
Call UnZip(My_FILE, My_DESTINATION)
Next My_ROWS
End Sub
Sub UnZip(ByVal My_FILE As String, My_DESTINATION As String)

Dim oApp As Object

Dim FileNameFolder As Variant


If Right(My_DESTINATION, 1) <> Application.PathSeparator Then
My_DESTINATION = My_DESTINATION & Application.PathSeparator
End If

FileNameFolder = My_DESTINATION

Set oApp = CreateObject("Shell.Application")

oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(My_FILE).items




End Sub

mancubus
10-21-2016, 02:07 AM
typo or original code?

My_FILE = My_FILE = My_DESTINATION & Range("A" & My_ROWS).Value & "\" & Range("B" & My_ROWS).Value

mancubus
10-21-2016, 02:16 AM
Call UnZip(My_FILE, My_DESTINATION)

My_FILE should be a valid zipped file name and My_DESTINATION should be a valid folder name.

debug your code to see which values your variables take as the code proceeds.

Cinema
10-21-2016, 02:39 AM
Hi mancubus,

that's right :
My_FILE = My_DESTINATION & Range("A" & My_ROWS).Value & "\" & Range("B" & My_ROWS).Value



I want to unzip zip files that are in the same Folder but in different subfolders. The path of the Folder is in Range(B1) and the names of the subfolders are listed in Range("A5:A" & LastRow). Next to the Names of the subfolders are the file names of the zip files. For example A5 has the entry \SubfolderTest and B5 has the entry test.zip

Now my aim is to unzip all zip files by Looping through the subfolders and then extract them all in the Mainfolder where the subfolders are.

mancubus
10-21-2016, 04:45 AM
?


Sub vbax_57506_UnZip_Files()

Dim Parent_Folder As String, Sub_Folder As String, Zip_File As String
Dim i As Long

Parent_Folder = Range("B1").Value

For i = 5 To Range("A" & Rows.Count).End(xlUp).Row
Sub_Folder = Range("B1").Value & "\" & Range("A" & i).Value
Zip_File = Sub_Folder & "\" & Range("B" & i).Value
Call UnZipFiles(Parent_Folder, Zip_File)
Next i

End Sub




Sub UnZipFiles(ByVal Dest_Folder As String, Zip_File As String)

With CreateObject("Shell.Application")
.Namespace(Dest_Folder).CopyHere .Namespace(Zip_File).Items
End With

End Sub

mancubus
10-21-2016, 04:46 AM
or, with no variables



Sub vbax_57506_UnZip_Files_short()

Dim i As Long

For i = 5 To Range("A" & Rows.Count).End(xlUp).Row
Call UnZipFiles(Range("B1").Value, Range("B1").Value & "\" & Range("A" & i).Value & "\" & Range("B" & i).Value)
Next i

End Sub




Sub UnZipFiles(ByVal Dest_Folder As String, Zip_File As String)

With CreateObject("Shell.Application")
.Namespace(Dest_Folder).CopyHere .Namespace(Zip_File).Items
End With

End Sub

mancubus
10-21-2016, 04:53 AM
or, without calling another sub



Sub vbax_57506_UnZip_Files_shorter()

Dim i As Long

For i = 5 To Range("A" & Rows.Count).End(xlUp).Row
With CreateObject("Shell.Application")
.Namespace(Range("B1").Value).CopyHere .Namespace(Range("B1").Value & "\" & Range("A" & i).Value & "\" & Range("B" & i).Value).Items
End With
Next i

End Sub

Cinema
10-26-2016, 04:18 AM
Thank you Mancubus !!!!