Convert xlsx/csv to .dat file to be saved in a local folder

[Abegail0203]

Hello,

Hope you are doing well.

Needed a help. Looking for a possibility if there is a vba code that will auto-convert an xlsx or csv file into a ".dat" file.

The spreadsheet itself is a list of names and addresses (just standard information) with no formulas or formatting.

How would I go about "exporting" this information into ".dat" format and writing a macro that will do this for me.

Destination folder is a local drive - "H:\Output Folder"

Many thanks,

 

[Marc L]

Hello,​

​

as you forgot to describe your worksheet and what could be your ".dat" format as well​

so I can just advise to activate the Macro Recorder then use the Save As option from the File menu …​

 

[Abegail0203]

Hi Marc,

Thank you for taking my concern. Actually, I have found a similar case here but it posted way back 2014. It solved his problem on the code given by one of the members here. However, when I used his code, I run into errors on the path to where the output will go. Please refer to the text in bold format.
Kindly help me write my destination folder. My local folder is "H:\Output Folder".


Sub TestDatFile()

' 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 "\\filepath\UploadPipe.dat" For Output As #1

For Each myRecord In Worksheets("Dat Test").Range("B4:B" & Range("B" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Worksheets("Dat Test").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 #1, Mid(sOut, 1)
sOut = Empty
End With
Next myRecord
Close #1
MsgBox "UploadPipe.dat has been created successfully", vbOKOnly

End Sub

 

[Marc L]

​

Just replace the string with your complete destination filename like H:\Output Folder\filename …​