CODEWITHSUNDEEP
excelfixed-widthvba
user3610029
user3610029

Reputation: 1

Create excel macro to open .dat files and convert them to Fixed Width .dat files in excel

I am trying to take .dat files and convert them to fixed width .dat files. When I use Macro Recorder it pops out this:

Sub Macro6()
    Workbooks.OpenText Filename:= _
        "G:\N5Baseline\E1 and E3 Neuroscan\header files\2026E1BEC-1hB.dat", Origin:= _
        437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
        Array(7, 1), Array(22, 1), Array(35, 1), Array(44, 1), Array(55, 1), Array(68, 1), Array(79 _
        , 1), Array(88, 1), Array(99, 1), Array(110, 1)), TrailingMinusNumbers:=True
    ChDir "C:\Users\admin\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\admin\Desktop\Filename1.txt", _
        FileFormat:=xlText, CreateBackup:=False
End Sub

I would want to be able to run this macro on hundreds of files in the same folder. I would want it to open up this .dat file, convert it to fixed width .dat file and then save it in the same folder. I am not great with VB scripting, so be gentle if possible.

Upvotes: 0

Views: 3786

Answers (1)

user3514930
user3514930

Reputation: 1717

Use this code to find the files you want:

Dim FName As String
FName = Dir("e:\0\a\*.dat")
Do While FName <> ""
    Debug.Print FName
    FName = Dir
Loop

Instead of Debug.print, put your code ... or call your procedure with parameter like:

Macro6 ("e:\0\a\", FName)

The new files shall be named:

Left(FName, Len(FName) - 3) & "txt"

After save, close the files

ActiveWorkbook.Close

Upvotes: 0

Related Questions