|
PureBytes Links
Trading Reference Links
|
Trey,
In the line: Sheets("Sheet1").Copy
Before:=Workbooks(TargetBook).Sheets("Scripts1")
"Sheet1" will never exist hence the 'subscript out of range' message.
The Sheet name will be whatever is resolved from FilePrefix + TestNum +
"o.dat" (minus the extension - i.e. '.dat').
e.g. If you enter a "1" at the TestNum InputBox the Sheet name(s) will be
Sheets("y10t1o")
Sheets("y10t1e")
Sheets("y10tlm")......... respectively each time ReadTestFile is called.
Therefore, instead of:
Sheets("Sheet1").Copy Before:=Workbooks(TargetBook).Sheets("Scripts1")
.... this line would work for this example:
Sheets(Left(FileName, 6)).Copy
Before:=Workbooks(TargetBook).Sheets("Scripts1")
..... and the line following that will also need to be changed.
Sheets(Left(FileName, 6)).Name = SheetName.
I have used the Left Function only to illustrate the problem and solution.
I would suggest changing the use of variable FileName so that it does not
contain the .dat extension rather than using the Left Function in case you
decide to use filenames longer than six characters.
Rgds,
Kim
----- Original Message -----
From: "Trey Johnson" <dickjohnson3@xxxxxxxxxxxxxx>
To: <omega-list@xxxxxxxxxx>
Sent: Wednesday, September 24, 2003 10:31 AM
Subject: Excel Problem
> 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
>
>
>
>
|