VBA Save CSV with headers and data only from one column as result of formula

Status
Not open for further replies.

Jorgi

New Member
Joined
Jul 7, 2021
Messages
41
Office Version
  1. 2019
Platform
  1. Windows
Hello, Could you be so kind and help me with VBA, please? I would like to save the below example as CSV via marco. There will be only data in column C as result of formula but the header data needs to be in separated columns A B C D on csv also if there will be no data e.g. C5 the result of a formula will be "" and will be great if will be possible not to include "" on the csv/txt. The CSV will also need to be saved in the same folder what xlsx (the working on workbook). I have managed to get macro to save only column C ( Header C1 and data from C2,C3,C4 and C5 with "" which is not ideal) with data but without headers from A1 B1 and D1. Thank you so much for you help and guidance.

1636182719531.png


Sub saveABCDToCSV()

Dim myCSVFileName As String
Dim myWB As Workbook
Dim tempWB As Workbook
Dim rngToSave As Range

Application.DisplayAlerts = False
On Error GoTo err

Set myWB = ThisWorkbook
myCSVFileName = myWB.Path & "\" & "ABCD-" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & ".csv"

Set rngToSave = Range("C1:C5")
rngToSave.Copy

Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial xlPasteValues
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
MsgBox "CSV created"
End With
err:
Application.DisplayAlerts = True
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
199
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
Sub SaveAsCSV()
  Dim C, i, myCSVFileName
  C = Cells(1, 3).Resize(Cells(1, 3).End(xlDown).Row, 1) 'read column into memory
  myCSVFileName = ActiveWorkbook.Path & "\" & "ABCD-" & Format(Now, "dd-MMM-yyyy") & ".csv"
  Open myCSVFileName For Output As #1
  For i = LBound(C) To UBound(C) 'write line by line
    Print #1, ",," & C(i, 1) & ","
  Next i
  Close #1
End Sub
 

Jorgi

New Member
Joined
Jul 7, 2021
Messages
41
Office Version
  1. 2019
Platform
  1. Windows
Try this

VBA Code:
Sub SaveAsCSV()
  Dim C, i, myCSVFileName
  C = Cells(1, 3).Resize(Cells(1, 3).End(xlDown).Row, 1) 'read column into memory
  myCSVFileName = ActiveWorkbook.Path & "\" & "ABCD-" & Format(Now, "dd-MMM-yyyy") & ".csv"
  Open myCSVFileName For Output As #1
  For i = LBound(C) To UBound(C) 'write line by line
    Print #1, ",," & C(i, 1) & ","
  Next i
  Close #1
End Sub
Thank you Dermot for the code. I probably did something wrong. I'm not very good with VBA still learning process. Thank you for any hints :)
1636186995030.png
 
Status
Not open for further replies.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,573
Messages
5,838,170
Members
430,533
Latest member
paublojobino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top