|
PureBytes Links
Trading Reference Links
|
Excel uses "Sheets1" and "Scripts1" as indexes into the array of sheets in the respective workbooks. If it can't find the name, it can't find the index, and reports it as an (array) subscript out of range.
Check that these sheets do in fact exist with those exact names when your script hits that line.
HTH,
Mike Gossland
>Hello List,
> I thought I would ask the list as I have not found an answer
>anywhere else. I'm having trouble with the following macros. They are
>supposed to open files and import them into a target workbook. I keep
>getting an error message referring to the following line in the
>ReadTestFile macro:
>
>Sheets("Sheet1").Copy Before:=Workbooks(TargetBook).Sheets("Scripts1")
>
>The error message is: Runtime error '9': subscript out of range. Thanks
>in advance for any help or suggestions.
>Trey
>
>Here are the two macros:
>
>Option Explicit
>
>Global Const TargetBook = "Book1.XLS" ' Spreadsheet file name
>Global Const FilePrefix = "y10t" ' C++ output file prefixes
>Global Const FirstTestNum = 1 ' Number of first test
>Global Const LastTestNum = 5 ' Number of last test
>
>Sub ReadTestFile(SheetName As String, FileName As String)
> ' Procedure reads a comma-delimited data file prepared using the
> ' C++ system development shell into a new sheet. The inserted
> ' sheet is placed before sheet Scripts1 (the sheet containing
> ' this macro).
> Workbooks.OpenText FileName:="C:\scicon\results\test10\" + FileName,
>Origin:=xlWindows _
> , StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
> Semicolon:=False, Comma:=True, Space:=False, Other:=False
> Sheets("Sheet1").Copy
>Before:=Workbooks(TargetBook).Sheets("Scripts1")
> Sheets("Sheet1").Name = SheetName
> Sheets(SheetName).Select
> Cells.Select
> Selection.EntireColumn.AutoFit
> Cells(1, 1).Select
>End Sub
>
>Sub ReadTestResults()
> ' Macro reads the standard set of comma-delimited data files for one
> ' test or for all tests into a set of standard named sheets
> ' using the ReadTestFile procedure.
> Dim TestNum As String, I As Integer
> TestNum = InputBox("Test Number (## for one test, -1 for all
>tests)?")
> If CInt(TestNum) > 0 Then
> ReadTestFile "t" + TestNum + "o", FilePrefix + TestNum + "o.dat"
> ReadTestFile "t" + TestNum + "e", FilePrefix + TestNum + "e.dat"
> ReadTestFile "t" + TestNum + "m", FilePrefix + TestNum + "m.dat"
> End If
> If CInt(TestNum) < 0 Then
> For I = FirstTestNum To LastTestNum
> TestNum = Format(I, "00")
> ReadTestFile "t" + TestNum + "o", FilePrefix + TestNum +
>"o.dat"
> ReadTestFile "t" + TestNum + "e", FilePrefix + TestNum +
>"e.dat"
> ReadTestFile "t" + TestNum + "m", FilePrefix + TestNum +
>"m.dat"
> Next I
> End If
>End Sub
|