UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Unexplained Error Excel Autofill Macro, Access 2016    
 
   
InOverMyHead
post Oct 14 2017, 03:30 AM
Post#21



Posts: 858
Joined: 3-March 11
From: Sydney, Australia


Hi David

Thank you for your patience. All understood and a very good learning exercise in Excel automation for me.

Cheers
John
Go to the top of the page
 
cheekybuddha
post Oct 14 2017, 03:31 AM
Post#22


UtterAccess VIP
Posts: 9,177
Joined: 6-December 03
From: Telegraph Hill


John, you're most welcome!

Just keep an eye out for those pesky unqualified references when converting macro generated code!

Jon and I are pleased we could assist.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
InOverMyHead
post Oct 15 2017, 07:23 PM
Post#23



Posts: 858
Joined: 3-March 11
From: Sydney, Australia


Hi David

I copied and pasted your sample code and I get object not supported error on the .Paste line.
CODE
Sub test()

Dim oXLApp As Object, _
    oXLWbk As Object, _
    oXlSheet As Object

Set oXLApp = CreateObject("Excel.Application")
With oXLApp
    Set oXLWbk = .Workbooks.Open(fMyDocs & "Anniversaries.xlsx")
    With oXLWbk
        Set oXlSheet = .Sheets(1)
        With oXlSheet
            .Range("A1:D1").Copy
            .Range("A2").Paste  '<-------------
        End With  ' oXlSheet object
        .Save
        .Close
    End With  ' oXLWbk object
    .Quit
End With  ' oXLApp object

Set oXlSheet = Nothing
Set oXLWbk = Nothing
Set oXLApp = Nothing

End Sub

John
Go to the top of the page
 
cheekybuddha
post Oct 15 2017, 07:54 PM
Post#24


UtterAccess VIP
Posts: 9,177
Joined: 6-December 03
From: Telegraph Hill


Yes, it looks like .Paste is a method of the Worksheet object rather than the Range object. (Not very intuitive!!!)

This is because the .Copy method contains a Destination argument - ie copy and paste in one go!

See here

So, try:
CODE
' ...
        With oXlSheet
            .Range("A1:D1").Copy destination:=.Range("A2")
        End With
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
InOverMyHead
post Oct 15 2017, 09:53 PM
Post#25



Posts: 858
Joined: 3-March 11
From: Sydney, Australia


David

OK, thanks again.

Though may I ask, as I know very little about Excel automation and have always relied on the macro recorder ,why can't I use oXLApp like I have always done as I can't recall having any problems with that method?

Regards
John
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2017 - 09:43 AM