VBA: Save As Script That the Opens, Tweaks New CSV File

[TheIronSaber]

I'm sure this is a common intro, but I'm new to VBA and am hping someone might be able to help me work through a bit of code.

Essentially, the jist of what I'm trying to do is automate the creation of some CSV files that I can then import into another program.

This is the code that I'm using:

<code>

Sub ExportCSV()

        Dim FlSv As Variant
        Dim MyFile As String
        Dim sh As Worksheet
        Dim MyFileName As String
        Dim DateString As String


        DateString = Format(Now(), "yyyy-mm-dd_hh_mm") '<~~ uses current time from computer clock down to the second
        MyFileName = DateString & "_" & "CSV_Data"


        Set sh = Sheets("000_CSV_Manager")
        sh.Copy
        FlSv = Application.GetSaveAsFilename(MyFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Where should we save this?")


     If FlSv = False Then GoTo UserCancel Else GoTo UserOK


UserCancel:             '<~~ this code is run if the user cancels out the file save dialog
        ActiveWorkbook.Close (False)
        MsgBox "Export canceled"
        Exit Sub


UserOK:                 '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
        MyFile = FlSv
        With ActiveWorkbook
            .SaveAs (MyFile), FileFormat:=xlCSV, CreateBackup:=False
            .Close False
        End With


    End Sub

</code>

As I'm sure is obvious, this script (which I've hooked up to a button) allows the user to prompt a Save As dialogue box, defaulting to the CSV (Comma delimited) format and the filename "DateString_CSV Data.csv". (It needs this functionality as users will needs to be able to customize their filename somewhat, as well as the location they're saving the CSVs to.)

What I would now like to do is to be able to add a portion of code at the end of this script that immediately opens this CSV file, makes one cell formatting change and then saves the CSV file again.

Rationale: I need one set of double quotes surrounding the fourth column of the CSV. If I include the double quotes in the xls file, when I save it as a csv, I'm suddenly stuck with an extra two double quotes for every original one. (This may be how most csv is supposed to be written out, but I need to have only one set for our csv reader to pick up the HTML coding within the quotes. This may be uncommon, but I've tested the program several times and it does work if I can achieve that formatting.) When I add double quotes to the CSV specifically (either manually or using the customized text format "@")and then save, those additional double quotes are not added to the final CSV file.

Secondly, I also need all the blank rows removed from the bottom of the csv that the formulas (despite having retrieved no values) have triggered. These, I've discovered, can be removed simply by opening the CSV in Excel (which no longer contains formulas and registers all blank rows beneath the last values as being empty) and simply saving again.

What I'm Hoping For: Essentially, what I'm hoping some can help me out with is adding a section of code to the end of the one above that will:
-open the saved CSV immediately after it is created
-add the quotes to either end of the fourth column ("D")
-save over the original CSV document without a prompt
-and close itself while leaving the original document open

Essentially, a sample line from the script is going to look like this:

Header1,Header2,Header3,Header4
text,text,text,"Text1
Text2"

While there may be methods outside of excel that could do the same thing, I'd very much like to try to make this work within excel.

If I have not clarified things well enough, just let me know and I will do my best to fill in any gaps.

Thank you for the help!

 

[LockeGarmin]

While there may be methods outside of excel that could do the same thing, I'd very much like to try to make this work within excel.

What you're describing won't be possible through Excel's "Save As..." csv functionality (as you've probably determined). The reason for this is that Excel is going to save it in such a way that will ensure the document can be opened by Excel in the future. Because of this the solution to your problem will need to be more customized than anything out of the box in Excel.

I think the code below will fit your needs. You'll need to modify for what is in your sheet by adding or subtracting columns. If the resulting file imports successfully into your system then great! But do be aware that this isn't going to factor in any other CSV rules (such as doubling up quotes in each element or putting quotes around an element if it contains a comma (or whatever the delimiter might be)).

Finally, just make sure that whenever you add a or remove a column in the "WriteRow" method you make the change in all three places (the method parameters, the IF statement, and in the WriteLine method of the stream object).

Hope that helps!

Sub WriteWorksheetToCsv(WS As Worksheet, Path As String)

Dim Stream As Object 'Scripting.TextStream '
Dim Row As Range

  Set Stream = OpenFileStream(Path)

  'Optional Headings added
  Call Stream.WriteLine("Heading1,Heading2,Heading3,Heading4,Heading5")

  For Each Row In WS.UsedRange.Rows
    Call WriteRow(Stream, Row.Columns("A").Value2, Row.Columns("B").Value2, Row.Columns("C").Value2, Row.Columns("D").Value2, Row.Columns("E").Value2)
  Next Row

  'Never forget to use this line as soon as you are done with the stream
  Stream.Close

End Sub

Sub WriteRow(Stream As Object, Value1 As String, Value2 As String, Value3 As String, Value4 As String, Value5 As String)

  If Not (Value1 = "" And Value2 = "" And Value3 = "" And Value4 = "" And Value5 = "") Then
    Call Stream.WriteLine(Value1 & "," & Value2 & "," & Value3 & ",""" & Value4 & """," & Value5)
  End If

End Sub

Function OpenFileStream(Path As String) As Object 'Scripting.TextStream '

Const ForWriting As Long = 2

  Set OpenFileStream = CreateObject("Scripting.FileSystemObject") _
                            .OpenTextFile(Path, IOMode:=ForWriting, Create:=True)
                          
End Function

Just use it in your code like this:

UserOK:
        Call WriteWorksheetToCsv([COLOR=#333333]ActiveWorkbook.Sheets(1), [/COLOR]FlSv)