Change VBA SaveAs CVS to XLSX upon user choice

[GuyGadois]

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.

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

 

[Joe4]

One important thing. NEVER open a CSV file in Excel to "review it".
Excel does NOT open the CSV file "as-is"; it peforms its own conversions on it (especially on date and numeric fields).

If you want to see what the CSV file TRULY looks like, do not open it in Excel. Use a Text Editor, like NotePad or WordPad.
This will show you the CSV file, as it truly exists, without changing any of the data formatting upon opening it.

 

[GuyGadois]

One important thing. NEVER open a CSV file in Excel to "review it".
Excel does NOT open the CSV file "as-is"; it peforms its own conversions on it (especially on date and numeric fields).

If you want to see what the CSV file TRULY looks like, do not open it in Excel. Use a Text Editor, like NotePad or WordPad.
This will show you the CSV file, as it truly exists, without changing any of the data formatting upon opening it.

@Joe4 Thanks for the advice. I will make sure to change that!

Do you have any feedback on how to adjust this VBA so the user can choose to export in XLSX or CSV format?

Cheers,

GG

 

[Joe4]

Just use an Input Box or a Message Box and prompt them for an answer, i.e. something structured like this:

    Dim typ As String
    
    typ = InputBox("Enter X for Excel or C for CSV", "Do you wish to save to an Excel or CSV file?")
    
    Select Case UCase(typ)
        Case "X"
            'Save as Excel file
        Case "C"
            'Save as CSV file
        Case Else
            MsgBox "You did not make a valid selection", vbOKOnly, "PLEASE TRY AGAIN!"
            Exit Sub
    End Select