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: 871
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,512
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: 871
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,512
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: 871
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
 
cheekybuddha
post Oct 26 2017, 11:57 AM
Post#26


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


Apologies, John - I seem to have let this question slip through during a busy spell.

To use the code as you have done before I think would require early-binding, as Jon pointed out in post #15 (I still didn't test this and don't quite understand how it would work).

Also, the code provided by the macro recorder relies heavily on using ActiveWorkbook and ActiveSheet to reference the objects being operated on.

When automating from Access it might be risky to rely on these objects pointing to what you expect them to, similar to using Screen.ActiveControl in Access can cause problems when Access isn't visible on screen (I may be being overly cautious here wary.gif )

However, the benefits of cleaning the code produced by the macro recorder are significant - firstly SPEED! Basically, stripping out the unnecessary user interactions you speed up the execution of the code considerably.

Also, you have less code which makes it easier to understand what is happening, and has less risk of those pesky unqualified references.

The object model is not that difficult. Essentially it's:

Application --> Workbook --> Worksheet --> Range

Of course, it's a trade-off! If your code works using what is output by the macro recorder then why bother going any further?

But if you want to do more complex things you may find you have no choice!

hth,

d thumbup.gif

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


Regards,

David Marten
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    22nd February 2018 - 05:27 AM