[VexedFist]

Help I am trying to write an EXCEL Macro that will open ALL text files
located in a directory (C:\Temp Data\Raw Data).

Run formatting on each file (I.e., locate a certian figure) and then import
the figure to my datasheet. Then close and delete all the Text files in the
directory.

I know how to do everything EXCEPT Open all of the files, and then close ALL
of the Files, after macro has run.

this is my current Macro that works for ONE File.


Sub KillProperly(Killfile As String)
' Macro recorded 12/30/2004 by Ralph M. Hill @ Siemens Managed Services
Helpdesk
'

'
If Len(Dir$(Killfile)) 0 Then
SetAttr Killfile, vbNormal
Kill Killfile
End If
End Sub

Sub HF()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.OpenText Filename:= _
"C:\Temp Data\Raw Data\Profiles Harper Woods.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(51, 2))

Columns("A:A").EntireColumn.AutoFit
Windows("Macro HF Profile Counts.xls").Activate
Range("G8").FormulaR1C1 = "=VLOOKUP(""Total number of
subscribers"",'Profiles Harper Woods.txt'!C1:C2,2,FALSE)"
Range("G8").Select

Windows("Profiles Harper Woods.txt").Activate
ActiveWindow.Close

KillProperly "C:\Temp Data\Raw Data\Profiles Harper Woods.txt"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Any and all help and or suggestions would be most appreciated.

[Harald Staff]

See if this get you started:
http://www.contextures.com/xlfaqMac.html#LoopBooks

HTH. Best wishes Harald

"VexedFist" skrev i melding
...
Help I am trying to write an EXCEL Macro that will open ALL text files
located in a directory (C:\Temp Data\Raw Data).

Run formatting on each file (I.e., locate a certian figure) and then
import
the figure to my datasheet. Then close and delete all the Text files in
the
directory.

I know how to do everything EXCEPT Open all of the files, and then close
ALL
of the Files, after macro has run.

this is my current Macro that works for ONE File.


Sub KillProperly(Killfile As String)
' Macro recorded 12/30/2004 by Ralph M. Hill @ Siemens Managed Services
Helpdesk
'

'
If Len(Dir$(Killfile)) 0 Then
SetAttr Killfile, vbNormal
Kill Killfile
End If
End Sub

Sub HF()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.OpenText Filename:= _
"C:\Temp Data\Raw Data\Profiles Harper Woods.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(51, 2))

Columns("A:A").EntireColumn.AutoFit
Windows("Macro HF Profile Counts.xls").Activate
Range("G8").FormulaR1C1 = "=VLOOKUP(""Total number of
subscribers"",'Profiles Harper Woods.txt'!C1:C2,2,FALSE)"
Range("G8").Select

Windows("Profiles Harper Woods.txt").Activate
ActiveWindow.Close

KillProperly "C:\Temp Data\Raw Data\Profiles Harper Woods.txt"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Any and all help and or suggestions would be most appreciated.