Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Everyday, my customer receives several CSV files and needs the the data sorted in different ways. The fields include name, address, city state, zip, dollar amount, etc., and he needs three additional files to be created for each file received, with these respective sorts: by state, zip and dollar amount. The original CSV file name, for example, is filename.csv and the new files should be named: filename_state.csv, filename_zip.csv and filename_dollar.csv. I suspect this is an Excel macro (VBA) which I cannot write because of my continued lack of VBA skills.

    Suggestions, solutions?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try the following code.
    Code:
    Sub ExportSorted()
      SortNSave 4, "state"
      SortNSave 5, "zip"
      SortNSave 6, "dollar"
    End Sub
    
    Sub SortNSave(ColNo As Long, ColName As String)
      Dim strFile As String
      Dim strCSV As String
    
      strFile = ActiveWorkbook.FullName
      strCSV = Left(strFile, Len(strFile) - 4) & _
    	"_" & ColName & ".csv"
      Range("A1").CurrentRegion.Sort Key1:=Cells(1, ColNo), _
    	Order1:=xlAscending, Header:=xlYes
      ActiveSheet.SaveAs Filename:=strCSV, FileFormat:=xlCSV
      ActiveWorkbook.Close SaveChanges:=False
      Workbooks.Open strFile
    End Sub
    Notes:
    • The code should be stored in a separate workbook or in your personal macro workbook Personal.xls.
    • Make sure that the .csv file to be processed is open and is the active workbook when you run the macro.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Can these two subs be stored in a module in the original file and then run?
    I suspect not, for some reason, as I tried that and the original file was closed after running ExportSorted (which is the only macro that showed up, however, in the macro list) and only the "state" csv was created.

    Kevin

    [quote name='HansV' post='761570' date='25-Feb-09 17:12']Try the following code.
    Code:
    Sub ExportSorted()
      SortNSave 4, "state"
      SortNSave 5, "zip"
      SortNSave 6, "dollar"
    End Sub
    
    Sub SortNSave(ColNo As Long, ColName As String)
      Dim strFile As String
      Dim strCSV As String
    
      strFile = ActiveWorkbook.FullName
      strCSV = Left(strFile, Len(strFile) - 4) & _
    [tab][/tab]"_" & ColName & ".csv"
      Range("A1").CurrentRegion.Sort Key1:=Cells(1, ColNo), _
    [tab][/tab]Order1:=xlAscending, Header:=xlYes
      ActiveSheet.SaveAs Filename:=strCSV, FileFormat:=xlCSV
      ActiveWorkbook.Close SaveChanges:=False
      Workbooks.Open strFile
    End Sub
    Notes:
    • The code should be stored in a separate workbook or in your personal macro workbook Personal.xls.
    • Make sure that the .csv file to be processed is open and is the active workbook when you run the macro.
    [/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You cannot store macros in a .csv file (because that is a plain text file), that's why I mentioned that you'd have to store the macro elsewhere.

Posting Permissions

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