I have the following code but I wanted to see if it was possible to let the user choose which way they want to save this file being generated, CSV or XLXS. In addition, I would like the values in Column A has to be formatted as MM/DD/YYYY and column C needs to be in number format 000.0000 (4 decimal places) but the headers formatted as General.
The output currently looks like this (as a CSV)
Note how the date isn't 01/21/2021 and the Target weight goes to too many decimal places so it won't work.
Any ideas how to do this?
Cheers,
Guy
VBA Code:
Public Sub SaveAsYC()
Dim sFileName As String
Dim intFH As Integer
Dim aRange As Range
Dim iLastColumn As Integer
Dim iLastRow As Long '<- added
Dim oCell As Range
Dim iRec As Long
Application.ScreenUpdating = False
Worksheets("Exports").Activate
Set aRange = Range("B3:D200")
iLastColumn = aRange.Column + aRange.Columns.Count - 1
iLastRow = aRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row '<- added
Set aRange = Range("B3:D" & iLastRow) '<- added
'hardcode path and file name
sFileName = "C:\Models for Upload\" & Range("upload_name").Value & Format(Date, "YYMMDD") & ".csv" '<-- CHANGE
intFH = FreeFile()
Open sFileName For Output As intFH
iRec = 0
For Each oCell In aRange
If oCell.Column = iLastColumn Then
Print #intFH, oCell.Value
iRec = iRec + 1
Else
Print #intFH, oCell.Value; ",";
End If
Next oCell
Close intFH
MsgBox "Finished: " & CStr(iRec) & " records written to " _
& sFileName & Space(10), vbOKOnly + vbInformation
'open file to review it
Workbooks.Open sFileName '<- changed
Worksheets("Portfolios").Activate
Application.ScreenUpdating = True
End Sub
The output currently looks like this (as a CSV)
Date | Symbol | Target Weight |
1/21/2021 | AAPL | 0.0094 |
1/21/2021 | ACN | 0.005222 |
1/21/2021 | ADP | 0.006 |
1/21/2021 | BX | 0.0068 |
1/21/2021 | C | 0.0042 |
Note how the date isn't 01/21/2021 and the Target weight goes to too many decimal places so it won't work.
Any ideas how to do this?
Cheers,
Guy