Thread: Need help to tweak a macro so that it will format a plain csv file into a nice table

Hello, I have a problem.
Our system automatically generates reports (tables) in the csv format only. It is very plain and ugly looking and does not have any formatting.

I have used the macro recorder to record a macro so that when i click "Ctrl" and "K" it will format the data (several columns and several rows with the first row as the header) into a pre-program table in Excel.

Here is the codes.

I want to ask for help to tweak this code so that:-

1) it will work for the current active (opened) CSV file regardless of the number of rows and the number of columns. I realised that when i record this macro, it is fixed rows and fix columns and if I use this macro for another csv file where there is less/more rows and less/more columns, the formatting of the table will not carry to these other rows


2) I also need this csv file to be automatically saved into the latest Excel file format so that the formatting can be retained the next time this file is opened.

Sub FORMATTABLE()
'
' FORMATTABLE Macro
' This is to format csv into tables
'
' Keyboard Shortcut: Ctrl+k
'
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$BG$68"), , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight6"
    Range("F11").Select
End Sub

It may help to post a sample workbook with your code in place.

Regards, TMS