Creating Multiple .DAT Files

Hi

I am using this code to generate multiple .DAT files from excel, however only the first column is getting printed, how do I change it to a range, if you see the attached screen, I have data from A1 to H12 and I would like everything to come to the XML file

Creating Multiple .DAT FilesData

Sub ExportTextFiles()


Dim i As Long
Dim LastDataRow As Long
Dim MyFile As String
Dim fnum
LastDataRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
For i = 1 To LastDataRow
‘The next line uses the contents of column B on the same row to name it
MyFile = “C:\Users\pkc504787\Desktop\test\” & ActiveSheet.Range(“A” & i).Value & “.dat”
‘Use the following line instead to just create a series of numbered files
‘MyFileName = “C:\Users\pkc504787\Desktop\test\Book” & i & “.txt”
fnum = FreeFile()
Open MyFile For Output As fnum
Print #fnum, Format(Range(“A” & i))
Close fnum
Next i

End Sub

Top Contributor Asked 10 hours ago in VBA.
Add Comment
1 Answer(s)

HI Pranab,

Here is the solution for your query.

Sub ExportTextFiles()
    Dim i As Long
    Dim LastDataRow As Long
    Dim MyFile As String
    Dim fnum
    With ActiveSheet
        LastDataRow = .Range("A" & Rows.Count).End(xlUp).Row
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    For i = 2 To LastDataRow
        'The next line uses the contents of column B on the same row to name it
        MyFile = "C:\Users\pkc504787\Desktop\test\" & ActiveSheet.Range("A" & i).Value & ".dat"    '
        
        fnum = FreeFile()
        
        Open MyFile For Output As fnum
        
        'It reads all values from respective row
        For icntr = 1 To lastColumn
            Print #1, Cells(i, icntr)
        Next icntr
        
        Close fnum
    Next i
End Sub

Hope it resolves your problem. Please do let us know, If you need any further assistance.

Regards!

Expert Answered 3 hours ago.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.