How to update macro from 2000 to 2003

[rookie]

We created a macro using Excel 2000 and our computer have since been upgraded
to use Excel 2003, not the macro does not work properly when we try and open
it in 2003.

Is there a solution for this other than to rewrite the macro?

[Otto Moehrbach]

Please post the macro (not the file please). HTH Otto
"rookie" <rookie@discussions.microsoft.com> wrote in message
news:77F70499-B007-4E8E-BDB6-3307E359999A@microsoft.com...
> We created a macro using Excel 2000 and our computer have since been
> upgraded
> to use Excel 2003, not the macro does not work properly when we try and
> open
> it in 2003.
>
> Is there a solution for this other than to rewrite the macro?

[rookie]

here it is

Sub RunCodeOnAllXLSFiles()
Dim varInput As String 'asign variable varInput to String type
Dim saveInput As String
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

'ask user for filename and asign the name to variable varInput
varInput = InputBox("enter directory and path where data files are located")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = varInput
.FileType = msoFileTypeAllFiles
.Filename = "*.csv"

'creaate a workbook to compile data in

Set newBook = Workbooks.Add(xlWBATWorksheet) ' add a workbook to
gather data in, name a file to sare results in
With newBook
.SaveAs Filename:="C:\compression-strength.xls"
End With

'add a sheet with a chart in it; data will be ploted for each specimen

Charts.Add.Name = "graph" ' add a chart to the sheet "graph"
Charts("graph").Activate
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ' assign a chart
type XY scater with no markers

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults =
Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'DO YOUR CODE HERE

Range("A1:G1000").Select ' select cell range A1:G1000 in
current workbook
Selection.Copy ' copy all the data
temp = ActiveWorkbook.Name 'store the workbook name in
variable "temp"
Windows("compression-strength.xls").Activate 'activate
target workbook
' add a new worksheet before the active sheet to
compression-strength.xls
Sheets.Add.Name = temp ' give the active sheet the same name
as the curent data file name
ActiveSheet.Paste 'paste the data from the active data file
in "compression-strength.xls"
Range("H2").Select ' selecte cell H2
ActiveCell.FormulaR1C1 = "Max Force (lbs)" ' write "max in
cell H2
Range("H3").Select
ActiveCell.FormulaR1C1 = "=abs(MIN(RC[-1]:R[871]C[-1]))"
'find the max strength achieved with the sample

Range("J2").Select
ActiveCell.FormulaR1C1 = "Displacement (in)"
Range("J3").Select
ActiveCell.FormulaR1C1 = "MAX"
Range("K3").Select
ActiveCell.FormulaR1C1 = "MIN"
Range("L2").Select
ActiveCell.FormulaR1C1 = "Displacement at Failure (in)"
Range("J4").Select
ActiveCell.FormulaR1C1 = "=ABS(MIN(R[-1]C[-4]:R[996]C[-4]))"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=ABS(MAX(R[-1]C[-5]:R[96]C[-5]))"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-5]-R[2]C[-4]"

Sheets("sheet1").Select ' got to sheet 1
Range("A" & lCount + 24) = temp
Range("D" & lCount + 24) = Sheets(temp).Range("H3")
Range("F" & lCount + 24) = Sheets(temp).Range("O2")

'Range("F25").Select
'ActiveCell.FormulaR1C1 = "= MIN(RC[-1]:R[871]C[-1])-
range(f3)"
'Range("F" & lCount + 24) = " = Min(RC[-1]:R[871]C[-1]"
'graph data to chart
' get data from sheet "data"
Charts("graph").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(lCount).XValues =
Sheets(temp).Range("F3:F1000")
ActiveChart.SeriesCollection(lCount).Values =
Sheets(temp).Range("G3:G1000")
ActiveChart.SeriesCollection(lCount).Name = temp

With newBook
.SaveAs Filename:="C:\compression-strength.xls"
End With

wbResults.Close SaveChanges:=True

Next lCount
End If
End With
'summarize data in sheet "summary", max force, standard deviation and COV %

Sheets("sheet1").Select
Sheets("sheet1").Name = "summary"

Call instron_summary_sheet 'Call formating and summary sheet'
' add_mean_std_cov Macro
' Macro recorded 9/6/2005 by GX150Ghost
'

Range("A40").Select
ActiveCell.FormulaR1C1 = "Mean"
Range("A41").Select
ActiveCell.FormulaR1C1 = "Std. Dev."
Range("A42").Select
ActiveCell.FormulaR1C1 = "% COV"
Range("B40").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-15]C:R[-1]C)"
Range("B41").Select
ActiveCell.FormulaR1C1 = "=STDEV(R[-16]C:R[-2]C)"
Range("B42").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R[-2]C*100"
Range("B40:B42").Select
Selection.AutoFill Destination:=Range("B40:F42"), Type:=xlFillDefault
Range("B40:F42").Select
Worksheets("summary").Columns("A").AutoFit
Selection.ColumnWidth = 10

'calculate compression stength for samples copy average, SD, and COV%
Call compstrength

' give title and axis names
Sheets("graph").Activate
Chart.Activate
With ActiveChart 'assign chart title and axis titles
.HasTitle = True
.ChartTitle.Characters.Text = "Compression: Load vs. Displacement"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Displacement
(in)"
.Axes(xlValue).ReversePlotOrder = True
.Axes(xlCategory).ReversePlotOrder = True
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Load(lbs.)"

End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Sub compstrength()
'
Sheets("summary").Activate
'lable columns
'calculate compression strength (ksi)
Range("E25") = "=RC[-1]/(RC[-3]*RC[-2])/1000"
Range("E25").Copy
'copy compression strength formula to remaing cells
Range("E26:E34").Paste
'Copy average, SD and COV to columns
'Range("B36:B38").Copy
'Range("C36:C38").Paste
'Range("D36:D38").Paste
'Range("E36:E38").Paste
'Range("G36:G38").Paste

End Sub
Sub datahandle()
'process static compression strength data in worksheet

Range("A1:G1000").Select ' select cell range A1:G100 in current workbook
Selection.Copy ' copy all the data
temp = ActiveWorkbook.Name 'store the workbook name in
variable "temp"
Windows("compression-strength.xls").Activate 'activate
target workbook
' add a new worksheet before the active sheet to
compression-strength.xls
Sheets.Add.Name = temp ' give the active sheet the same name
as the sample file name
ActiveSheet.Paste 'paste the data from the found file in
"compression-strength.xls"
Range("H2").Select ' selecte cell H2
ActiveCell.FormulaR1C1 = "max force" ' write "max in cell H2
Range("H3").Select
ActiveCell.FormulaR1C1 = "=abs(MIN(RC[-1]:R[871]C[-1]))"
'find the max strength achieved with the sample

Range("J2").Select
ActiveCell.FormulaR1C1 = "Displacement (in)"
Range("J3").Select
ActiveCell.FormulaR1C1 = "MAX"
Range("K3").Select
ActiveCell.FormulaR1C1 = "MIN"
Range("L2").Select
ActiveCell.FormulaR1C1 = "Displacement at Failure (in)"
Range("J4").Select
ActiveCell.FormulaR1C1 = "=ABS(MIN(R[-1]C[-4]:R[996]C[-4]))"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=ABS(MAX(R[-1]C[-5]:R[96]C[-5]))"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-5]-R[2]C[-4]"

Sheets("sheet1").Select ' got to sheet 1
Range("A" & lCount + 1) = temp
Range("B" & lCount + 1) = Sheets(temp).Range("H3")
Range("F" & lCount + 24) = Sheets(temp).Range("O2")

'graph data to chart
' get data from sheet "data"
Charts("graph").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(lCount).XValues =
Sheets(temp).Range("F3:F1000")
ActiveChart.SeriesCollection(lCount).Values =
Sheets(temp).Range("G3:G1000")
ActiveChart.SeriesCollection(lCount).Name = temp

With newBook
.SaveAs Filename:="C:\Documents and
Settings\lopezad.HEXCEL\My Documents\Alfonso\compression-strength.xls"
End With

End Sub
Sub testit()

Set fs = Application.FileSearch
With fs
.LookIn = "D:\compression testing"
.Filename = "*.csv"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For I = 1 To .FoundFiles.Count
MsgBox .FoundFiles(I)
Next I
Else
MsgBox "There were no files found."
End If
End With
End Sub
Sub instron_summary_sheet()
'
' instron_summary_sheet Macro
' Macro recorded 9/6/2005 by GX150Ghost
'

'
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A3:F3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A1:F1").Select
ActiveCell.FormulaR1C1 = "HEXCEL CORPORATION"
Selection.Font.Bold = True
Range("A2:F2").Select
ActiveCell.FormulaR1C1 = "R&T Instron Test Laboratory"
Selection.Font.Bold = True
Range("A3:F3").Select
ActiveCell.FormulaR1C1 = "Dublin, CA"
Selection.Font.Bold = True
Range("A6").Select
ActiveCell.FormulaR1C1 = "Sample ID:"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Test Method:"
Range("A6:A7").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("E6").Select
ActiveCell.FormulaR1C1 = "Test Date:"
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A10").Select
ActiveCell.FormulaR1C1 = "Test Information:"
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A11").Select
ActiveCell.FormulaR1C1 = "Name"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C11").Select
ActiveCell.FormulaR1C1 = "Value"
Range("A11:B11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C11:D11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A12:B12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A13:B13").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A14:B14").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A15:B15").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A16:B16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A17:B17").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A18:B18").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A19:B19").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A20:B20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C12:D12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C13:D13").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C14:D14").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C15:D15").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C16:D16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C17:D17").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C18:D18").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C19:D19").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("C20:D20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A12:B12").Select
ActiveCell.FormulaR1C1 = "Test Machine"
Range("A13:B13").Select
ActiveCell.FormulaR1C1 = "Operator Name"
Range("A14:B14").Select
ActiveCell.FormulaR1C1 = "Interface Type"
Range("A15:B15").Select
ActiveCell.FormulaR1C1 = "Data Acquisition rate (pts/sec)"
Range("A16:B16").Select
ActiveCell.FormulaR1C1 = "Crosshead Speed (in/sec)"
Range("A17:B17").Select
ActiveCell.FormulaR1C1 = "Temperature (deg. F)"
Range("A18:B18").Select
ActiveCell.FormulaR1C1 = "Humidity (%)"
Range("A19:B19").Select
ActiveCell.FormulaR1C1 = "Test Temp. and Cond."
Range("A20:B20").Select
ActiveCell.FormulaR1C1 = "Project Number"
Range("A12:B20").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
End With
Range("C11:D11").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A11:D20").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A11:D11").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A24:F42").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A24:F24").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A46").Select
ActiveCell.FormulaR1C1 = "Comments/Notes:"
Range("A24").Select
ActiveCell.FormulaR1C1 = "Specimen #"
Range("B24").Select
ActiveCell.FormulaR1C1 = "Specimen Thickness (in)"
Range("C24").Select
ActiveCell.FormulaR1C1 = "Specimen Width (in)"
Range("D24").Select
ActiveCell.FormulaR1C1 = "Max Force (lbs)"
Range("E24").Select
ActiveCell.FormulaR1C1 = "Compression Strength (Ksi)"
Range("F24").Select
ActiveCell.FormulaR1C1 = "Displacement at Failure (in)"
Range("A24:F24").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
End Sub






"Otto Moehrbach" wrote:

> Please post the macro (not the file please). HTH Otto
> "rookie" <rookie@discussions.microsoft.com> wrote in message
> news:77F70499-B007-4E8E-BDB6-3307E359999A@microsoft.com...
> > We created a macro using Excel 2000 and our computer have since been
> > upgraded
> > to use Excel 2003, not the macro does not work properly when we try and
> > open
> > it in 2003.
> >
> > Is there a solution for this other than to rewrite the macro?
>
>
>

[NickHK]

rookie,
Any chance of a hint as to which part "does not work properly" ?

One thing I see you have a "With newBook" block in a "With
Application.FileSearch" block.
Also the .FileSearch always seems flakey anyway, especially between
versions.
By the way, could you not set up a template with desired formatting and
basic data, instead of running code every time.
e.g. all this stuff
<set in template>
Selection.Merge
Range("A3:F3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("A1:F1").Select
ActiveCell.FormulaR1C1 = "HEXCEL CORPORATION"
Selection.Font.Bold = True
.................
Range("A7").Select
ActiveCell.FormulaR1C1 = "Test Method:"
Range("A6:A7").Select
</set in template>

NickHK

"rookie" <rookie@discussions.microsoft.com> wrote in message
news:7E88979E-D6C4-495A-B390-60D4F7E34D59@microsoft.com...
> here it is
>
> Sub RunCodeOnAllXLSFiles()
> Dim varInput As String 'asign variable varInput to String type
> Dim saveInput As String
> Dim lCount As Long
> Dim wbResults As Workbook
> Dim wbCodeBook As Workbook
>
> 'ask user for filename and asign the name to variable varInput
> varInput = InputBox("enter directory and path where data files are
located")
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Application.EnableEvents = False
>
> On Error Resume Next
>
> Set wbCodeBook = ThisWorkbook
>
> With Application.FileSearch
> .NewSearch
> 'Change path to suit
> .LookIn = varInput
> .FileType = msoFileTypeAllFiles
> .Filename = "*.csv"
>
> 'creaate a workbook to compile data in
>
> Set newBook = Workbooks.Add(xlWBATWorksheet) ' add a workbook to
> gather data in, name a file to sare results in
> With newBook
> .SaveAs Filename:="C:\compression-strength.xls"
> End With
----------- CUT --------------

[rookie]

I'm not really sure which part is off. The formatting is for the data summary
page. If I set a template I would have to run the macro from the workbook
that has the template, and bring the data into this book?

I guess there is not quick fix for it.

This is what the macro should do

- Find a folder on the cd drive (we have to enter the folder name each time
as it is never the same)
- find all the .cvs files in folder and import them into a workbook (we have
ten .cvs files numbered 1- 10, and we'd want 10 worksheets in one workbook)
- find information on each sheet a place into summary sheet
- creat a chart from the summary sheet

in 2000 it does this but in 2003 it is placing each file into it's own
workbook (10 workbooks, rather than 10 sheets in one workbook). The data is
also being placed in the wrong sections, part of the data from the .cvs file
is on one sheet and the other half is dumped onto the summary sheet.



"NickHK" wrote:

> rookie,
> Any chance of a hint as to which part "does not work properly" ?