Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: TO convert Excel entire workbook in csv format

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    TO convert Excel entire workbook in csv format

    Hi

    I have excel workbbok with around 20 sheet. I want to convert it in csv format so that i will get all sheet in single csv file.

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    Code:
    Sub SheetsToCSV()
    Application.DisplayAlerts = False
    dpath = "C:\test\"      ' <<< to be changed
        For Each ws In Worksheets
            ws.Copy
            ActiveWorkbook.SaveAs Filename:=dpath & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
            ActiveWorkbook.Close
        Next ws
    Application.DisplayAlerts = True
    End Sub

  3. #3
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    I guess the most efficient way would be to export the data using ADO.

    If you export each sheet as patel explained, you can use this method providing you did not add a header/fieldnames row.
    [SOLVED] Import multiple files from same directory

    Code:
    'http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/
    Sub MakeCSVMasters()
        Dim p As String, x() As Variant, i As Integer
    
        p = ThisWorkbook.Path & "\?-?????? *.csv"
        x() = GetFileList(p)
        Select Case IsArray(x)
            Case True 'files found
                'MsgBox UBound(x), , "Count of Found Files"
                'Sheets("Sheet1").Range("A:A").Clear
                'For i = LBound(x) To UBound(x)
                '    Sheets("Sheet1").Cells(i, 1).Value = x(i)
                'Next i
                MakingCSVMasters ThisWorkbook.Path, x()
            Case False 'no files found
                MsgBox "No matching files"
        End Select
    End Sub
    
    Sub MakingCSVMasters(pPath As String, a() As Variant)
      Dim x() As Variant, y() As Variant, xv As Variant, yv As Variant
      Dim z() As Variant, zv As Variant, i As Integer, s As String
      
      x() = UniqueArray(a)
      'MsgBox Join(x, vbLf)
      
      ReDim y(LBound(x) To UBound(x))
      i = LBound(x) - 1
      For Each xv In x()
        'MsgBox Join(Filter(a(), xv, True), " ")
        'Add Parent Path, pPath, and embed quotes around full file name
        'and bulid string for COPY
        s = vbNullString
        i = i + 1
        For Each zv In Filter(a(), xv, True)
          '+ sign is the COPY concatenation operator
          s = s & "+" & """" & pPath & "\" & zv & """"
        Next zv
        y(i) = Right(s, Len(s) - 1) 'Trim first "+" character
        'MsgBox Join(y(), vbLf)
      Next xv
      
      For i = LBound(y) To UBound(y)
        s = "cmd /c Copy " & y(i) & " " & """" _
          & pPath & "\Master_" & Left(x(i), 8) & ".csv" & """"
        'Debug.Print s
        Shell s, vbHide
      Next i
    End Sub
    
    Function UniqueArray(inArray() As Variant) As Variant
    Dim it As Variant, sn() As Variant, c00 As Variant
      With CreateObject("scripting.dictionary")
          For Each it In inArray()
              c00 = .Item(Left(CStr(it), 8))
          Next
          sn = .Keys ' the array .keys contains all unique keys
          'MsgBox Join(sn, vbLf) ' you can join the array into a string
      End With
      UniqueArray = sn
    End Function
    
    
    Function GetFileList(FileSpec As String) As Variant
    '   Returns an array of filenames that match FileSpec
    '   If no matching files are found, it returns False
    
        Dim FileArray() As Variant
        Dim FileCount As Integer
        Dim FileName As String
        
        On Error GoTo NoFilesFound
    
        FileCount = 0
        FileName = Dir(FileSpec)
        If FileName = "" Then GoTo NoFilesFound
        
    '   Loop until no more matching files are found
        Do While FileName <> ""
            FileCount = FileCount + 1
            ReDim Preserve FileArray(1 To FileCount)
            FileArray(FileCount) = FileName
            FileName = Dir()
        Loop
        GetFileList = FileArray
        Exit Function
    
    '   Error handler
    NoFilesFound:
        GetFileList = False
    End Function

  4. #4
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    Quote Originally Posted by Kenneth Hobson View Post
    I guess the most efficient way would be to export the data using ADO.
    If you export each sheet as patel explained, you can use this method providing you did not add a header/fieldnames row.
    can you explain better what you mean ?

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    This is sufficient:

    Code:
    Sub M_snb()
      For Each sh In Sheets
        sn = sh.UsedRange
        For j = 1 To UBound(sn)
            c00 = c00 & vbCr & Join(Application.Index(sn, j, 0), ",")
        Next
      Next
      
      CreateObject("scripting.filesystemobject").createtextfile("G:\OF\summary.csv").write Mid(c00, 2)
    End Sub

  6. #6
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    Patel, the op said:
    ...get all sheet in single csv file.
    Either "...export all sheets into a single csv file" or "...export each sheet into separate csv files." was the intent. I read it as the former. You can see the referenced thread for more info about that users need for one master csv file.

  7. #7
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    @Kenneth
    Sorry, I misread the initial post

    # snb
    For j = 1 To UBound(sn) ???????????
    Last edited by patel; 08-14-2013 at 09:54 PM.

  8. #8
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    Patel, maybe you read it right and I read it wrong. Sometimes I read well and sometimes not. Only pritee knows for sure, or your hair dresser....

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Quote Originally Posted by patel;6375

    # snb
    [B
    For j = 1 To UBound(sn)[/B] ???????????
    Did you test the macro?
    If yes, what is your question (beside the question marks) ?

  10. #10
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    yes I did, run-time error 13 (excel 2010)

Similar Threads

  1. Replies: 4
    Last Post: 06-20-2013, 04:25 PM
  2. Save Excel 2010 File In CSV Format VBA
    By mag in forum Excel Help
    Replies: 7
    Last Post: 01-08-2013, 07:16 PM
  3. Need VBA code to convert csv to xlsx and vice versa
    By Pravee89 in forum Excel Help
    Replies: 1
    Last Post: 10-13-2012, 11:31 PM
  4. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 AM
  5. Save File In CSV Format VBA
    By Raj Kumar in forum Excel Help
    Replies: 3
    Last Post: 06-01-2011, 07:22 AM

Posting Permissions

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