Unable to get the Characters property error

[RRAnthon]

Howdy folks,
Got an existing VBA app that runs an export to excel. Works perfectly fine in excel 2003, fortunately one of my testers has recently had Office 2007 installed and when testing, gets the 'Run-time error 1004, Unable to get the Characters property of the Textbox class.'

Do Until i > Len(txtBHEnvFactors.Text)
i = i + 200
oXls.Selection.Characters(i).Insert String:=Mid(txtBHEnvFactors.Text, i, 200)
Loop
The error occurs on the bolded line. Is this a reference thing or do I need to change some code. I've googled this guy and have drawn a blank.

TIA.

 

[SkipVought]

Hi,

Selection

Is the selection correct?

Using the Selection Method like this is NOT a best and accepted practice.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRAnthon]

Hey, Skip, thanks for taking a look.
Yes, there's data in there. Forgot to add this.

'Environment Factors
.ActiveSheet.Shapes("Text Box 2").Select
.Selection.Characters.Text = Mid(txtBHEnvFactors.Text, 1, 200)

 

[macropod]

What is the definition & specification for 'oXls'? In your Word vba module, do you have a reference to Excel under Tools|References?

Cheers
Paul Edstein
[MS MVP - Word]

 

[RRAnthon]

Yes, it's pointing to 9.0.
Public Sub ExportToExcel(ByRef oXls As Excel.Application)

 

[fumei]

Maybe try

.ActiveSheet.Shapes("Text Box 2").TextFrame _
    .Characters.Text = Mid(txtBHEnvFactors.Text, 1, 200)

< 60 working days until retirement

 

[RRAnthon]

Ok, a little more info (this is a PITA, since I don't have Win2007 installed).

.ActiveSheet.Shapes("Text Box 2").Select
[b].Selection.Characters.Text = Mid(txtBHEnvFactors.Text, 1, 200) [/b]
Do Until i > Len(txtBHEnvFactors.Text)
            i = i + 200
            [COLOR=red]oXls.Selection.Characters(i).Insert String:=Mid(txtBHEnvFactors.Text, i, 200)[/color red]
 Loop

Data is in txtBHEnvFactors.Text, however, only 90 chars. So when it first hits the bolded line, it populates the text box. Then, when it gets to the loop, the red line throws the error, because(?), the string from 201 on is empty? And excel 2007 doesn't like that?

Code down the line throws the same error, however that particular string has 30,000 characters in it.

 

[fumei]

I do not think Selection.Characters.Text is valid.

The error is (if I understand correctly): 'Run-time error 1004, Unable to get the Characters property of the Textbox class.

Unable to get the Characters property of the textbox class.

That is because Characters is the property of TextFrame.

< 60 working days until retirement

 

[macropod]

You should be able to use:

.ActiveSheet.Shapes("Text Box 2").TextFrame.Characters.Text = Left(txtBHEnvFactors.Text, 200)

With this, it shouldn't really matter how long txtBHEnvFactors.Text is.

Cheers
Paul Edstein
[MS MVP - Word]

 

[RRAnthon]

Thanks for the responses, I'll experiment with your suggestions over the weekend.

To those who celebrate, Merry Christmas!