We have been running this script on 2003 for 2-3 years with no
problem...recently I upgraded my machine to 2007. When running the script,
the first error I get is that the file name is incorrect...so I noticed the
extension for 2007 is .xlam...added the "m" to the script...now it won't run
because of something else...
The error message I get now is "Runtime error 1004: method run of object
'_application' failed"...

Any suggestions will be greatly appreciated.

Sub Select_Sample()

Dim vNumber As Variant

'- Prompt for OK / Cancel
If MsgBox("This will erase the current sample on this worksheet",
vbOKCancel) = 2 Then
Exit Sub
End If

'- unprotect sheet to select
ActiveSheet.Unprotect
'- Get number of sample items to select
vNumber = Range("Number_of_Items").Value + 10

'- Clear output range for sample
Range("Sample_Range").ClearContents

'- call analysis toolpak to select sample
Application.Run "ATPVBAEN.XLAM!sample",
ActiveSheet.Range("$A$11:$A$65536") _
, ActiveSheet.Range("$K$11:$K$310"), "R", vNumber, False

'- copy conditional format to smaple selection
Range("Conditional_Format").Copy
Range("Sample_Range").PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'- Select cell & cancel copy
Range("J9").Select
Application.CutCopyMode = False
'- Reprotect sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

End Sub

hi pfa,

Where is the code erroring out now?
Is it on the same line?

Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200432

http://www.thecodecage.com/forumz

Hi,

The error was on this portion:

'- call analysis toolpak to select sample
Application.Run "ATPVBAEN.XLAM!sample",
ActiveSheet.Range("$A$11:$A$65536") _
, ActiveSheet.Range("$K$11:$K$310"), "R", vNumber, False

However, I found out by happenstance, reading through some really old posts
here that, by using the Convert function from the Office Menu, the code would
run properly (as long as I edited the file extension to add the "m" to the
end of it). This worked, so I am all set. Thanks for taking the time to
reply. I apporeciate it.

"broro183" wrote:


hi pfa,

Where is the code erroring out now?
Is it on the same line?

Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200432

http://www.thecodecage.com/forumz

.