• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

My apologies if this question is already answered

AGYANI

Member
Situation:My Excel file contains
WorkSheetName: Chart
Columns: A,B,C,D.......V

I want to export ALL the columns by Columns to a CSV file and close the file, this file then gets over-written by calling the same subroutine after few minute.

I am struggling to understand how to export all the columns in one write statement and close the file.

Regards
Agyani
 
Code:
Sub SaveAsCSV()
'
' Save worksheet as CSV File
'
'
  Dim fname as String
  fname = "My CSV file" 'Change as appropriate

  Sheets("Chart").Copy
  ActiveWorkbook.SaveAs _
  Filename:=fname, _
  FileFormat:=xlCSV, _
  CreateBackup:=False 

End Sub
 
Thank you Hui it does the copy and save, I just want the Columns A thru V in worksheet ("Chart") and new file keeps popping up do you "want to save as " message box, I want the this macros the overwrite the file already saved before.

I have macros which, does write a file, and overwrites the existing file

Code:
Public Sub CopyReturnNotepad()
Dim strPath As String: strPath = "F:\Users\Documents\" & Range("Chart!J1").Value & ".txt"
Dim intFF As Integer: intFF = FreeFile()
Open strPath For Output As #intFF
Print #intFF, Join(Application.Transpose(Range("Chart!J1:J" & Range("Chart!J" & Rows.Count).End(xlUp).Row).Value), vbCrLf)
Close #intFF
End Sub
This code does only copies one column (J), is there a way to "Join" the columns and write it to a output file, does not matter text or csv. If we can fine tune the
"Print #intFF, Join(Application.Transpose(Range("Chart!J1:J" & Range("Chart!J" & Rows.Count).End(xlUp).Row).Value), vbCrLf)" to add Column A thru Column V, that will be perfect
 
Last edited by a moderator:
Why not just add a line to clear the data in Columns V onwards

Code:
Sub SaveAsCSV()
'
' Save worksheet as CSV File
'
'
 Dim fname As String
  fname = "My CSV file" 'Change as appropriate

  Sheets("Sheet1").Copy
  Columns("V:XFD").ClearContents 'This is the new line
  ActiveWorkbook.SaveAs _
  Filename:=fname, _
  FileFormat:=xlCSV, _
  CreateBackup:=False

End Sub
 
Thank you Hui, appreciate your quick response, the code with the ("V.XFD)is doing the job copy and pasting to a new file that good.

The issue is it is opening the new file and showing the message to save as, I just want it to be overwritten to that file as I will be writing to this file in regular intervals.

I was able to write a single column using the "Join" command , any chance you could decode it to write multiple columns
 
Can you post the file with instructions as to exactly what you are trying to do?
 
Can you post the file with instructions as to exactly what you are trying to do?
Hui I am trying to make a copy of this worksheet , in to another file when I click a button in the main workbook. The button will have your code to copy the columns.
 
...The problem is the message box keeps popping up "Do you want to Save the file" I want it to overwrite the existing file, it is ok to delete the old file and create a new file.
 
When I run the code as is it overwrites the file?

Are you trying to save the whole data set or just some columns ?
 
Back
Top