Remove the header (column names) in vba before saving the sheet in csv file

[Soly_90]

Hello

I am saving the table from specific sheet as csv file by macro vba .
But I am still looking to saving the table without headers column name .
Is there any easy way for that ?
Thanks for help

 

[Soly_90]

Sub SaveSheetAsCSV()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim fullpath As String
 
    ' Read the folder path and file name from specified cells
    folderPath = Range("R29").Value
    fileName = Range("R30").Value
 
    ' Ensure the folder path ends with a backslash
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    dt = Format(CStr(Now), "dd.mm.yyyy_hh.mm")
    ' Set the full path for the CSV file
    fullpath = folderPath & dt & "_" & fileName & ".csv"
 
    ' Save the active sheet as a CSV file
    Set ws = Sheets("output")
    ws.Copy
    ActiveSheet.ListObjects("results").ShowHeaders = False
    ActiveWorkbook.SaveAs fileName:=fullpath, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    ActiveWorkbook.Close SaveChanges:=False
 
 
End Sub

This is above my code and it works well

I am using a button at specific sheet but it implements this code on another sheet called output .
output is my sheet name
results is my table name

But you have suggestions that I have to use

Set ws = Sheets("output")

My table name instead of sheet name but I have tested it and it does not work with me .

Also could you please add the over write file method to save the csv file . I am not sure where should I add lines in my code ?
and edit the code with table name .

Thanks a lot

Can you post your code using the VBA Quick Wrap selection on the Mr Excel ribbon?

What filename are you giving the file?

My code used the time as that changes every second but only to demonstrate that it works to you.

Add these first three lines of code above the With ActiveWorkbook line.

This will delete the file before it is recreated.

 On Error Resume Next
  Kill (strPath & "\" & Format(Now(), "HHMMSS"))
  On Error GoTo 0
   
  With ActiveWorkbook

Could you please reply ? I have added my code down if you could edit it to specific table name of the sheet ? If I have two table in the same sheet ,how could you save both of them in seperate csv file ?
How to let the code over write the file ?

 

[HighAndWilder]

Could you please reply ? I have added my code down if you could edit it to specific table name of the sheet ? If I have two table in the same sheet ,how could you save both of them in seperate csv file ?
How to let the code over write the file ?

A bit of project creep here.

So.... Two tables now in the 'output' worksheet.

Each needs to be saved as a CSV file without the column headers.

I need to know where the CSV path and filename comes from for each of the worksheets.

I also need to know the name of each of the tables.

I also need to know the position of each of the tables in the worksheet.

 

[Soly_90]

A bit of project creep here.

So.... Two tables now in the 'output' worksheet.

Each needs to be saved as a CSV file without the column headers.

I need to know where the CSV path and filename comes from for each of the worksheets.

I also need to know the name of each of the tables.

I also need to know the position of each of the tables in the worksheet

I have written in code ,file name and folder path comes from two cell in specific worksheet (input ) but you can consider any file name and I will change it later .
Table1 ,Table 2
The position of tables in output worksheet
Table 1 at Row 1 and Table 2 in row 10 .
In fact , my code works for saving one table .
But i have to develop it to save two tables ,in csv file .
Let me know , how could you edit set ws to work with tables .I did it but it did not work .

 

[HighAndWilder]

I have written in code ,file name and folder path comes from two cell in specific worksheet (input ) but you can consider any file name and I will change it later .
Table1 ,Table 2
The position of tables in output worksheet
Table 1 at Row 1 and Table 2 in row 10 .
In fact , my code works for saving one table .
But i have to develop it to save two tables ,in csv file .
Let me know , how could you edit set ws to work with tables .I did it but it did not work .

A different approach.
Set the folder and filename as you wish but for now it comes from values in cells.
It's not a good idea to have periods in file names.

Run the subCreateCSVFilesFromTables procedure.

Public Sub subCreateCSVFilesFromTables()
Dim folderPath As String
Dim fileName As String
Dim Ws As Worksheet
Dim fullpath As String
Dim dt As String

On Error GoTo Err_Handler

  ActiveWorkbook.Save
  
  Set Ws = Sheets("output")
  
  ' Table1
  
  folderPath = Ws.Range("R29").Value
  
  fileName = Ws.Range("R30").Value
  
  If Right(folderPath, 1) <> "\" Then
    folderPath = folderPath & "\"
  End If
  
  dt = Format(CStr(Now), "ddmmyyyy_hhmmss")
  
  fullpath = folderPath & dt & "_" & fileName & ".csv"
  
  On Error Resume Next
  Kill (fullpath)
  On Error GoTo 0

  Call SubWriteCSVFils(Ws.ListObjects("Table1"), fullpath)
  
  ' Table2
  
  folderPath = Ws.Range("R31").Value
  
  fileName = Ws.Range("R32").Value
  
  If Right(folderPath, 1) <> "\" Then
    folderPath = folderPath & "\"
  End If
  
  dt = Format(CStr(Now), "ddmmyyyy_hhmmss")
  
  fullpath = folderPath & dt & "_" & fileName & ".csv"
  
  On Error Resume Next
  Kill (fullpath)
  On Error GoTo 0

  Call SubWriteCSVFils(Ws.ListObjects("Table2"), fullpath)

Exit_Handler:

  Exit Sub
  
Err_Handler:

  MsgBox Err.Number & " " & Err.Description

  Resume Exit_Handler

End Sub

Public Sub SubWriteCSVFils(tbl As ListObject, strFileName As String)
Dim csvFilePath As String
Dim fNum As Integer
Dim tblArr() As Variant
Dim rowArr
Dim csvVal
Dim i As Integer

On Error GoTo Err_Handler
  
  tblArr = tbl.DataBodyRange.Value
  
  fNum = FreeFile()
  
  Open strFileName For Output As #fNum
  
  For i = LBound(tblArr) To UBound(tblArr)
    rowArr = Application.Index(tblArr, i, 0)
    csvVal = VBA.Join(rowArr, ",")
    Print #1, csvVal
  Next
  
  Close #fNum
  
  Set rowArr = Nothing
  Set csvVal = Nothing

Exit_Handler:

  Exit Sub
  
Err_Handler:

  MsgBox Err.Number & " " & Err.Description

  Resume Exit_Handler

End Sub

 

[Soly_90]

A different approach.
Set the folder and filename as you wish but for now it comes from values in cells.
It's not a good idea to have periods in file names.

Run the subCreateCSVFilesFromTables procedure.

Public Sub subCreateCSVFilesFromTables()
Dim folderPath As String
Dim fileName As String
Dim Ws As Worksheet
Dim fullpath As String
Dim dt As String

On Error GoTo Err_Handler

  ActiveWorkbook.Save
 
  Set Ws = Sheets("output")
 
  ' Table1
 
  folderPath = Ws.Range("R29").Value
 
  fileName = Ws.Range("R30").Value
 
  If Right(folderPath, 1) <> "\" Then
    folderPath = folderPath & "\"
  End If
 
  dt = Format(CStr(Now), "ddmmyyyy_hhmmss")
 
  fullpath = folderPath & dt & "_" & fileName & ".csv"
 
  On Error Resume Next
  Kill (fullpath)
  On Error GoTo 0

  Call SubWriteCSVFils(Ws.ListObjects("Table1"), fullpath)
 
  ' Table2
 
  folderPath = Ws.Range("R31").Value
 
  fileName = Ws.Range("R32").Value
 
  If Right(folderPath, 1) <> "\" Then
    folderPath = folderPath & "\"
  End If
 
  dt = Format(CStr(Now), "ddmmyyyy_hhmmss")
 
  fullpath = folderPath & dt & "_" & fileName & ".csv"
 
  On Error Resume Next
  Kill (fullpath)
  On Error GoTo 0

  Call SubWriteCSVFils(Ws.ListObjects("Table2"), fullpath)

Exit_Handler:

  Exit Sub
 
Err_Handler:

  MsgBox Err.Number & " " & Err.Description

  Resume Exit_Handler

End Sub

Public Sub SubWriteCSVFils(tbl As ListObject, strFileName As String)
Dim csvFilePath As String
Dim fNum As Integer
Dim tblArr() As Variant
Dim rowArr
Dim csvVal
Dim i As Integer

On Error GoTo Err_Handler
 
  tblArr = tbl.DataBodyRange.Value
 
  fNum = FreeFile()
 
  Open strFileName For Output As #fNum
 
  For i = LBound(tblArr) To UBound(tblArr)
    rowArr = Application.Index(tblArr, i, 0)
    csvVal = VBA.Join(rowArr, ",")
    Print #1, csvVal
  Next
 
  Close #fNum
 
  Set rowArr = Nothing
  Set csvVal = Nothing

Exit_Handler:

  Exit Sub
 
Err_Handler:

  MsgBox Err.Number & " " & Err.Description

  Resume Exit_Handler

End Sub

Thanks a lot ,I will test it