none
excel row into separate file RRS feed

  • Question

  • Experts,

    I have a requirement where I need to Save each excel row into a separate file.

    In my Excel file I have two column A and B and has data like below

    I want to save column B data into separate file for each row and give Column A value in filename.

    if ColumnB starts with "<" as shown in B2 I want to save column B data and give file name as John.xml

    if ColumnB starts with out "<" as shown in B3 I want to save column B data and give filename as Smith.txt

    Thanks


    2Venture2

    Tuesday, June 14, 2016 1:26 AM

Answers

  • You can use the following code to do what you want. Put the code inThisWorkbook and do a Call Oneliners to execute.

    Option Explicit
    '*
    '* Adjust as needed
    '*
    Const STARTROW = 2
    Const STARTCOL = "A"
    Const FILEFOLDER = "C:\Temp\"
    
    Sub OneLiners()
      Dim r As Range
      Dim i As Integer
      Dim lastRow As Integer
      Dim fileName As String
      Dim fileExt As String
      Dim Content As String
      
      Set r = Range(STARTCOL & STARTROW)  'Set starting cell
      lastRow = ActiveSheet.UsedRange.Rows.Count
      For i = STARTROW To lastRow
        fileName = r.Value
        Content = r.Offset(0, 1).Value  'Get file content
        fileExt = ".txt"                'Assume a text file
        If Left(LTrim(Content), 1) = "<" Then fileExt = ".xml"
        Open FILEFOLDER & fileName & fileExt For Output As #1
        Print #1, Content
        Close #1
        Set r = r.Offset(1, 0) 'Next row
      Next i
        
      Debug.Print "Wrote " & (lastRow - STARTROW) + 1 & " files to " & FILEFOLDER
    End Sub
    


    Best regards, George

    Wednesday, June 15, 2016 10:57 AM

All replies

  • Do you mean that Smith.txt contains the single text "pgrstuv" and that's it? ANd that John.xml contains the single line "abcdefg"? But don't you want the contents of John.xml in XML format?

    Do you want to write this in VBA? C#? Is this a one-off project or do you want to create an Excel Addin?

    There are a lot of details missing.

    Moreover, why are you posting in this forum on Visual Studio instead of one of the Excel forums?

    Tuesday, June 14, 2016 1:41 AM
  • Thanks Muth for replying

    VBA or C both will be fine.

    yes that's correct smith.txt contains a text "pgrstuv"

    I already have xml format data for john.xml which starts with "<" so I am checking if column B's first character is "<" without quote I am saving as .xml.


    2Venture2

    Tuesday, June 14, 2016 1:50 AM
  • Hi John,

    Thank you for your post.

    Since Our forum is discussing about VS general question like how to set/configure Visual Studio and Visual Studio tools. As your question is excel. I help you move this case to Microsoft Office for Developers  >  Excel for Developers  for dedicated support.

    Thank you for your understanding.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

    Click HERE to participate the survey.

    Wednesday, June 15, 2016 6:38 AM
  • You can use the following code to do what you want. Put the code inThisWorkbook and do a Call Oneliners to execute.

    Option Explicit
    '*
    '* Adjust as needed
    '*
    Const STARTROW = 2
    Const STARTCOL = "A"
    Const FILEFOLDER = "C:\Temp\"
    
    Sub OneLiners()
      Dim r As Range
      Dim i As Integer
      Dim lastRow As Integer
      Dim fileName As String
      Dim fileExt As String
      Dim Content As String
      
      Set r = Range(STARTCOL & STARTROW)  'Set starting cell
      lastRow = ActiveSheet.UsedRange.Rows.Count
      For i = STARTROW To lastRow
        fileName = r.Value
        Content = r.Offset(0, 1).Value  'Get file content
        fileExt = ".txt"                'Assume a text file
        If Left(LTrim(Content), 1) = "<" Then fileExt = ".xml"
        Open FILEFOLDER & fileName & fileExt For Output As #1
        Print #1, Content
        Close #1
        Set r = r.Offset(1, 0) 'Next row
      Next i
        
      Debug.Print "Wrote " & (lastRow - STARTROW) + 1 & " files to " & FILEFOLDER
    End Sub
    


    Best regards, George

    Wednesday, June 15, 2016 10:57 AM
  • Hi J45John,

    here I would recommend you to check the suggestion given by the George.B.Summers.

    I had check and test the suggestion given by him and find that it can solve your issue.

    please check it and let us know it worked for you or not.

    if it worked then I would recommend you to mark the suggestion of George.B.Summers as an Answer.

    if not then we will try to provide further help to solve your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 16, 2016 12:25 AM
    Moderator