Modifying excel from Access VBA

[gecko_1]

Hi,
I have a database in Access with the Interface created in VBA, the reports are created in Excel by filling in each cell.

Set xlw = xlx.workbooks.Open(reportLoc())
   Set xl = xlw.worksheets("PMP")
   xl.Activate
   xl.cells(2, 8).Value = rs.Fields("xxxx")

This work fine, but now I am trying to create a watermark on the page using word art. The following places the wordart in excel

xl.Shapes.AddTextEffect(msoTextEffect1, "NOT APPROVED", "Arial Black" , 36#, msoFalse, msoFalse, 264.75, 396.75).Select

But I am unable to modify the shape from this point. The code to do this in Excel VBA is eg. "Selection.ShapeRange.Fill.Transparency = 1#"

Is this possible to do from Access VBA and hopefully it is so where would I start?

Also is there a website or book that lists all of the methods available for modifying excel from Access?

Thanks,

 

[fuzzygeek]

http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

http://support.microsoft.com/default.aspx?scid=kb;EN-US;291308

http://support.microsoft.com/default.aspx?scid=kb;en-us;246335

Short example of vba:

'The function is set to True unless a problem develops. Then
'it would be set to False.
fncCreateExcelSpreedsheet = True
' Create a new instance of Excel
Set objXL = New Excel.Application
Set wbXL = objXL.Workbooks.Add
Set wsXLValues = wbXL.Worksheets(1)
Set wsXLCCS = wbXL.Worksheets(2)

'Delete the third(default) worksheet
wbXL.Worksheets(3).Delete

'Set up colors for Values spreadsheet (sheet 1)
wsXLValues.Range("I12:N20").Interior.ColorIndex = 6 'Yellow
wsXLValues.Range("H23:N34").Interior.ColorIndex = 38 'Pinkish
wsXLValues.Range("A24:F39").Interior.ColorIndex = 8 'Lt Blue
wsXLValues.Range("A7:K7").Interior.ColorIndex = 6 'Yellow
wsXLValues.Range("A8:K8").Interior.ColorIndex = 38 'Pinkish
wsXLValues.Range("A9:K9").Interior.ColorIndex = 8 'Lt Blue

'Set up borders and border thickness for Values spreadsheet (sheet 1)
'Where the entry rates came from: Sep 98 settings and weight (thickness)
wsXLValues.Range("I12").Borders(xlEdgeLeft).Weight = xlMedium
wsXLValues.Range("I12:N12").Borders(xlEdgeTop).Weight = xlMedium
wsXLValues.Range("I12:N12").Borders(xlEdgeBottom).Weight = xlMedium

-------
'format the spreadsheet cells
intJ = 1
Do Until rstColumnDataValues.EOF

wsXLValues.Range("A" & intJ).Value = rstColumnDataValues(1)
wsXLValues.Range("A" & intJ).Font.Size = 8
wsXLValues.Range("B" & intJ).Value = rstColumnDataValues(2)
wsXLValues.Range("B" & intJ).Font.Size = 8

 

[gecko_1]

I have found that the following code works if the Shape is WordArt 1 or WordArt 2. After the second error this function is exited and the code runs from the calling method. Is there a better way to loop through errors, or to find out what the name of the wordArt is when it’s created?

i = 0
xl.Shapes.AddTextEffect(msoTextEffect1, "NOT APPROVED", "Arial Black", 100#, msoFalse, msoFalse, 0, 0).Select
wordArt:
Err.Clear
i = i + 1
If i < 10 Then
On Error GoTo wordArt
xl.Shapes("WordArt " & i).Fill.Transparency = 1#
xl.Shapes("WordArt " & i).IncrementRotation 55
xl.Shapes("WordArt " & i).Line.Transparency = 0.4
End If
On Error GoTo err_output

Any Hints Thanks,