Exporting To Excel And Formatting, Office 2010

[DazJWood]

I have an Access 2003 database that now needs to run in Access 2010.

Amongst the many changes I have so far had to make to forms and VBA I have now hit a wall trying to resolve a particular issue.

I have a report that extracts to Excel and various bits of formatting are applied. This is all done via VBA.

One part of the code draws a textbox and then populates it with text.

The extract of code that is causing the problem is as follows:

exlBook.Sheets("Extract").Shapes.AddTextbox(msoTex tOrientationHorizontal, 1520, 540, _
384#, 180).Select

For byta = 0 To objExcel.WorksheetFunction.Ceiling(Len(arrTextSumm ary(3)) / 200, 1)
If byta = 0 Then
objExcel.Selection.Characters((byta * 200) + 1).Insert String:=Left(arrTextSummary(3), 200)
Else
objExcel.Selection.Characters((byta * 200) + 1).Insert String:=Mid(arrTextSummary(3), (byta * 200) + 1, 200)
End If
Next byta

The line in BOLD is the problem and reports the error -

"Runtime error: 1004 Unable to get the Characters property of the TextBox class"

This ran faultlessly in 2003 and though I have tried I cannot fathom how I need to rewrite this line in order to get the code to work.



Can anyone help?

Thanks in advance,

Daz

[June7]

Did the library Reference for Excel reset when you opened the project in 2010? For 2010 it changed from 12 to 14.

I successfully tested creating textbox and setting value with simple procedure in Excel2010 VBA:

Sub AddTextBox()
    ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 2.5, 1.5, _
        116, 145).TextFrame.Characters.Text = "This is a test of inserting a text box to a sheet and adding some text."
    With Selection.Characters(Start:=1, Length:=216).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub

So I know the Characters method works. Perhaps you need to start with simpler code that works then build it up.

Could you put the code behind the Excel book?

[DazJWood]

As an update:

It seems that with Excel 2010 the characters property can now only access locations in strings that exist. The property has changed to zero based and by trying to access the 200th character it now becomes a value out of range.

The way round it would be to access the 199 character, insert a space as well as the next 199 characters.

However, it also seems that the 256 character limit has also been lifted now, so I can write all the characters in one hit.
So I can just replace the whole For...Next loop with:

objExcel.Selection.Characters.Text = arrTextSummary(3)

It would be so much easier to error trap access if their error messages actually made more sense!!!

June7, thanks for your reply.

Daz