Consulting

Results 1 to 2 of 2

Thread: Converting Excel File to CSV

  1. #1

    Converting Excel File to CSV

    Hi VBExpress Nation!

    I am hopeful if a second pair eyes will help out: I am trying to capture cells in my spreadsheet that may have commas and I need to keep the content of that cell together. My code is below

    Sub Create CSVDim fname as strong
    Dim LText as String
    Dim CSText as Variant
    Dim myRange as Range, I, j
    fname = ThisWorkbook.Path & “ExportData” & “.csv”
     Open fname for Output as #1
    With Active Sheet
    Set myRange = .Range(“Report”)
    For I= 1 to myRange.Rows.Count
    For j = 1 to myRange.Columns.Count
    If Instr(myRng.Cells(i,j),”,”)> 0 then
    CSText = “‘“ & myRange.Cells(I,j) & “‘“
    LText = LText & CSText
    CSText = “”
    else 
    LText = IIf(j=1, “”,LText & “,”) & myRange.Cells(I,j)
    End If
    Next j
    print #1, LText
    Next I
    End With
    Msgbox “All complete!”, vbInformation, “Data to CSV Format”
    End Sub
    Thanks in advance to everyone for their help.

    Slice

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    326
    Location
    Hi Slicemahn!
    Sub Create_CSV()
    Dim fname As String
    Dim LText As String
    Dim CSText As Variant
    Dim myRange As Range, I, j
    fname = ThisWorkbook.path & "\ExportData" & ".csv"
    Open fname For Output As #1
    With ActiveSheet
        Set myRange = .Range("Report")
        For I = 1 To myRange.Rows.Count
            For j = 1 To myRange.Columns.Count
                If InStr(myRange.Cells(I, j), ",") > 0 Then
                    CSText = "" & myRange.Cells(I, j) & ""
                    LText = LText & CSText
                    CSText = ""
                Else
                    LText = IIf(j = 1, "", LText & ",") & myRange.Cells(I, j)
                End If
            Next j
            Print #1, LText
        Next I
    End With
    Close #1
    MsgBox "All complete!", vbInformation, "Data to CSV Format"
    End Sub
    Last edited by 大灰狼1976; 05-16-2019 at 09:25 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •