Copy / Pastespecial in 2007

[Confused Slug]

I am using the code below to format a spreadsheet from 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")
..........

 

[ryguy7272]

Check out this code for manipulating Excel from Access:

Make sure you set a reference to Excel, and then run this code in an Access
module:

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub
Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet
Sub Rep()
Dim strFile As String
strFile = "C:\Documents and Settings\Desktop\Ryan\MyWorkbook.xls"
‘Of course, this is just an example; put the actual path to your actual file
here…
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here...you can record a macro and make the process
super easy!!
End With
' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing
End Sub

Probably the best thing to do is record a macro in Excel ’07 and take the
results and paste it into the spot above where I indicate ‘Your Excel code
begins here...’

Happy Thanksgiving!!

HTH,
Ryan---