[Solved] Extract gz and convert csv to xlsx

by


While I agree the OP does not appear to have done their fair share of figuring this out (how hard is Google?), I know someone else will be looking in the future. Posting information will help them.

@OP, I’m not going to do all your file handling work for you but here is the basic code to convert from CSV to XLSX which is probably the least common part of the question posed.

Option Explicit

Dim strCSVfile, strExcelFile, FSO
Dim objWorkbook, objWorksheet1

set FSO = CreateObject("Scripting.FileSystemObject")

strCSVfile = "C:\temp\Excel Test\myFile.csv"
strExcelFile = "C:\temp\Excel Test\myFile.xlsb"  'this was changed

if FSO.FileExists(strCSVfile) then
   Dim objExcel
   Set objExcel = CreateObject("Excel.Application")

   objExcel.Visible = False
   objExcel.displayalerts=false

   'Import CSV into Spreadsheet
   Set objWorkbook = objExcel.Workbooks.open(strCSVfile)
   Set objWorksheet1 = objWorkbook.Worksheets(1)

   'Save workbook (51 = 2010 format) 
   'Formats ref https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
   'Parameters ref https://msdn.microsoft.com/en-us/library/office/ff841185.aspx 
   objWorksheet1.SaveAs strExcelFile, 50  'this was changed
   objExcel.Quit()
else
   msgbox "File Note found"
end if

Edit: BTW, OP@… post your code and more people will help you. If you update your question with the code you have written for unzipping and file handling, I will help with that part.

Edit: Updated for .xlsb format output. Lines changed marked as such.

1

solved Extract gz and convert csv to xlsx