Excel COM Insert (and Delete) Methods

[Exaskryz]

Code: Select all

---------------------------
&Test Script.ahk
---------------------------
Error:  0x800A03EC - 
Source:		Microsoft Excel
Description:	Unable to get the Insert property of the Range class
HelpFile:		xlmain11.chm
HelpContext:	0

Specifically: Insert

	Line#
	316: Return
	320: Loop,250
	321: SendInput,{Del}{Backspace}
	322: SendInput,=$N:$X
	323: Return
	327: xl := ComObjActive("Excel.Application")
	328: xl.Range("M1514:X1514").Select  
--->	329: xl.Selection.Insert("xlShiftDown")  
	330: xl.Range("M1522:X1522").Select  
	331: xl.Selection.ClearContents  
	332: xl.Range("M1530:X1530").Select  
	333: xl.Selection.ClearContents  
	334: xl.Range("M1538:X1538").Select  
	335: xl.Selection.Delete("xlShiftUp")  
	336: xl := ""

Continue running the script?
---------------------------
Yes   No   
---------------------------

Not sure what is going wrong, will be digging into the Excel help file... but I have been googling for a while. I've found pages like https://msdn.microsoft.com/en-us/librar ... -snippet-1 and https://msdn.microsoft.com/en-us/librar ... elete.aspx

I'm at a loss as to how I should be doing this. Line 328 is working properly. I have also tried combining 328 and 329 by using xl.Range("M1514:X1514").Insert("xlDown") (didn't yet learn of the xlShiftDown document as named in the first link, haven't yet tried xlShiftDown here, but assume it won't work based on...) to no avail.

VBA Code that I have from recording the macro; you'll notice I'm trying to apply this to a different range.

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("M1442:X1442").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M1450:X1450").Select
    Selection.ClearContents
    Range("M1458:X1458").Select
    Selection.ClearContents
    Range("M1466:X1466").Select
    Selection.Delete Shift:=xlUp
End Sub

[jeeswg]

- This might not be relevant in this case but Selection/Range/Select etc only apply to the active workbook.
- It's not "xlShiftDown", but xlShiftDown without quotes e.g. xlShiftDown := -4121
ms%20office%20constants%20(excel).ahk
https://raw.githubusercontent.com/jeesw ... excel).ahk
- [EDIT:] Do a search like: msdn excel insert, to get the MSDN pages:
Range.Insert Method (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel
Range.Delete Method (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel

Code: Select all

xlShiftDown := -4121
xlShiftUp := -4162
xlFormatFromLeftOrAbove := 0

xl.Selection.Insert(Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove)
xl.Selection.Delete(Shift:=xlShiftUp)

[Blackholyman]

yeah you need to use the value of the constants not the string name of them in AHK, most just do like jeewg showed but that is just for human readablity you only need .Insert(-4121)


look under point 5 here https://autohotkey.com/boards/viewtopic.php?t=8978

[Exaskryz]

Thanks Black. I got as far as section 4 to verify methods would be formatted like functions. Didn't go beyond that.

Thanks jeeswg, I figured it'd be some numerical value, but had no idea where to find it. If I follow the link on the Insert docs I found for the constants, I get this page with no numbers in the table: https://msdn.microsoft.com/en-us/librar ... ction.aspx . The link you provide brings me to https://msdn.microsoft.com/en-us/vba/ex ... tion-excel which has the numbers.

I want to say that I ended up on these less informative pages using F1 in the VBA editor as kon instructs in Black's link, but maybe it came from googling. It prolly was googling as I think back to my late night hurdle, because I got thrown to a page that seemed to be on a different insert method.

Here's the link F1 in VBA editor brings me when my cursor is in word "Insert": https://msdn.microsoft.com/en-us/VBA/Ex ... thod-excel ... shapenodes doesn't sound right.

Referring to point 5 in kon's guide, it loops back to 2.3 (using ctrl+f to find the Object Browser) and digging through search results I can find the right reference with F1.

Thanks to both of you; working in Excel maybe 2-3 times a year has kept me unfamiliar with all this.

[jeeswg]

In Excel, to get a constant's value:
- look it up on Microsoft documentation
- generate a list of constants via COM (see my link above)
- look it up via an Excel macro:

Code: Select all

'Excel VBA
Sub Macro1()
MsgBox xlLastCell '11
End Sub