none
How to add csv files in numerical [human] order? RRS feed

  • Question

  • Good day to all,

    I was wondering how to add csv files into a xlsx file and the order of each spreedsheet name sort by numerically human order.

    So far the code that I have is:

    Sub MacroLoop()
    Dim strFile As String
    Dim ws As Worksheet
    strFile = Dir("c:\test\*.csv")
    Do While strFile <> vbNullString
    Set ws = Sheets.Add
    ws.Name = Left(strFile, 31) 
    With ws.QueryTables.Add(Connection:= _
       "TEXT;" & "C:\test\" & strFile, Destination:=Range("$A$1"))
       .Name = strFile
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .RefreshStyle = xlInsertDeleteCells
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .TextFilePromptOnRefresh = False
       .TextFileStartRow = 1
       .TextFileParseType = xlDelimited
       .TextFileTextQualifier = xlTextQualifierDoubleQuote
       .TextFileConsecutiveDelimiter = False
       .TextFileTabDelimiter = False
       .TextFileSemicolonDelimiter = False
       .TextFileCommaDelimiter = True
       .TextFileSpaceDelimiter = False
       .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
       .TextFileTrailingMinusNumbers = True
       .Refresh BackgroundQuery:=False
    End With
    strFile = Dir
    Loop
    End Sub

    Thanks in advance for any clue

    Tuesday, August 19, 2014 3:22 PM

Answers

  • I meant 'just inside the  End Sub line"

    Here is the whole macro -

    Sub MacroLoopWhole()
    Dim strFile As String
    Dim ws As Worksheet
    strFile = Dir("c:\test\*.csv")
    Do While strFile <> vbNullString
    Set ws = Sheets.Add
    ws.Name = Left(strFile, 31) 
    With ws.QueryTables.Add(Connection:= _
       "TEXT;" & "C:\test\" & strFile, Destination:=Range("$A$1"))
       .Name = strFile
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .RefreshStyle = xlInsertDeleteCells
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .TextFilePromptOnRefresh = False
       .TextFileStartRow = 1
       .TextFileParseType = xlDelimited
       .TextFileTextQualifier = xlTextQualifierDoubleQuote
       .TextFileConsecutiveDelimiter = False
       .TextFileTabDelimiter = False
       .TextFileSemicolonDelimiter = False
       .TextFileCommaDelimiter = True
       .TextFileSpaceDelimiter = False
       .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
       .TextFileTrailingMinusNumbers = True
       .Refresh BackgroundQuery:=False
    End With
    strFile = Dir
    Loop

        Dim n As Integer
        Dim m As Integer
        
        For m = 1 To Worksheets.Count - 1
           For n = m To Worksheets.Count
                If CInt(Replace(Worksheets(n).Name, ".csv", "")) < CInt(Replace(Worksheets(m).Name, ".csv", "")) Then
                   Worksheets(n).Move Before:=Worksheets(m)
                End If
           Next n
        Next m
    End Sub


    Tuesday, August 19, 2014 6:39 PM
  •  Replace


                If CInt(Replace(Worksheets(n).Name, ".csv", "")) < CInt(Replace(Worksheets(m).Name, ".csv", "")) Then
     

    with

               If CInt(Split(Worksheets(n).Name, "_")(0)) < CInt(Split(Worksheets(m).Name, "_")(0)) Then



    Wednesday, August 20, 2014 1:38 AM

All replies

  • What do you mean by numerically human order? What are some of the file names, and how do you want the sheets sorted?

    Tuesday, August 19, 2014 5:07 PM
  • Good day,

    What I mean is:  

    Having these files:

    1.csv

    10.csv

    2.csv

    When I add them to xlsx, I want to those files be added in each spreedsheet as follows:

    1st spredsheet 2nd spredsheet 3th spredsheet 

    1.csv 2.csv 10.csv

    Where tab is the spreedsheet spacer

    ----------------
    What I get is:

    2.csv 10.csv 1.csv

    • Edited by Beginner_13 Tuesday, August 19, 2014 5:35 PM
    Tuesday, August 19, 2014 5:33 PM
  • Add this to the end of your code:

        Dim n As Integer
        Dim m As Integer
        
        For m = 1 To Worksheets.Count - 1
           For n = m To Worksheets.Count
                If CInt(Replace(Worksheets(n).Name, ".csv", "")) < CInt(Replace(Worksheets(m).Name, ".csv", "")) Then
                   Worksheets(n).Move Before:=Worksheets(m)
                End If
           Next n
        Next m


    Tuesday, August 19, 2014 6:24 PM
  • Thanks for replying back so fast and I know with the properly answer.

    But, I'm not so good on ms excel scripting. But I really need this codeso, please, can you tell me what do you mean by the end of the code, where the code ends? I try it literally and it doesn't work

    Tuesday, August 19, 2014 6:32 PM
  • I meant 'just inside the  End Sub line"

    Here is the whole macro -

    Sub MacroLoopWhole()
    Dim strFile As String
    Dim ws As Worksheet
    strFile = Dir("c:\test\*.csv")
    Do While strFile <> vbNullString
    Set ws = Sheets.Add
    ws.Name = Left(strFile, 31) 
    With ws.QueryTables.Add(Connection:= _
       "TEXT;" & "C:\test\" & strFile, Destination:=Range("$A$1"))
       .Name = strFile
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .RefreshStyle = xlInsertDeleteCells
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .TextFilePromptOnRefresh = False
       .TextFileStartRow = 1
       .TextFileParseType = xlDelimited
       .TextFileTextQualifier = xlTextQualifierDoubleQuote
       .TextFileConsecutiveDelimiter = False
       .TextFileTabDelimiter = False
       .TextFileSemicolonDelimiter = False
       .TextFileCommaDelimiter = True
       .TextFileSpaceDelimiter = False
       .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
       .TextFileTrailingMinusNumbers = True
       .Refresh BackgroundQuery:=False
    End With
    strFile = Dir
    Loop

        Dim n As Integer
        Dim m As Integer
        
        For m = 1 To Worksheets.Count - 1
           For n = m To Worksheets.Count
                If CInt(Replace(Worksheets(n).Name, ".csv", "")) < CInt(Replace(Worksheets(m).Name, ".csv", "")) Then
                   Worksheets(n).Move Before:=Worksheets(m)
                End If
           Next n
        Next m
    End Sub


    Tuesday, August 19, 2014 6:39 PM
  • Thanks so much.

    I have a last question:

    How would the code be if the names are:

    1_ascj(...)vgfsd.csv

    2_skfkvoj(...)asc.csv

    10_pkmce(...)advcg.csv

    Thanks so much for your fruitful help

    Tuesday, August 19, 2014 6:49 PM
  • Please, realy that one is the last question
    Tuesday, August 19, 2014 7:01 PM
  • Are the ... the numbers of interest?  Can you post a couple of actual file names?
    Tuesday, August 19, 2014 7:29 PM
  • Re:  sorting worksheets in numerical order

    Bernie may be awhile, however he continues to amaze me with the quick and appropriate answers he generates.
    I've done the task of writing code to sort sheets in strict numerical order and I would not want to do it again from scratch.  That code is in my "Extras for Excel" commercial add-in.

    In the meantime, you could try using the same number of digits for all numbers in the sheet names...
    Instead of 1 use 01, instead of 9 use 09
    If you have over 99 sheet names then the numbers should all have 3 digit numbers...
    9 becomes 009 and 99 becomes 099.
    Almost any sheet sorting code would then work on the sheet names.

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:37 PM
    Tuesday, August 19, 2014 9:53 PM
  •  Replace


                If CInt(Replace(Worksheets(n).Name, ".csv", "")) < CInt(Replace(Worksheets(m).Name, ".csv", "")) Then
     

    with

               If CInt(Split(Worksheets(n).Name, "_")(0)) < CInt(Split(Worksheets(m).Name, "_")(0)) Then



    Wednesday, August 20, 2014 1:38 AM
  • Thanks so much for your kindly help, wish the best energy on life to you (I don't know other way to say thanks, but thanks again) ( :
    Wednesday, August 20, 2014 9:34 PM