Hi

I'm using the following code to output various bits of data to an
excel sheet:

                With
Workbooks("ScorecardOutput.xls").Sheets("Scorecard")
                    .Range("D" & iRNum) = sRecommend
                    .Range("E" & iRNum) = ipCode
                    .Range("H" & iRNum) = iDirectCompDist
                End With

I have one procedure that reads in the data from a text file and then
calls the procedure containing the above code. This works for the
first row of data, however, when it attemps the write the next line of
data in Excel i get the message "Subscript out of range" on the
following line

With Workbooks("ScorecardOutput.xls").Sheets("Scorecard")

so it appears to recognise the workseet once and then doesn't for the
following rows. I've tried specifying the worksheet using

Dim xl As New Excel.Application
Dim xlSheet As Worksheet

            Set xlwbook =
Workbooks("ScorecardOutput.xls").Sheets("Scorecard")
            Set xlSheet = xlWbook.Sheets("Scorecard")

             with xlSheet

etc but no joy. any ideas where i'm going wrong?

Thanks for any suggestions.
Paul

Hi

I'm using the following code to output various bits of data to an
excel sheet:

                With
Workbooks("ScorecardOutput.xls").Sheets("Scorecard")
                    .Range("D" & iRNum) = sRecommend
                    .Range("E" & iRNum) = ipCode
                    .Range("H" & iRNum) = iDirectCompDist
                End With

I have one procedure that reads in the data from a text file and then
calls the procedure containing the above code. This works for the
first row of data, however, when it attemps the write the next line of
data in Excel i get the message "Subscript out of range" on the
following line

With Workbooks("ScorecardOutput.xls").Sheets("Scorecard")

so it appears to recognise the workseet once and then doesn't for the
following rows. I've tried specifying the worksheet using

Dim xl As New Excel.Application
Dim xlSheet As Worksheet

            Set xlwbook =
Workbooks("ScorecardOutput.xls").Sheets("Scorecard")
            Set xlSheet = xlWbook.Sheets("Scorecard")

             with xlSheet

etc but no joy. any ideas where i'm going wrong?

What if you abandon the With statement?

Also, where is your code located?

Paul
~~~~
Microsoft MVP (Visual Basic)

If that's your actual code, xlwbook needs to be a member of the
application object  (xl), and you haven't declared it at all, so
you're trying to 'Set' an Object that doesn't exist;

Dim xl As Excel.Application
Dim xlwbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xl = New Excel.Application

' NOTE: xlwbook = *XL*.Workbooks.Open...
Set xlwbook = xl.Workbooks.Open("c:\ScorecardOutput.xls")

Set xlsheet = xlwbook.Sheets("Scorecard")

With xlsheet
.Range()...etc...

HTH
--
Alfie [UK]
<http://www.delphia.co.uk/>
If you meet someone without a smile.......give them yours.