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

 

[Skyybot]

Simply slip in the following before the save instruction. Change Table2 to your table name.

ActiveSheet.ListObjects("Table2").ShowHeaders = False

 

[Soly_90]

Simply slip in the following before the save instruction. Change Table2 to your table name.

ActiveSheet.ListObjects("Table2").ShowHeaders = False

I have tried it but it does not work with me .
I got the table from power query but I did not find a method to remove header names (column names) in power query so I would like to do it in macro vba before saving the csv file .


I have tried Activesheet.ListObjects(“results”).ShowHeaders = False
After the line of ws,Copy
But it does not work ,
Could you please edit this code .thanks a lot for help

Set Ws=Sheets(“results”)
ws.Copy
ActiveWorkbook.SaveAs file name :=fullpath, FileFormat:=xlCSV, CreateBackup:=False,Local:=True

 

[Soly_90]

It works .i found my mistake ,i have to write the name of table instead of name of my sheet .thanks a lot

 

[HighAndWilder]

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

Does this work for you?

You will need to change the lines indicated

Public Sub subCopyTableToCSVFile()
Dim Ws As Worksheet
Dim strPath As String

' Change this line.
Set Ws = Worksheets("TheTable")

strPath = ActiveWorkbook.Path

Ws.Copy

With ActiveSheet
With .ListObjects(1)
.Unlist
End With
.Rows(1).Delete
End With

' Change this line.
ActiveWorkbook.SaveAs Filename:=strPath & "\" & Format(Now(), "HHMMSS"), _
FileFormat:=xlCSV, _
CreateBackup:=False, _
Local:=True

End Sub

 

[Soly_90]

Does this work for you?

You will need to change the lines indicated

Public Sub subCopyTableToCSVFile()
Dim Ws As Worksheet
Dim strPath As String

' Change this line.
Set Ws = Worksheets("TheTable")

strPath = ActiveWorkbook.Path

Ws.Copy

With ActiveSheet
With .ListObjects(1)
.Unlist
End With
.Rows(1).Delete
End With

' Change this line.
ActiveWorkbook.SaveAs Filename:=strPath & "\" & Format(Now(), "HHMMSS"), _
FileFormat:=xlCSV, _
CreateBackup:=False, _
Local:=True

End Sub

' Change this line.
Set Ws = Worksheets("TheTable")

Thanks a lot for your script .
I have noticed now that I have to mention the table not the whole sheet .
But when I mention the name of table ,I got error .(debug)
How could I display ,what exactly the mistake here
I tried set ws =Worksheets(“name of table”)
And I have tried set ws =Sheets(“name of table”)
Both of them does not let the code to run .
It mention a mistake at this line .
But when I use
Set ws= Sheets(“results”)
name of sheet ,it works
I understood that I am saving the whole sheet not only the specific table .but I am lucky ,I have only one table .
But it is still interesting to solve the problem of this line .

 

[HighAndWilder]

' Change this line.
Set Ws = Worksheets("TheTable")

Thanks a lot for your script .
I have noticed now that I have to mention the table not the whole sheet .
But when I mention the name of table ,I got error .(debug)
How could I display ,what exactly the mistake here
I tried set ws =Worksheets(“name of table”)
And I have tried set ws =Sheets(“name of table”)
Both of them does not let the code to run .
It mention a mistake at this line .
But when I use
Set ws= Sheets(“results”)
name of sheet ,it works
I understood that I am saving the whole sheet not only the specific table .but I am lucky ,I have only one table .
But it is still interesting to solve the problem of this line .

Try this. More explanation.

Read the notes. No need to state the table name.

Public Sub subCopyTableToCSVFile()
Dim Ws As Worksheet
Dim strPath As String
Dim intRow As Integer

  ActiveWorkbook.Save

  ThisWorkbook.Activate

  ' Change this line.
  ' This line assigns a worksheet object to the
  ' worksheet object variable Ws.
  ' Substitute 'TheTable' with you worksheet name, not the table name.
  Set Ws = Worksheets("TheTable")
  
  Ws.Activate
  
  ' Change this to where you want the CSV file saved.
  ' Omit the '\' at the end.
  strPath = ActiveWorkbook.Path
  
  ' Thsi copies the worksheet to a new workbook with just one worksheet.
  Ws.Copy
  
  ' The new workbook and single sheet with the table is now the active sheet.
  With ActiveSheet
  
    ' This makes reference to the FIRST table on the new worksheet
    ' so the table name is NOT required.
    With .ListObjects(1)
      ' Convert the table to a simple range.
      .Unlist
    End With
    
    ' Delete the first row of the worksheet.
    .Rows(1).Delete
       
  End With
 
  With ActiveWorkbook
    
    ' Change this line to state the CSV filename.
    ' This bit "\" & Format(Now(), "HHMMSS")
    .SaveAs Filename:=strPath & "\" & Format(Now(), "HHMMSS"), _
      FileFormat:=xlCSV, _
      CreateBackup:=False, _
      Local:=True

    .Close False
  
  End With

End Sub

 

[Soly_90]

Try this. More explanation.

Read the notes. No need to state the table name.

Public Sub subCopyTableToCSVFile()
Dim Ws As Worksheet
Dim strPath As String
Dim intRow As Integer

  ActiveWorkbook.Save

  ThisWorkbook.Activate

  ' Change this line.
  ' This line assigns a worksheet object to the
  ' worksheet object variable Ws.
  ' Substitute 'TheTable' with you worksheet name, not the table name.
  Set Ws = Worksheets("TheTable")
 
  Ws.Activate
 
  ' Change this to where you want the CSV file saved.
  ' Omit the '\' at the end.
  strPath = ActiveWorkbook.Path
 
  ' Thsi copies the worksheet to a new workbook with just one worksheet.
  Ws.Copy
 
  ' The new workbook and single sheet with the table is now the active sheet.
  With ActiveSheet
 
    ' This makes reference to the FIRST table on the new worksheet
    ' so the table name is NOT required.
    With .ListObjects(1)
      ' Convert the table to a simple range.
      .Unlist
    End With
   
    ' Delete the first row of the worksheet.
    .Rows(1).Delete
      
  End With
 
  With ActiveWorkbook
   
    ' Change this line to state the CSV filename.
    ' This bit "\" & Format(Now(), "HHMMSS")
    .SaveAs Filename:=strPath & "\" & Format(Now(), "HHMMSS"), _
      FileFormat:=xlCSV, _
      CreateBackup:=False, _
      Local:=True

    .Close False
 
  End With

End Sub

Did it works with you ?
I have tried it but it did not work .
It is okey no problem ,thanks .
Which code that i have to use to overwrite the file , if it has the same name .

 

[HighAndWilder]

Did it works with you ?
I have tried it but it did not work .
It is okey no problem ,thanks .
Which code that i have to use to overwrite the file , if it has the same name .

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

 

[Soly_90]

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

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