0

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.

Community
  • 1
  • 1
user3610029
  • 1
  • 1
  • 1

1 Answers1

0

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
user3514930
  • 1,721
  • 1
  • 9
  • 7
  • I currently have this coding but it does not like what's after<> "": Dim FName As String FName = Dir("G:\test\*.dat") Do While FName <> "" Workbooks.OpenText Filename:=FName_ 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 ActiveWorkbook.SaveAs Filename:= FName xlText, CreateBackup:=False FName = Dir Loop End Sub – user3610029 May 07 '14 at 17:14
  • FName rerturn only the name of the file, not the path (2° code sample). if you don't change the extension you overwrite (3° code)... – user3514930 May 08 '14 at 05:43