Output to excel - "Subscript out of range" error 
Author Message
 Output to excel - "Subscript out of range" error

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



Mon, 11 Apr 2011 23:44:23 GMT  
 Output to excel - "Subscript out of range" error


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)



Tue, 12 Apr 2011 01:28:49 GMT  
 Output to excel - "Subscript out of range" error

Quote:

>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?

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.



Tue, 12 Apr 2011 02:36:04 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. "subscript out of range" - Excel

2. "Subscript out of range Error"

3. "Subscript out of Range" error message

4. "subscript out of range"

5. CreateObject("Excel","//server"), MsgBox output

6. CreateObject("Excel","//server"), MsgBox output

7. "Subscirpt out of range error"

8. "Fetch type out of range" error

9. GetObject("","InternetExplorer.Application") fails in Excel VBA

10. GetObject("","InternetExplorer.Application") fails in Excel VBA

11. CreateObject("Excel.Application") Error

12. access 2000 "subscript out of range" error PLZ HELP!!!

 

 
Powered by phpBB® Forum Software