Excel Automation - Printer Selection

Hi All, I made a CU to put data to a xls.file and then to print it out (“xlsheet.printout”). I take the data direct from a form without a report. It works fine with “xlapp.activeprinter= myPrinter”. But now I’m looking for a chance to show the ‘Printer Selection’- Dialog in Attain or in Excel. By Automation ‘xlApp.Dialogs(xlDialogPrint).Show’ it doesn’t work. Is there a chance to show the dialog in Attain like CU 412 ‘Common Dialog Management’? TIA bye Andre

Hi I’ve played a little bit with dialog/dialogs in automation. Name DataType Subtype Length xlApp Automation ‘Microsoft Excel 9.0 Object Library’.Application xlDialogs Automation ‘Microsoft Excel 9.0 Object Library’.Dialogs xlDialog Automation ‘Microsoft Excel 9.0 Object Library’.Dialog xlDialogs:= xlApp.Dialogs; XXXXXXXXXXXXX xlDialog.Show; XXXXXXXXXXXXX See below! If I try: xlDialog:= xlDialogs.Item(xlDialogPrinterSetup); (xlDialogPrinterSetup is the official name of the Excel- dialog.) >>Error: Unknown variable! (xlDialogPrinterSetup) If I try: xlDialog:= xlDialogs.Item(‘xlDialogPrinterSetup’); >>Error: Left Integer - Right Text If I try: variable: xlDialogPrinterSetup Integer xlDialog:= xlDialogs.Item(xlDialogPrinterSetup); >>Error: Error in extern components …! But, if I try: xlDialog:= xlDialogs.Item(1); Then the FileOpen- dialog appears (in Excel). But I can’t change the Item to another no. Application.Dialogs.Count in Excel returns 706 dialogs. Does anybody knows something about the mystery of the dialog/dialogs in ATTAIN Automation. Thanks in advance bye Andre

Hi thank you for reading my posting . Now I have it. With the following VBA-code you can ‘scan’ the dialogs in excel. Please choose always ‘Escape’. The message box after the dialog will show you a number. This number you need for excel - automation. The number I’m searching for was 9. >> xlApp.Dialogs.Item(9).Show; <<


Sub dialog()
Dim i, ende, txt, x
ende = 706
i = 1

Lauf1:
If i < ende Then
 ActiveCell.Value = i
 On Error Resume Next
  Application.Dialogs.Item(i).Show
 On Error GoTo 0
 
 x = MsgBox("Last Dialog!" & Chr(10) & "No. " & i & Chr(10) & "Next?", 4)
 If x = 6 Then
  i = i + 1
  GoTo Lauf1
 End If
End If

End Sub

bye Andre

quote:


Originally posted by Andre DDB: If I try: xlDialog:= xlDialogs.Item(xlDialogPrinterSetup); >>Error: Unknown variable! (xlDialogPrinterSetup)


xlDialogPrinterSetup is an integer variable with a constant value of 9. Therefore

 xlDialog:= xlDialogs.Item(9);
 

or better

 
Var
  xlDialogPrinterSetup : Integer;

BEGIN
  xlDialogPrinterSetup := 9;
  xlDialog:= xlDialogs.Item(xlDialogPrinterSetup);
 

should do the trick. Here is the List of Excel-Dialogs and their appropriate constant:


  xlDialogOpen = $00000001;
  xlDialogOpenLinks = $00000002;
  xlDialogSaveAs = $00000005;
  xlDialogFileDelete = $00000006;
  xlDialogPageSetup = $00000007;
  xlDialogPrint = $00000008;
  xlDialogPrinterSetup = $00000009;
  xlDialogArrangeAll = $0000000C;
  xlDialogWindowSize = $0000000D;
  xlDialogWindowMove = $0000000E;
  xlDialogRun = $00000011;
  xlDialogSetPrintTitles = $00000017;
  xlDialogFont = $0000001A;
  xlDialogDisplay = $0000001B;
  xlDialogProtectDocument = $0000001C;
  xlDialogCalculation = $00000020;
  xlDialogExtract = $00000023;
  xlDialogDataDelete = $00000024;
  xlDialogSort = $00000027;
  xlDialogDataSeries = $00000028;
  xlDialogTable = $00000029;
  xlDialogFormatNumber = $0000002A;
  xlDialogAlignment = $0000002B;
  xlDialogStyle = $0000002C;
  xlDialogBorder = $0000002D;
  xlDialogCellProtection = $0000002E;
  xlDialogColumnWidth = $0000002F;
  xlDialogClear = $00000034;
  xlDialogPasteSpecial = $00000035;
  xlDialogEditDelete = $00000036;
  xlDialogInsert = $00000037;
  xlDialogPasteNames = $0000003A;
  xlDialogDefineName = $0000003D;
  xlDialogCreateNames = $0000003E;
  xlDialogFormulaGoto = $0000003F;
  xlDialogFormulaFind = $00000040;
  xlDialogGalleryArea = $00000043;
  xlDialogGalleryBar = $00000044;
  xlDialogGalleryColumn = $00000045;
  xlDialogGalleryLine = $00000046;
  xlDialogGalleryPie = $00000047;
  xlDialogGalleryScatter = $00000048;
  xlDialogCombination = $00000049;
  xlDialogGridlines = $0000004C;
  xlDialogAxes = $0000004E;
  xlDialogAttachText = $00000050;
  xlDialogPatterns = $00000054;
  xlDialogMainChart = $00000055;
  xlDialogOverlay = $00000056;
  xlDialogScale = $00000057;
  xlDialogFormatLegend = $00000058;
  xlDialogFormatText = $00000059;
  xlDialogParse = $0000005B;
  xlDialogUnhide = $0000005E;
  xlDialogWorkspace = $0000005F;
  xlDialogActivate = $00000067;
  xlDialogCopyPicture = $0000006C;
  xlDialogDeleteName = $0000006E;
  xlDialogDeleteFormat = $0000006F;
  xlDialogNew = $00000077;
  xlDialogRowHeight = $0000007F;
  xlDialogFormatMove = $00000080;
  xlDialogFormatSize = $00000081;
  xlDialogFormulaReplace = $00000082;
  xlDialogSelectSpecial = $00000084;
  xlDialogApplyNames = $00000085;
  xlDialogReplaceFont = $00000086;
  xlDialogSplit = $00000089;
  xlDialogOutline = $0000008E;
  xlDialogSaveWorkbook = $00000091;
  xlDialogCopyChart = $00000093;
  xlDialogFormatFont = $00000096;
  xlDialogNote = $0000009A;
  xlDialogSetUpdateStatus = $0000009F;
  xlDialogColorPalette = $000000A1;
  xlDialogChangeLink = $000000A6;
  xlDialogAppMove = $000000AA;
  xlDialogAppSize = $000000AB;
  xlDialogMainChartType = $000000B9;
  xlDialogOverlayChartType = $000000BA;
  xlDialogOpenMail = $000000BC;
  xlDialogSendMail = $000000BD;
  xlDialogStandardFont = $000000BE;
  xlDialogConsolidate = $000000BF;
  xlDialogSortSpecial = $000000C0;
  xlDialogGallery3dArea = $000000C1;
  xlDialogGallery3dColumn = $000000C2;
  xlDialogGallery3dLine = $000000C3;
  xlDialogGallery3dPie = $000000C4;
  xlDialogView3d = $000000C5;
  xlDialogGoalSeek = $000000C6;
  xlDialogWorkgroup = $000000C7;
  xlDialogFillGroup = $000000C8;
  xlDialogUpdateLink = $000000C9;
  xlDialogPromote = $000000CA;
  xlDialogDemote = $000000CB;
  xlDialogShowDetail = $000000CC;
  xlDialogObjectProperties = $000000CF;
  xlDialogSaveNewObject = $000000D0;
  xlDialogApplyStyle = $000000D4;
  xlDialogAssignToObject = $000000D5;
  xlDialogObjectProtection = $000000D6;
  xlDialogCreatePublisher = $000000D9;
  xlDialogSubscribeTo = $000000DA;
  xlDialogShowToolbar = $000000DC;
  xlDialogPrintPreview = $000000DE;
  xlDialogEditColor = $000000DF;
  xlDialogFormatMain = $000000E1;
  xlDialogFormatOverlay = $000000E2;
  xlDialogEditSeries = $000000E4;
  xlDialogDefineStyle = $000000E5;
  xlDialogGalleryRadar = $000000F9;
  xlDialogEditionOptions = $000000FB;
  xlDialogZoom = $00000100;
  xlDialogInsertObject = $00000103;
  xlDialogSize = $00000105;
  xlDialogMove = $00000106;
  xlDialogFormatAuto = $0000010D;
  xlDialogGallery3dBar = $00000110;
  xlDialogGallery3dSurface = $00000111;
  xlDialogCustomizeToolbar = $00000114;
  xlDialogWorkbookAdd = $00000119;
  xlDialogWorkbookMove = $0000011A;
  xlDialogWorkbookCopy = $0000011B;
  xlDialogWorkbookOptions = $0000011C;
  xlDialogSaveWorkspace = $0000011D;
  xlDialogChartWizard = $00000120;
  xlDialogAssignToTool = $00000125;
  xlDialogPlacement = $0000012C;
  xlDialogFillWorkgroup = $0000012D;
  xlDialogWorkbookNew = $0000012E;
  xlDialogScenarioCells = $00000131;
  xlDialogScenarioAdd = $00000133;
  xlDialogScenarioEdit = $00000134;
  xlDialogScenarioSummary = $00000137;
  xlDialogPivotTableWizard = $00000138;
  xlDialogPivotFieldProperties = $00000139;
  xlDialogOptionsCalculation = $0000013E;
  xlDialogOptionsEdit = $0000013F;
  xlDialogOptionsView = $00000140;
  xlDialogAddinManager = $00000141;
  xlDialogMenuEditor = $00000142;
  xlDialogAttachToolbars = $00000143;
  xlDialogOptionsChart = $00000145;
  xlDialogVbaInsertFile = $00000148;
  xlDialogVbaProcedureDefinition = $0000014A;
  xlDialogRoutingSlip = $00000150;
  xlDialogMailLogon = $00000153;
  xlDialogInsertPicture = $00000156;
  xlDialogGalleryDoughnut = $00000158;
  xlDialogChartTrend = $0000015E;
  xlDialogWorkbookInsert = $00000162;
  xlDialogOptionsTransition = $00000163;
  xlDialogOptionsGeneral = $00000164;
  xlDialogFilterAdvanced = $00000172;
  xlDialogMailNextLetter = $0000017A;
  xlDialogDataLabel = $0000017B;
  xlDialogInsertTitle = $0000017C;
  xlDialogFontProperties = $0000017D;
  xlDialogMacroOptions = $0000017E;
  xlDialogWorkbookUnhide = $00000180;
  xlDialogWorkbookName = $00000182;
  xlDialogGalleryCustom = $00000184;
  xlDialogAddChartAutoformat = $00000186;
  xlDialogChartAddData = $00000188;
  xlDialogTabOrder = $0000018A;
  xlDialogSubtotalCreate = $0000018E;
  xlDialogWorkbookTabSplit = $0000019F;
  xlDialogWorkbookProtect = $000001A1;
  xlDialogScrollbarProperties = $000001A4;
  xlDialogPivotShowPages = $000001A5;
  xlDialogTextToColumns = $000001A6;
  xlDialogFormatCharttype = $000001A7;
  xlDialogPivotFieldGroup = $000001B1;
  xlDialogPivotFieldUngroup = $000001B2;
  xlDialogCheckboxProperties = $000001B3;
  xlDialogLabelProperties = $000001B4;
  xlDialogListboxProperties = $000001B5;
  xlDialogEditboxProperties = $000001B6;
  xlDialogOpenText = $000001B9;
  xlDialogPushbuttonProperties = $000001BD;
  xlDialogFilter = $000001BF;
  xlDialogFunctionWizard = $000001C2;
  xlDialogSaveCopyAs = $000001C8;
  xlDialogOptionsListsAdd = $000001CA;
  xlDialogSeriesAxes = $000001CC;
  xlDialogSeriesX = $000001CD;
  xlDialogSeriesY = $000001CE;
  xlDialogErrorbarX = $000001CF;
  xlDialogErrorbarY = $000001D0;
  xlDialogFormatChart = $000001D1;
  xlDialogSeriesOrder = $000001D2;
  xlDialogMailEditMailer = $000001D6;
  xlDialogStandardWidth = $000001D8;
  xlDialogScenarioMerge = $000001D9;
  xlDialogProperties = $000001DA;
  xlDialogSummaryInfo = $000001DA;
  xlDialogFindFile = $000001DB;
  xlDialogActiveCellFont = $000001DC;
  xlDialogVbaMakeAddin = $000001DE;
  xlDialogFileSharing = $000001E1;
  xlDialogAutoCorrect = $000001E5;
  xlDialogCustomViews = $000001ED;
  xlDialogInsertNameLabel = $000001F0;
  xlDialogSeriesShape = $000001F8;
  xlDialogChartOptionsDataLabels = $000001F9;
  xlDialogChartOptionsDataTable = $000001FA;
  xlDialogSetBackgroundPicture = $000001FD;
  xlDialogDataValidation = $0000020D;
  xlDialogChartType = $0000020E;
  xlDialogChartLocation = $0000020F;
  _xlDialogPhonetic = $0000021A;
  xlDialogChartSourceData = $0000021C;
  _xlDialogChartSourceData = $0000021D;
  xlDialogSeriesOptions = $0000022D;
  xlDialogPivotTableOptions = $00000237;
  xlDialogPivotSolveOrder = $00000238;
  xlDialogPivotCalculatedField = $0000023A;
  xlDialogPivotCalculatedItem = $0000023C;
  xlDialogConditionalFormatting = $00000247;
  xlDialogInsertHyperlink = $00000254;
  xlDialogProtectSharing = $0000026C;
  xlDialogOptionsME = $00000287;
  xlDialogPublishAsWebPage = $0000028D;
  xlDialogPhonetic = $00000290;
  xlDialogNewWebQuery = $0000029B;
  xlDialogImportTextFile = $0000029A;
  xlDialogExternalDataProperties = $00000212;
  xlDialogWebOptionsGeneral = $000002AB;
  xlDialogWebOptionsFiles = $000002AC;
  xlDialogWebOptionsPictures = $000002AD;
  xlDialogWebOptionsEncoding = $000002AE;
  xlDialogWebOptionsFonts = $000002AF;
  xlDialogPivotClientServerSet = $000002B1;

With best regards from Switzerland Marcus Fabian Edited by - fabian on 2002 Aug 15 18:50:20

Hallo Marcus, I missed you yesterday . But I found it by myself (with the VBA- Code). Two questions: Where is the list from? I didn’t find anything in the vba helpfile and in the net. If want to show ‘xlDialogSort’. With my code you will get 39. I’ve tested with Attain. It works. Your list says $00000027;. What is the difference? And what is with ‘$0000000C’? Isn’t integer, or? Marcus, please can you enlighten me? bye Andre

Andre, the values in Marcus’ list are hexadecimal values, so this equals the following decimal values: $27 = 2 x 16 + 7 = 39 $0C = 0 x 16 + 12 = 12 Torsten

quote:


Originally posted by Andre DDB: Your list says $00000027;. What is the difference? And what is with ‘$0000000C’? Isn’t integer, or? bye Andre


Edited by - todro on 2002 Aug 16 19:17:05

Torsten: Thank you! Andre

I must confess, I’m not very familar with hexadecimal values, but I can translate the values with Excel (hexindec). If somebody is interested in, I can post the translation. Thank you again, Marcus & Torsten. Now I have another problem: The xldialog is on the screen, but the Attain- code runs on and on. I stop the code with sleep() and a confirm(), but this is not a proper way. Does anybody know another solution to stop the Attain- code during showing an Excel- dialog. bye Andre