src_file = "C:\Users\operator\Desktop\transpon\src\16.xls"
dest_file = "C:\Users\operator\Desktop\transpon\src\load.csv"

Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open(src_file)
Set objSheet = objWorkbook.Sheets(1)
    
   Set colvalue = objSheet.Cells.Find("*",objSheet.Range("A1"),xlValues,xlPart,xlByColumns,xlPrevious,False,False)
   c = colvalue.Column
   MsgBox (c)

Set FSO = CreateObject("Scripting.FileSystemObject")
src_file = "~\1.xls"
dest_file = "~\l.csv"

Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.Workbooks
objApp.Visible = True
Set objWorkbook = objWbs.Open(src_file)
Set objSheet = objWorkbook.Sheets(1)
name1 = objSheet.Name

Dim xlValues, xlPart, xlByColumns, xlPrevious, xlByRows
xlValues = -4163
xlPart = 2
xlByColumns = 2
xlPrevious = 2
xlByRows = 1

   Set colvalue = objSheet.Cells.Find("*", objSheet.Range("A1"), xlValues, xlPart, xlByColumns, xlPrevious, False, False)
   c = colvalue.Column
   
   Set rowvalue = objSheet.Cells.Find("*", objSheet.Range("A1"), xlValues, xlPart, xlByRows, xlPrevious, False, False)
   r = rowvalue.Row
   
   objSheet.Range(objSheet.Cells(1, 1), objSheet.Cells(r, c)).Select

   objWorkbook.Sheets.Add , objWorkbook.Sheets(objWorkbook.Sheets.Count)
   objWorkbook.Sheets(objWorkbook.Sheets.Count).Name = "P"
    
     For n = 0 To (r - 2)
         For i  = 1 To c
             objWorkbook.Sheets("P").Cells(i + n * c, 2) = objWorkbook.Sheets(name1).Cells(1, i)
             objWorkbook.Sheets("P").Cells(i + n * c, 1) = n + 1
         Next 
         For i = 1 To c
            objWorkbook.Sheets("P").Cells(i + n * c, 3) = objWorkbook.Sheets(name1).Cells(n + 2, i)
         Next
    Next 

    
 
objWorkbook.Sheets("Pr").Select
objWorkbook.SaveAs dest_file, 6 ',,,false,true,,,,,, -4143
objWorkbook.Close False
objApp.Quit