Saving as .csv with parmaeters

[Kaizer]

Hey guys,

In Sheet1 I have AccountNames in ColumnA and values in ColumnB (about 8000 lines). Most of the values are zero. In Sheet2 I have EntityName="Entity1" in Cell("A1") and Month="Apr-05" (i.e. 4-th month in the year) in Cel("A2"). I need to save the .csv file with non-zero values as following:

Line1: Entity1
Line2: 4
Line3: 4
Line4: Actual
Line5: Account1,125
Line6: Account15,-24
...
Can you help with the code when it saves the file in the format I need with non-zero values?

Thank you in advance.

[mvidas]

Hi Kaizer,

I'd like to clarify a little bit what you need. You're looking to take your spreadsheet, run a macro, and produce a .csv file using
line1: sheet2!A1
line2: month(sheet2!A2)
line3: month(sheet2!A2)
line4: Actual
line5: First account name from sheet1 column A, first account value from sheet 1 column b
line6: second account name from sheet 1 col A, second account value from sheet 1 col b
line7: third...
etc.

But only when the account value is non-zero, correct?

I'll get started on the code for this, and make changes to it when I hear from you.
Matt

[Kaizer]

Hi Matt,

What you described is exactly what I want to get at the end.

Thank you.

Kaizer

[mvidas]

Great, give this a try:


Sub KaizerExportCSV()
Dim vFF As Long, vSaveFile As String
Dim AllAcctData, ExpAcct() As String, ExpCount As Long, i As Long
Dim RG As Range
'Account data range, starting in row 2 to account for headers
Set RG = Intersect(Sheets(1).Range("A2:B65536"), Sheets(1).UsedRange)
If RG Is Nothing Then Exit Sub 'no data on sheet 1
AllAcctData = RG.Value
'Create array of data to export, using only non-zero and non-blank account values
ExpCount = 0
For i = LBound(AllAcctData, 1) To UBound(AllAcctData, 1)
If AllAcctData(i, 2) <> 0 And AllAcctData(i, 2) <> "" Then
ReDim Preserve ExpAcct(ExpCount)
ExpAcct(ExpCount) = AllAcctData(i, 1) & "," & AllAcctData(i, 2)
ExpCount = ExpCount + 1
End If
Next i
If ExpCount = 0 Then Exit Sub 'No non-zero values
'Get save file name
vSaveFile = Application.GetSaveAsFilename(InitialFilename:=Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & ".csv", filefilter:="CSV Files,*.csv,All Files,*.*")
If LCase(vSaveFile) = "false" Then Exit Sub 'user hit cancel
'Export data
vFF = FreeFile
Open vSaveFile For Output As #vFF
Print #vFF, Sheets(2).Range("A1").Text 'line 1
Print #vFF, CStr(Month(Sheets(2).Range("A2"))) 'line 2
Print #vFF, CStr(Month(Sheets(2).Range("A2"))) 'line 3
Print #vFF, "Actual" 'line 4
For i = 0 To ExpCount - 2
Print #vFF, ExpAcct(i) 'lines 5 through second last account
Next i
Print #vFF, ExpAcct(ExpCount - 1); 'last account ends in ; to prevent extra line feed
Close #vFF
End Sub

Matt

[Kaizer]

Cool,

Thank you Matt. That's what I wanted.

Kaizer

[mvidas]

Glad I could help! Let me know if it needs any tweaking or anything