in VBA:
Code: Select all
Application.Dialogs(xlDialogPrint).Show
ActiveWindow.SelectedSheets.PrintOut
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
}
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
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
Code: Select all
Xl.ActiveWindow.SelectedSheets.PrintOut
Return
Code: Select all
All give me Type MisMatch - Dialogs:
Error 0x80020005 - Type mismatch.
Specifically: Dialogs
...
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
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()