Pastespecial with Late Binding

D

Dustin Ventin

I'm writing a Microsoft Access application that integrates with Excel, and
automatically makes Excel do various things using a late-bound Excel-type
object.

Like so:

Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")

Now, I'm trying to select a range of fields and paste only the values into
another worksheet. My code works fine when I'm not using late-binding, but
when I am it starts complaining:

"Pastespecial method of Range class failed."

I've tried several variations, neither work:

ExcelApp.Selection.Pastespecial Paste:=xlPasteValues

ExcelApp.ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks :=False, Transpose:=False

Any ideas? Additionally, since it is quite possible that I will be doing a
lot more work in late-binding with Excel, is there any way I can troubleshoot
these kind of problems without bothering you guys?

Thanks!

Dustin
 
H

Harald Staff

Hi Dustin

Stuff like xlPasteValues is most often enumerated numeric constants, which
are not recognized using late binding. The object browser in the application
(here Excel) is your friend then. Search for xlPasteValues and you will see

Const xlPasteValues = -4163 (&HFFFFEFBD)

now replace the constant xlPasteValues with the real value -4163 in your
code, and it should in theory work (it's past midnight here, so I didn't
test this). Likewise Const xlNone = -4142

HTH. Best wishes Harald
 
B

Bob Phillips

You can also get away without Pastespecial or any enumerated constants

With ExcelApp.Selection
.Value = .Value
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Dustin Ventin

Well, I tried this:

ExcelApp.ActiveSheet.Pastespecial Paste:=-4163, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

And it didn't work. Did I do it wrong?

Dustin
 
D

Dustin Ventin

Actually, that pasted nothing. Isn't that code just setting a selection of
fields to equal what it is already?

Dustin
 
D

Dustin Ventin

OK:

ExcelApp.ActiveSheet.Pastespecial Paste:=-4163, Operation:=-4142, SkipBlanks _
:=False, Transpose:=False

This doesn't work, either.

"Application Defined or Object-Defined Error."

Any ideas?

Dustin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top