How to UnZip while opening an connection

[Dojorgen]

Hello Excel gurus

The string below i have been using for ".log" files but now would like to use it for ".gz" which are zip files. How could i add to unzip during connecting?


Sub import_data()



Dim str_val2 As String
For x = 4 To 240


Worksheets("User Index").Select
str_val = Sheet1.Range("D3").Value
str_val2 = Cells(x + 1, 11)

Worksheets("RVI Scan").Select

Set sht_RVI2 = Sheets("User Index")
Dim j As Integer
For j = 1 To 1
Cells(j + 1, 16).Value = sht_RVI2.Cells(x + 1, 11)
Next j




ActiveWorkbook.Queries.Add Name:=str_val & "_vfei_" & str_val2 & " log", Formula:="let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""R:" & str_val & "_vfei_" & str_val2 & ".log.1""),[Delimiter="":"", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", Int64.Type}, {""Column3"", type text}, {""Column4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Sheets("RVI Scan").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Chr(34) & str_val & "_vfei_" & str_val2 & " log"";Extended Properties=""""", Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & str_val & "_vfei_" & str_val2 & " log]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_" & str_val & "_vfei_" & str_val2 & "_log"
On Error Resume Next
.Refresh BackgroundQuery:=False
If Err <> 0 Then
Err.Clear
End If
On Error GoTo 0

End With