I am using the code below to format a spreadsheet from within Access. After
copying sheet1 I then use pastespecial to remove links to the data on sheet 2
before deleting sheet2. When i use this in 2003 all is well. However, in 2007
the
copy / pastespecial does not appear to copy and paste the data in any
Textboxes on sheet1. Is this the real problem? Why does this occur and how
can i over come the issue? Any info greatfully recieved.


Set xlWb = xlApp.Workbooks.Open(newFileName)
xlApp.Visible = True

' Formats Excel Workbook
Dim xlSheet As Object
Set xlSheet = xlWb.Worksheets("Sheet1")
xlSheet.Cells.Copy
xlSheet.Cells.PasteSpecial Paste:=-4163, Operation:=-4142,
SkipBlanks:=False, Transpose:=False
xlWb.Worksheets("Sheet2").Delete
xlSheet.Select
xlSheet.Range("A1").Select
xlSheet.Protect ("secret")
..........

Text boxes will never be copied in any version of Excel. the boxes are
not part of the worksheet object. Boxes are just a type of shape that
sits ontop of the workksheet and is not part of the worksheet object.

I would also add the Reference in the Access VBA Tool menu for eacel so
you don't have to use the number -4142 and -4143. Go to menu Tools -
Refernce
and add the following library


Microsoft Excel Object 11.0 library (or latest version on your version
of Access)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157453

Microsoft Office Help