Excel Trying to get COM syntax from VBA (Print dialog) [SOLVED]

[canisdibellum]

Trying to show print dialog in Excel the VBA I have here works, but I'm getting a type mismatch when attempting to execute with COM
in VBA:

Code: Select all

Application.Dialogs(xlDialogPrint).Show
ActiveWindow.SelectedSheets.PrintOut

I've tried everything below in AHK...I broke up the code a little bit for you to make it easier to debug, should the rest of my code have impacted the error I'm getting
Top of Code before COM:

Code: Select all

Export=
(
Box 01,Box 01,Issuable Location,Box~ Pelican,1,Box 01,,,On-Hand
Box 01,Box 01–01,Laptop,Dell E5430,1,11BWBZ1,,Patriot,Issued
Box 01,Box 01–02,Laptop,Dell E5430,1,10QVBZ1,,Patriot,Issued
Box 01,Box 01–03,Laptop,Dell E5430,1,11NWBZ1,,Patriot,Issued
Box 01,Box 01–04,Laptop,Dell E5430,1,11NYBZ1,,Patriot,Issued
Box 01,Box 01–05,Laptop,Dell E5500,1,H9MQDK1,,Patriot,Issued
Box 01,Box 01–06,Laptop,Dell E5500,1,H9LRDK1,,Patriot,Issued
Box 01,Box 01–07,Laptop,Dell E5500,1,H9J0FK1,,,On-Hand
Box 01,Box 01–08,Laptop,Dell E5500,1,H9K0FK1,,,On-Hand
Box 01,Box 01–09,Laptop,Dell E5500,1,H9LXDK1,,,On-Hand
Box 01,Box 01–10,Laptop,Dell E5500,1,H9HZDK1,,,On-Hand
)
Hdr := "Location|Label|Item|Model|Qty|Serial|MAC Address|Issued To|Status"
TtlC := 0
SelCols=
Loop, Parse, hdr, |
{
  SelCols .= A_Index . "|"
  TtlC += 1
}

COM Start:

Code: Select all

Xl := ComObjCreate("Excel.Application")
oExcel := Xl.Application  ;not sure if this even makes sense but I got it from a forum post and tried it in some of the attempts
Xl.Workbooks.Add                            ;add a new workbook
Columns := Object(1,"A",2,"B",3,"C",4,"D",5,"E",6,"F",7,"G",8,"H",9,"I")
EoL := Columns[TtlC]
Xl.Range("A:" . EoL).NumberFormat := "@"
Loop, Parse, Export, `n, `r
{
  RowNumber := A_Index    ; + 1
  if(A_Loopfield = "")
    Break
  
  Loop, Parse, A_Loopfield, csv
  {
    if (A_Index = 10)
      Break
    StringReplace, Field, A_Loopfield, `~, `,, All
    Xl.Range(Columns[A_Index] . RowNumber).Value := Field
  }
}
Xl.ActiveSheet.ListObjects.Add(1, Xl.Range("A1").CurrentRegion,, 1).Name := "Table1"
Xl.ActiveSheet.ListObjects("Table1").TableStyle := "TableStyleMedium2"
Xl.Columns("A:" . EoL).EntireColumn.AutoFit
Xl.Visible := True                          ;by default excel sheets are invisible

Attempts:

Code: Select all

XL.Application.Dialogs(xlDialogPrint).Show
XL.Application.Dialogs("xlDialogPrint").Show
XL.Dialogs(xlDialogPrint).Show
XL.Dialogs("xlDialogPrint").Show

oExcel.Application.Dialogs(xlDialogPrint).Show
oExcel.Application.Dialogs("xlDialogPrint").Show
oExcel.Dialogs(xlDialogPrint).Show
oExcel.Dialogs("xlDialogPrint").Show

End of Code:

Code: Select all

Xl.ActiveWindow.SelectedSheets.PrintOut
Return

Code: Select all

All give me Type MisMatch - Dialogs:
Error 0x80020005 - Type mismatch.

Specifically: Dialogs
...

I know I have to do more to make the dialog actually function but that shouldn't have anything to do with the syntax error....I don't think....

On that note....once we figure out this little syntax debacle...any ideas how to extract what the user puts into the dialog and execute it?
Thanks so much

Edit: [WORKS!]
Thank you Capn Odin for getting me on the right track....

I was able to find the enumerated list (showing the related numbers) of Excel Built-In Dialogs at: https://msdn.microsoft.com/en-us/librar ... 94519.aspx

xlDialogPrint is number 8 on that list... so I now have a working code!

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := True
Xl.Workbooks.Add                            ;add a new workbook
XL.Application.Dialogs(8).Show

And the dialog box works as it should so no need to handle any user input. (so nix the ActiveWindow.SelectedSheets.PrintOut)
You can use the following code to print and close without showing Excel except for a second while printing

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Add                            ;add a new workbook
Xl.Range("A1").Value := "Some Text"
XL.Application.Dialogs(8).Show
Xl.Visible := False
XL.ActiveWorkbook.Saved := True
XL.ActiveWorkbook.Close()

[Capn Odin]

xlDialogPrint is likely an index (integer), I don't know what index would be the equivalent.

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := True
XL.Application.Dialogs(1).Show

[canisdibellum]

xlDialogPrint is likely an index (integer), I don't know what index would be the equivalent.

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := True
XL.Application.Dialogs(1).Show

1 shows the Open dialog box so we're on the right track....but every number higher I tried (stopped at 5) was null

[Capn Odin]

xlDialogPrint is likely an index (integer), I don't know what index would be the equivalent.

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := True
XL.Application.Dialogs(1).Show

1 shows the Open dialog box so we're on the right track....but every number higher I tried (stopped at 5) was null

Did you try 0 ?

[Capn Odin]

Try this.

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := True
MsgBox, % XL.Application.Dialogs.Count

should tell you how many dialogs it contains.

[Capn Odin]

Take a look at this.

[The extension xlsx has been deactivated and can no longer be displayed.]

had not seen your edit.

[canisdibellum]

Take a look at this.

Dialogs.xlsx

Awesome! That works too! Thank you!

I wound up finding similar list at: https://msdn.microsoft.com/en-us/librar ... 94519.aspx

Marked solved and included working script examples in Original post