Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (xls) files spontaneously converting to text files | Excel Discussion (Misc queries) | |||
Converting PDF Files to Excel | Excel Worksheet Functions | |||
Converting Large XML Files into CSV Files for Excel | Excel Discussion (Misc queries) | |||
Converting Adobe files into Excel files | Excel Discussion (Misc queries) | |||
Converting CSV files into Excel files | Excel Worksheet Functions |