Converting files to excel
 

Hi, I am very new to VB scripting and I have a problem. I have a bunch
of regular files in a folder and I need to convert each file to an
excel spreadsheet and then create a chart from the excel file. I
recorded a macro to convert the first file and create a chart, but I am
having problems ‘looping’ through the rest of the files. Here is what I
have so far:

Dim myFile As String
Dim basebook As Workbook
Dim mybook As Workbook
Dim myFolder As String
Dim FNames As String
Dim SaveDriveDir As String

SaveDriveDir = "C:\temp\output"
myFolder = "C:\temp\tmp"
ChDrive myFolder
ChDir myFolder
myFile = "DIR(C:\temp\tmp)"
FNames = "DIR(C:\temp\tmp\*.out)"

Do While FNames < ""
ChDir myFolder
Workbooks.OpenText Filename:="FNames", Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1))
Range("A6").Select
ActiveCell.FormulaR1C1 = "Time"
Range("A7").Select
'Sheets("FNames").Select
Sheets("DIR(C:\temp\tmp)").Select
ActiveWindow.ScrollRow = 262
Sheets("FNames").Name = "FNames"
Range("A6:E293").Select
Range("E293").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("FNames").Range( _
"A6:E293"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="FNames.chart"
With ActiveChart
..HasTitle = True
..ChartTitle.Characters.Text = "FNames"
..Axes(xlCategory, xlPrimary).HasTitle = False
..Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWorkbook.SaveAs Filename:="SaveDriveDir\FNames", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Loop

End Sub

When I reach the following line:
Workbooks.OpenText Filename:="DIR(C:\temp\tmp)", Origin _
It is looking for the file name = FName.xls and not the variable set in
line:
FNames = "DIR(C:\temp\tmp\*.out)"
Or when the loop is initialized…
Any help would be greatly appreciated…


--
jping45
------------------------------------------------------------------------
jping45's Profile: http://www.excelforum.com/member.php...o&userid=29446
View this thread: http://www.excelforum.com/showthread...hreadid=491528