Macro to save multiple worksheets as .dat files

[qasimmalik]

Hi All,

Is there a macro to save multiple excel worksheets to .dat files, and name the files with the same name as the worksheet.

Please help.

Thank you very much in advance.

 

[Yongle]

A Dat file is a text file
- you have not fully explained what you want it to contain etc
- hopefully this is a a reasonable start for you

Explanation
Here is a modified version of something found on another thread which works for me
- additional procedure loops through an array of worksheets (creates values needed for the other procedure)
- "found" procedure modified to allow looping and to meet your file naming request
- modify to suit your requirement
- it works perfectly for me (my worksheets contain values in columns A to F starting with cell A1)
- I have added a DateStamp which is useful while testing to avoid duplicate names etc (you may want to remove this after testing)

To test
- place both procedures in the same Standard module (in the file containing the worksheets that are to be saved)
- amend aPath value (= path to the folder to which at files are saved)
- insert sheet names of sheets to be copied inside double-quotes, separated by commas in the array
- run CallDat

Caveat
- this is not my code and is not in my area of expertise

[COLOR=#ff0000]'this loops through worksheets and calls procedure CreateDatFile[/COLOR]
Sub CallDat()
    Const aPath = "[COLOR=#000080]C:\Folder\SubFolder[/COLOR]"
    Const Extn = ".Dat"
    Dim DatFullPath As String, TimeStamp As String, aSheet As Variant
    
    For Each aSheet In Array("[COLOR=#006400]NameOfAnotherSheet[/COLOR]", "[COLOR=#006400]NameOfSheet[/COLOR]")
       TimeStamp = Format(Now, " yyyymmdd at hhmmss")
       DatFullPath = aPath & "\" & aSheet & TimeStamp & Extn
       Set aSheet = Sheets(aSheet)
       aSheet.Activate
       Call CreateDatFile(DatFullPath, aSheet)
    Next
End Sub

[I][COLOR=#ff0000]'credit for the code below goes to BrianO
' https://www.mrexcel.com/forum/excel-questions/419731-convert-xls-file-dat-file.html[/COLOR][/I]
Private Sub CreateDatFile(FullPath As String, NextSheet)
 
    '   The delimiter can be set to any value depending on requirements
    Const DELIMITER As String = "|"
 
    Dim myRecord As Range
    Dim myField As Range
    Dim sOut As String
 
     '   this is the path to where you want the output file to go
    Open FullPath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Debug.Print NextSheet.Name
    For Each myRecord In NextSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In NextSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
 
                '   The delimiter can be set either before or after the cell value
                sOut = sOut & myField.Text & DELIMITER
 
            Next myField
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Mid(sOut, 1)
            sOut = Empty
        End With
    Next myRecord
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub