Change VBA SaveAs CVS to XLSX upon user choice

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
344
Office Version
  1. 2019
Platform
  1. Windows
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.

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)
DateSymbolTarget 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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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
 
Upvote 0
Just use an Input Box or a Message Box and prompt them for an answer, i.e. something structured like this:
VBA Code:
    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
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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
Back
Top