Save an Excel cell to a file

Question

I need to save the contents of a cell to an xml file named by another cell.

I need to perform this check for every row in the sheet.

Sub FormatRange()

    Dim rng As Range
    Dim row As Range
    Dim cell As Range

    Set rng = Range("A1:AG686")

    For Each row In rng.Rows


    Next row

End Sub

For each row I need to save cell AG to xml file named after cell C in the same row.

I am guessing I can use StreamWriter to write the file. I think the real problem is referencing the cells I need.

What you've posted so far is a random excerpt from a totally different topic, and has absolutely nothing with what you've asked here. Please make a valid effort to solve the problem yourself, and then if you run into problems you can post **your** code here and ask a specific question about it. Just grabbing VBA code from another question is not an actual effort to solve the problem first. — Ken White, Aug 06 '13 at 21:48
If you're using 2007+, xlsx files are Open XML, which you can unzip. Inside the xl folder is a worksheets subfolder that already has the xml you're looking for. — mr.Reband, Aug 06 '13 at 21:49

score 4 · Accepted Answer · answered Aug 06 '13 at 22:22

 Sub FormatRange()

 Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("A2:AG686")

' Declare a FileSystemObject.
Dim fso As FileSystemObject

' Create a FileSystemObject.
Set fso = New FileSystemObject
' Declare a TextStream.
Dim stream As TextStream

' Create a TextStream.

For Each row In rng.Rows
        Dim path As String
        path = "C:\vba\" & row.Cells(1, 4) & ".xml"
       ' MsgBox (path + row.Cells(1, 33))
        Set stream = fso.CreateTextFile(path, True)
        stream.Write row.Cells(1, 33)


Next row
stream.Close

End Sub

Did it! Clustered my way through. Thanks for the suggestions.

score 1 · Answer 2 · answered Aug 06 '13 at 21:52

1

Do you have to use VBA? I'm guessing if you know what a StreamWriter is you use C# or another .NET language. If so have a look at EPPlus, you can easily loop through your Excel sheet and use the .NET Framework.

http://epplus.codeplex.com/

answered Aug 06 '13 at 21:52

Kevin

11
1

Yes, I can use c# but when i get to work i dont have access to c#, only vba. The xml will not have the info im looking for, because the xml is for a different program alltogether, ive created the xml information in a cell by linking together other cells with the required xml and other cells containing the address/name info etc in other cells. – JMG Aug 06 '13 at 21:59

score 0 · Answer 3 · answered Mar 19 '19 at 22:22

Here is my flexible solution with right-click action and file choosing dialog:

Public Sub CellSaver()
    Dim cell
    For Each cell In Application.Selection.Cells
        With Application.FileDialog(msoFileDialogSaveAs)
          .Title = "Please select the file to save cell contents"
          If .Show Then
            With CreateObject("Scripting.FileSystemObject").CreateTextFile(.SelectedItems(1), True)
                .Write cell
                .Close
            End With
          End If
        End With
    Next cell
End Sub

Private Sub Workbook_Open()
    With Application.CommandBars("Cell").Controls.Add(Temporary:=True)
        .Caption = "Save to file"
        .Style = msoButtonCaption
        .OnAction = "'CellSaver'"
    End With
End Sub

Credits go to:

Save an Excel cell to a file

3 Answers3