Access to Excel PasteSpecial Values

[Rich Pu]

Hi Guys,
Hope you can help
I am trying to interact with excel and I am having a problem with
pastespecial values

I am trying
excelobj.activesheet.pastespecial paste:=xlvalues
it keeps erroring on the paste:=xlvalues, does any one know the
correct syntax to use??

and any help on how to return the last row on a spreadsheet would help
as well

Thanks and regards,

RichPu

[DCM Fan]

If your project does not have a reference to the Excel Object Model, then Acces
can't resolve Excel's named constants, like xlvalues.

You have two choices: 1) Make a reference to the Excel Object Model, or 2)
change the constants to their long equivalents. (For example, xlvalues = -4163,
which I used the immediate window in Excel)

Another technique (if you do't want to follow the advice of 1) is to declare
your constants in your procedure, like so:

Const xlvalues As Long = -4163

Then you don't need to change your original code.

[Randy Harris]

I don't know if this is "correct" syntax, but it is currently working for
me.

' Excel Constants
Public Const xlAscending As Integer = 1
Public Const xlSortLabels As Integer = 2
Public Const xlTopToBottom As Integer = 1
Public Const xlAll As Integer = -4104
Public Const xlValues As Integer = -4163
Public Const xlDown As Integer = -4121
Public Const xlLeft As Integer = -4131
Public Const xlThin As Integer = 1
Public Const xlCenter As Integer = -4108
Public Const xlBottom As Integer = -4107
Public Const xlTop As Integer = -4160
Public Const xlRight As Integer = -4152
Public Const xlAutomatic As Integer = -4105
Public Const xlSolid As Integer = 1
Public Const xlMedium As Integer = -4138
Public Const xlDouble As Integer = -4119
Public Const xlThick As Integer = 4
Public Const xlEdgeBottom As Integer = 9
Public Const xlDatabase As Integer = 1
Public Const xlPageField As Integer = 3
Public Const xlColumnField As Integer = 2
Public Const xlDataField As Integer = 4
Public Const xlRowField As Integer = 1
Public Const xlEdgeRight As Integer = 10

Private Sub DoSomeExcelStuff()
Dim XL As Object, XLA As Object
Set XL = GetObject(SSFile)
Set XLA = XL.Application
With XLA
.Range("A1").copy
.Range("A4:A" & .Range("B4").end(xlDown).Row).pastespecial
Paste:=xlAll
End With
End Sub

It copies the formula in A1, to all cells from A4 to the end of the data in
B. It's late binding, needs no reference. You, of course, only need the
contstants that you are actually using.

HTH,
Randy Harris
"Rich Pu" <ri****@yahoo.com> wrote in message
news:ba**************************@posting.google.c om...

Hi Guys,
Hope you can help
I am trying to interact with excel and I am having a problem with
pastespecial values

I am trying
excelobj.activesheet.pastespecial paste:=xlvalues
it keeps erroring on the paste:=xlvalues, does any one know the
correct syntax to use??

and any help on how to return the last row on a spreadsheet would help
as well

Thanks and regards,

RichPu

[RichPu]

Thanks for the help

RichPu
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:ie***************@newssvr33.news.prodigy.com. ..

I don't know if this is "correct" syntax, but it is currently working for
me.

' Excel Constants
Public Const xlAscending As Integer = 1
Public Const xlSortLabels As Integer = 2
Public Const xlTopToBottom As Integer = 1
Public Const xlAll As Integer = -4104
Public Const xlValues As Integer = -4163
Public Const xlDown As Integer = -4121
Public Const xlLeft As Integer = -4131
Public Const xlThin As Integer = 1
Public Const xlCenter As Integer = -4108
Public Const xlBottom As Integer = -4107
Public Const xlTop As Integer = -4160
Public Const xlRight As Integer = -4152
Public Const xlAutomatic As Integer = -4105
Public Const xlSolid As Integer = 1
Public Const xlMedium As Integer = -4138
Public Const xlDouble As Integer = -4119
Public Const xlThick As Integer = 4
Public Const xlEdgeBottom As Integer = 9
Public Const xlDatabase As Integer = 1
Public Const xlPageField As Integer = 3
Public Const xlColumnField As Integer = 2
Public Const xlDataField As Integer = 4
Public Const xlRowField As Integer = 1
Public Const xlEdgeRight As Integer = 10

Private Sub DoSomeExcelStuff()
Dim XL As Object, XLA As Object
Set XL = GetObject(SSFile)
Set XLA = XL.Application
With XLA
.Range("A1").copy
.Range("A4:A" & .Range("B4").end(xlDown).Row).pastespecial
Paste:=xlAll
End With
End Sub

It copies the formula in A1, to all cells from A4 to the end of the data in B. It's late binding, needs no reference. You, of course, only need the
contstants that you are actually using.

HTH,
Randy Harris
"Rich Pu" <ri****@yahoo.com> wrote in message
news:ba**************************@posting.google.c om...

Hi Guys,
Hope you can help
I am trying to interact with excel and I am having a problem with
pastespecial values

I am trying
excelobj.activesheet.pastespecial paste:=xlvalues
it keeps erroring on the paste:=xlvalues, does any one know the
correct syntax to use??

and any help on how to return the last row on a spreadsheet would help
as well

Thanks and regards,

RichPu