newsman220

Thanks!

I'm doing some Outlook e-mail automation and have hit what appears to be an error in SciTE. It's flagging an object property as a keyword. Here's the code: Func _EmailResults($filepath, $emailaddress) $objOutlook = ObjCreate("Outlook.Application") $objMail = $objOutlook.CreateItem($olMailItem) With $objMail .To = $emailaddress .Subject = @UserName & " Project Forecast for " & @MON & "/" & @MDAY & "/" & @YEAR .Attachments.Add($filepath & ".xlsx") EndWith Local $signature = $objMail.HTMLBody $objMail.HTMLBody = " Weekly forecast" & $signature $objMail.Send() EndFunc The problem is line 5, the .To = $emailaddress. SciTE kicks that out with " error: missing separator character before keyword. .To ~^" So it's seeing the "To" as the AutoIT keyword "To" from "From.. To.. Next" and telling me I wrote it wrong. However, the program compiles without error and runs correctly. Does anyone know a way to exclude .To from the checking? And does anyone know how to file a bug against SciTE? I went through the Scintilla site to the SourceForge page and just could not find a way to submit a bug. Thanks, all.

This long, long function translates decimal Windows locale codes into human-readable names. I've added a wrapper to meet the Snippets guidelines. I wrote this when I was using WMI to query the Win32_OperatingSystem for, among other things, the OS language. Remember, these are the decimal codes, not the hex. The example returns English - United States. The list comes from: http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w- 7 Local $MyOSLanguage = 1033 ConsoleWrite(_OSLang($MyOSLanguage)) Func _OSLang($lang) Switch $lang Case 1078 Return "Afrikaans" Case 1052 Return "Albanian" Case 1118 Return "Amharic" Case 5121 Return "Arabic - Algeria" Case 15361 Return "Arabic - Bahrain" Case 3073 Return "Arabic - Egypt" Case 2049 Return "Arabic - Iraq" Case 11265 Return "Arabic - Jordan" Case 13313 Return "Arabic - Kuwait" Case 12289 Return "Arabic - Lebanon" Case 4097 Return "Arabic - Libya" Case 6145 Return "Arabic - Morocco" Case 8193 Return "Arabic - Oman" Case 16385 Return "Arabic - Qatar" Case 1025 Return "Arabic - Saudi Arabia" Case 10241 Return "Arabic - Syria" Case 7169 Return "Arabic - Tunisia" Case 14337 Return "Arabic - United Arab Emirates" Case 9217 Return "Arabic - Yemen" Case 1067 Return "Armenian" Case 1101 Return "Assamese" Case 2092 Return "Azeri - Cyrillic" Case 1068 Return "Azeri - Latin" Case 1069 Return "Basque" Case 1059 Return "Belarusian" Case 2117 Return "Bengali - Bangladesh" Case 1093 Return "Bengali - India" Case 5146 Return "Bosnian" Case 1026 Return "Bulgarian" Case 1109 Return "Burmese" Case 1027 Return "Catalan" Case 2052 Return "Chinese - China" Case 3076 Return "Chinese - Hong Kong SAR" Case 5124 Return "Chinese - Macau SAR" Case 4100 Return "Chinese - Singapore" Case 1028 Return "Chinese - Taiwan" Case 1050 Return "Croatian" Case 1029 Return "Czech" Case 1030 Return "Danish" Case 1125 Return "Divehi; Dhivehi; Maldivian" Case 2067 Return "Dutch - Belgium" Case 1043 Return "Dutch - Netherlands" Case 1126 Return "Edo" Case 3081 Return "English - Australia" Case 10249 Return "English - Belize" Case 4105 Return "English - Canada" Case 9225 Return "English - Caribbean" Case 2057 Return "English - Great Britain" Case 16393 Return "English - India" Case 6153 Return "English - Ireland" Case 8201 Return "English - Jamaica" Case 5129 Return "English - New Zealand" Case 13321 Return "English - Phillippines" Case 7177 Return "English - Southern Africa" Case 11273 Return "English - Trinidad" Case 1033 Return "English - United States" Case 12297 Return "English - Zimbabwe" Case 1061 Return "Estonian" Case 1080 Return "Faroese" Case 1065 Return "Farsi - Persian" Case 1124 Return "Filipino" Case 1035 Return "Finnish" Case 2060 Return "French - Belgium" Case 11276 Return "French - Cameroon" Case 3084 Return "French - Canada" Case 9228 Return "French - Congo" Case 12300 Return "French - Cote d'Ivoire" Case 1036 Return "French - France" Case 5132 Return "French - Luxembourg" Case 13324 Return "French - Mali" Case 6156 Return "French - Monaco" Case 14348 Return "French - Morocco" Case 10252 Return "French - Senegal" Case 4108 Return "French - Switzerland" Case 7180 Return "French - West Indies" Case 1122 Return "Frisian - Netherlands" Case 1071 Return "FYRO Macedonia" Case 2108 Return "Gaelic - Ireland" Case 1084 Return "Gaelic - Scotland" Case 1110 Return "Galician" Case 1079 Return "Georgian" Case 3079 Return "German - Austria" Case 1031 Return "German - Germany" Case 5127 Return "German - Liechtenstein" Case 4103 Return "German - Luxembourg" Case 2055 Return "German - Switzerland" Case 1032 Return "Greek" Case 1140 Return "Guarani - Paraguay" Case 1095 Return "Gujarati" Case 1037 Return "Hebrew" Case 1279 Return "HID (Human Interface Device)" Case 1081 Return "Hindi" Case 1038 Return "Hungarian" Case 1039 Return "Icelandic" Case 1136 Return "Igbo - Nigeria" Case 1057 Return "Indonesian" Case 1040 Return "Italian - Italy" Case 2064 Return "Italian - Switzerland" Case 1041 Return "Japanese" Case 1099 Return "Kannada" Case 1120 Return "Kashmiri" Case 1087 Return "Kazakh" Case 1107 Return "Khmer" Case 1111 Return "Konkani" Case 1042 Return "Korean" Case 1088 Return "Kyrgyz - Cyrillic" Case 1108 Return "Lao" Case 1142 Return "Latin" Case 1062 Return "Latvian" Case 1063 Return "Lithuanian" Case 2110 Return "Malay - Brunei" Case 1086 Return "Malay - Malaysia" Case 1100 Return "Malayalam" Case 1082 Return "Maltese" Case 1112 Return "Manipuri" Case 1153 Return "Maori" Case 1102 Return "Marathi" Case 2128 Return "Mongolian" Case 1104 Return "Mongolian" Case 1121 Return "Nepali" Case 1044 Return "Norwegian - Bokml" Case 2068 Return "Norwegian - Nynorsk" Case 1096 Return "Oriya" Case 1045 Return "Polish" Case 1046 Return "Portuguese - Brazil" Case 2070 Return "Portuguese - Portugal" Case 1094 Return "Punjabi" Case 1047 Return "Raeto-Romance" Case 2072 Return "Romanian - Moldova" Case 1048 Return "Romanian - Romania" Case 1049 Return "Russian" Case 2073 Return "Russian - Moldova" Case 1083 Return "Sami Lappish" Case 1103 Return "Sanskrit" Case 3098 Return "Serbian - Cyrillic" Case 2074 Return "Serbian - Latin" Case 1072 Return "Sesotho (Sutu)" Case 1074 Return "Setsuana" Case 1113 Return "Sindhi" Case 1115 Return "Sinhala; Sinhalese" Case 1051 Return "Slovak" Case 1060 Return "Slovenian" Case 1143 Return "Somali" Case 1070 Return "Sorbian" Case 11274 Return "Spanish - Argentina" Case 16394 Return "Spanish - Bolivia" Case 13322 Return "Spanish - Chile" Case 9226 Return "Spanish - Colombia" Case 5130 Return "Spanish - Costa Rica" Case 7178 Return "Spanish - Dominican Republic" Case 12298 Return "Spanish - Ecuador" Case 17418 Return "Spanish - El Salvador" Case 4106 Return "Spanish - Guatemala" Case 18442 Return "Spanish - Honduras" Case 2058 Return "Spanish - Mexico" Case 19466 Return "Spanish - Nicaragua" Case 6154 Return "Spanish - Panama" Case 15370 Return "Spanish - Paraguay" Case 10250 Return "Spanish - Peru" Case 20490 Return "Spanish - Puerto Rico" Case 1034 Return "Spanish - Spain (Traditional)" Case 14346 Return "Spanish - Uruguay" Case 8202 Return "Spanish - Venezuela" Case 1089 Return "Swahili" Case 2077 Return "Swedish - Finland" Case 1053 Return "Swedish - Sweden" Case 1114 Return "Syriac" Case 1064 Return "Tajik" Case 1097 Return "Tamil" Case 1092 Return "Tatar" Case 1098 Return "Telugu" Case 1054 Return "Thai" Case 1105 Return "Tibetan" Case 1073 Return "Tsonga" Case 1055 Return "Turkish" Case 1090 Return "Turkmen" Case 1058 Return "Ukrainian" Case 0 Return "Unicode" Case 1056 Return "Urdu" Case 2115 Return "Uzbek - Cyrillic" Case 1091 Return "Uzbek - Latin" Case 1075 Return "Venda" Case 1066 Return "Vietnamese" Case 1106 Return "Welsh" Case 1076 Return "Xhosa" Case 1085 Return "Yiddish" Case 1077 Return "Zulu" EndSwitch EndFunc

water, you nailed the issue. I was using a blank error-handling function to simply absorb the problem. When I changed it to give me a debug message it flagged an error when the cell was open. I added a global $COMError variable; the error-handler sets it to true. When the 5-second workbook comparison cycle comes around again, the list-comparison function checks for a COM connection by attempting to enumerate the workbooks. It sets $COMError to False if the count is > 0. While $COMError is True the purge function won't run at all. The script also ignores left and right-clicks whlie $COMError = True. There is an edge condition where the COM error can exist, but the script has not reported it yet. To handle that, the function which enumerates a workbook's worksheets tests for the connection before building the dialog box, by trying to count the selected workbook's worksheets. Any result < 1 and the function returns silently. In this case, I'm not trying to automate the Excel worksheets. I'm working around the clumsy Windows 7 method of grouping them all into a single taskbar item. I'm paid to manage projects; I have about 30 running right now, with many-paged Excel spreadsheets as project logs for each one. I can have a dozen open at a time. So I really just wanted to make a quick-picker that sits on the side of my second monitor. This method should be adaptable for people with lots of Word files open, too. Possibly the next extension to this would be to have it monitor my Projects folder & subfolders for Excel workbooks with "project log" in the name and present those, too, but in a different color in the listview. That should be a nice challenge for an evening. Thanks for the help!

Handy, that. Unfortunately, I'm using the script to pop them up so I can edit them. It's just an odd situation, and I'm wondering if it's a problem with the Excel COM model. Thanks for the pointer, though. I'll use that at some point.

I was faced with a similar problem parsing ugly SAP date output. I chopped the string into pieces, used _DateDiff('s', "1970/01/01 00:00:00", $temp2[3] & "/" & $temp2[1] & "/" & $temp2[2] & " 00:00:00") to generate epoch time in place of the string, sorted on the epoch time and turned that back into a readable date with _DateAdd('s', $result, "1970/01/01 00:00:00") Above, $temp is the array representing the SAP date blown apart on "/" and rearragned. $result is the result of sorting the array and using _ArrayPop to retrieve the date I wanted. In this case, it was the largest epoch time, i.e. furthest in the future. . I had to trim the HH:MM:SS off the end, but that's a simple StringTrim.

I've written a script to list all my open Excel workbooks, so I can switch to them easily. (I run with a lot of workbooks open) The script pops the workbook to the front with a left click. Right-click opens a dialog of all that workbook's sheets, so I can go directly to that sheet. The script checks every 5 seconds to see if the open list has changed. One thing I've noticed - if I click into a worksheet cell so the cursor is there, the script blanks the list. The same is true if I'm clicked into the edit box at the top of the worksheet. Basically, any kind of cell editing. This (the blanking) is the expected behavior when A) there are no worksheets open; the Excel object in the script isn't an object when the purge-workbook-list function runs as part of the list update. When I leave the cell, the list is back within 5 seconds, as I would expect. I've noticed Excel won't open or close a workbook when a cell is in the same condition. Click out of the cell, and the actions happen. This is Excel 2007. I'm wondering if anyone else has noticed this. If so, has anyone programmed around it? A lot of what I'm doing is with COM directly, e.g. $objExcel.Workbooks.Count, although I'll use a function when convenient. Thanks

This is fantastic. I'm using ListViews to hold the results of WMI queries, and this is the ideal way to get them out to a spreadsheet. Thanks for giving this to the community!

That does work, thank you. That will be quite handy. Now I'm having trouble attaching to the existing worksheet. I'm using a simple test program. #include $ojbOpen = _ExcelBookOpen("D:UsersjryanDropboxAvidProjectsKLRUKLRU Upgrade Project Log.xlsx", 0) ;$ojbOpen = _ExcelBookAttach("D:UsersjryanDropboxAvidProjectsKLRUKLRU Upgrade Project Log.xlsx") ;If @error Then ; MsgBox(0,"Closed","The file is not open.") ;Else ; MsgBox(0,"Open","The file is open.") ;EndIf If $ojbOpen.ActiveWorkbook.ReadOnly Then MsgBox(0,"Open","It's already open") _ExcelBookClose($ojbOpen) $ojbOpen = _ExcelBookAttach("D:UsersjryanDropboxAvidProjectsKLRUKLRU Upgrade Project Log.xlsx") If @error Then MsgBox(0,"Error","Unable to attach to open worksheet - error " & @error) Else MsgBox(0,"CLosed","It's not open") MsgBox(0,"Name",$ojbOpen.ActiveWorkbook.FullName) MsgBox(0,"Name",$ojbOpen.ActiveWorkbook.Name) EndIf $ojbOpen.Visible = True $ojbOpen.ActiveWorkbook.Sheets(4).Select $ojbOpen.ActiveSheet.Columns(2).Insert() MsgBox(0,"Killing time","Just holding the program open") _ExcelBookSave($ojbOpen) _ExcelBookClose($ojbOpen) Which works when the workbook starts closed, but not when it's open. I get a error: D:UsersjryanDropboxMy AutoITSite ProfilerOpenBooksTest.au3 (23) : ==> The requested action with this object has failed.: $ojbOpen.Visible = True $ojbOpen.Visible = True^ ERROR My testing correctly detects whether the workbook is open or not. But then I close the instance I opened, and try to attach to the existing instance, and it does not work. Where am I going off course here? Thanks.

Hello, all. I'm going round and round with this one. I have a number of scripts which gather data, open Excel files the user chooses, write date, set formatting, save the worksheet and close it. This only works if the workbook is closed. I understand this is a known issue. I test for the worksheet being open by seeing if I can open the file for writing, then warn the user. I would like to take the scripts up a notch, and let the user choose from worksheets which are already open. I understand _ExcelBookAttach() will let me access those. The question is, how can I get a list of what workbooks are open? I've tried using COM with ObjGet() to get the Application object and list names, but the script keeps failing, saying "The requested action with this object has failed" and pointing to a piece of my COM code. I've tried different things, so the failure point keeps changing. Does anyone have any code examples which can point me down the right direction? The MSDN articles on COM are semi-helpful, but they assume I have more knowledge than I do, and that I'm coding in Visual Basic.

An update. I was not able to get the conditional formatting to use the correct relative reference for the comparison cell. I'd tell it =$B43, and the rule in the spreadsheet would be =$B4. I'd run it again and get different results. I didn't spot a pattern. That was when I was adding data to an existing sheet, tacking my results on below any filled cells. I switched to RC references in the comparison, and it worked. Here's the final code snippet: If $SAPDonglePosition Then Local $endrow = UBound($arOutput) With $workbook.ActiveSheet.Range("$" & $SAPDonglePosition & "$" & $startrow + 1 & ":" & "$" & $SAPDonglePosition & "$" & $startrow + $endrow - 1).FormatConditions.Add(1, 4, "=RC[-1]") .Font.Colorindex = 30 .Interior.ColorIndex = 22 EndWith EndIf

With that nudge, I got it working. I changed it back to cell-based and used inequality as the basis for the test. Oddly, the rule still changed the second digit. If $SAPDonglePosition Then With $workbook.ActiveSheet.Range("$" & $SAPDonglePosition & "$2:" & "$" & $SAPDonglePosition & "$200").FormatConditions.Add(1, 4, "=$B1"); & Chr(Asc($SAPDonglePosition)-1) & "2") .Font.Colorindex = 30 .Interior.ColorIndex = 22 EndWith EndIf So instead of understanding it, I took the black box approach and changed the digit to 1 instead of what I thought it should be, 2. With that, everything worked. I suspect because I'm applying my rule to start at the second row instead of the first, Excel compensated for me and added one to the relative row reference. If I get time, I may test on that. I still need to change the code back to let it figure out the correct position, instead of hardcoding it. Thanks for your help, Juvigy.

Good evening. I'm working with a script to pull in data from text files and a web query page, put them in Excel, and apply some conditional formatting to highlight problems in the data. Everything is working except trying to create a conditional rule to compare 2 columns. When they differ, one should get a red background. I'm using a similar rule to highlight another column based on whether the cell contains "Good" or "Bad" and that works. What appears to be happening is when I use COM to create a reference such as =B2, the worksheet shows the rule as having =D3. I'm not sure where that's coming from. Here's a chunk of the code I'm using. If $SAPDonglePosition Then With $workbook.ActiveSheet.Range("$" & $SAPDonglePosition & "$2:" & "$" & $SAPDonglePosition & "$200").FormatConditions.Add(2, 5, "=C2 <> B2") .Font.Colorindex = 30 .Interior.ColorIndex = 22 EndWith EndIf $SAPDonglePosition is either the letter of the column that data's in, or the value False. I've tried this a number of ways, including having the rule cell-based instead of formula-based. In this instance, the "=C2<>B2" comes out as "=E3<>D3" which puts it two columns to the right and one column down. I'm seriously at a loss as to how that happens. Can anyone spot where I'm going wrong?