How to I insert cell values (not a range) from an Excel sheet to an existing csv file using VBA

Question

I have successfully written code to insert cell values from a worksheet into an SQL table using loop. There are unused columns and unused rows and incorrect headers in the original sheet which have been managed by the VBA code Now the database is being shut down and we want to use a csv file to upload the data to another database. What code is used to open the csv file and insert the data cell by cell?

Answer 1

1. Iterate through all active worksheets.
2. For each worksheet, iterate through all used rows.
3. For each row, iterate through all columns.
4. Build line string by wrapping each cell values in double quotes and delimiting with commas.
5. Write line string to csv file.

Const sFilePath = "C:\test\myfile.csv"
Const strDelim = ","
Sub CreateCSV_Output()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim X
    Dim lRow As Long
    Dim lCol As Long
    Dim strTmp As String
    Dim lFnum As Long

    lFnum = FreeFile
    Open sFilePath For Output As lFnum

    For Each ws In ActiveWorkbook.Worksheets
        'test that sheet has been used
        Set rng1 = ws.UsedRange
        If Not rng1 Is Nothing Then
            'only multi-cell ranges can be written to a 2D array
            If rng1.Cells.Count > 1 Then
                X = ws.UsedRange.Value2
                'The code TRANSPOSES COLUMNS AND ROWS by writing strings column by column
                For lCol = 1 To UBound(X, 2)
                    'write initial value outside the loop
                     strTmp = IIf(InStr(X(1, lCol), strDelim) > 0, """" & X(1, lCol) & """", X(1, lCol))
                    For lRow = 2 To UBound(X, 1)
                        'concatenate long string & (short string with short string)
                        strTmp = strTmp & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
                    Next lRow
                    'write each line to CSV
                    Print #lFnum, strTmp
                Next lCol
            Else
                Print #lFnum, IIf(InStr(ws.UsedRange.Value, strDelim) > 0, """" & ws.UsedRange.Value & """", ws.UsedRange.Value)
            End If
        End If
    Next ws

    Close lFnum
    MsgBox "Done!", vbOKOnly

End Sub

Ref