pasting - Excel Code In Access

[ppete]

Hello All,

Could anyone help me with this...?

I made a macro in excel which copy a range of cells and pastes values (rather than formulas) onto the same range.

I then took the code and put it into access like this:

Set XL = CreateObject("Excel.Application")
    
    With XL.Application
       
        .Visible = True
        .Workbooks.Open "H:\Accounts\BRC\Reports\B.xls"
        .CALCULATE
        .Range("B11:E13").Select
        .Selection.Copy
        .Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

But I keep getting this message. Why?



PasteSpecial method of Range class failed


Many thanks for past and future help.

 

[SforSoftware]

You get this message because you're working with Late Binding and Access can not interpret the (numeric) value of xlValues and xlNone.

There are two solutions:
1. The very best is using Early Binding, by setting a reference (Tools, References in the VB Editor) for the Excel Object Library. Then you declare XL with:

Dim XL As New Excel.Application
  With XL...

2. The other way is to change the variables from the Excel Object Library (here xlValues and xlNone) into the numeric Values (here -4163 and -4142, look at the Object Browser (F2) in Excel-VBA)

 

[ppete]

Thanks Bert,

That worked a treat.

I found out some more info on the whole integrating thing here:

http://www.sunny-beach.net/manual/306.htm#o306


Cheers,

Peter