I'm trying to write a Macro in Excel that will process all the text files in a particular folder by displaying them in their own tab, and plotting a graph containing all the data. I have gotten as far as writing code for processing one file, but don't know how to go about applying the Macro to all the files. The code I have so far is below. If anyone has any ideas on how to finish it I'd really appreciate it! Thanks.
Sub PeelTest()
'
' PeelTest Macro
'convert .txt to excell & plot graphs
'needs total.xlsx to be open & empty (1 empty sheet) - all selected .txt files put here
'
Dim MyFile As Variant
' Ask the user for the file name to open.
MyFile = Application.GetOpenFilename
' Check for the Cancel button.
If MyFile = False Then Exit Sub
' Open the Text file with the OpenText method.
Workbooks.OpenText Filename:=MyFile, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
Range("F4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Load"""
ActiveChart.SeriesCollection(1).XValues = "='1-1A'!$C$6:$C$500"
ActiveChart.SeriesCollection(1).Values = "='1-1A'!$B$6:$B$500"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
End Sub
1
