Archive
Posts Tagged ‘MS-Excel’
Radar Chart visualizing Cost of Living in select US Cities
2016/11/06
Leave a comment
Categories: Charts, e-commerce, Media
charting, MS-Excel
Have your VBA Add-in autogenerate menus for easier access to your macros
2016/07/22
Leave a comment
- Storing your macros in add-ins (.xlam) has many advantages over personal.xlsm and similar document locations.
- One disadvantage however is that an Add-in macro is not accessible through the Excel macros dialogue.
- The community recommends assigning shortcuts for easy access. I did that and went a couple of steps further using VBE extensibility to
- depending on scope
- public procedures on your end users’ computer to whom you distribute your Add-in
- and also non-private on the developer machine
- list your modules alphabetically
- list your macros alphabetically under your modules
- for each, find & assign a free (free within Excel only – short of assigned windows-wide shortcuts since in my current work environment, I am unable run tools that allow you to list these shortcuts) shortcut combination,
- automatically generate an Add-Ins menu of all that , to serve as a cheat sheet.
- depending on scope
- Code TBA
Categories: service-is-documenting, service-is-programming, sourcecode
add-ins, MS-Excel, ribbon, VBA
Try “Stop Collecting” with Office Clipboard for Runtime Error 1004 “Paste Method of Worksheet Failed”
2016/05/30
Leave a comment
PowerShell Script to convert your Testing Anywhere run logs into a Excel pivot table data source
2016/01/28
Leave a comment
- If confronted with a sizable Testing Anywhere test script codebase which has been marginally, but not substantially enhanced/cleaned up in several years while producing a barrage of automation errors daily,
- you may find that the run suite errors that Testing Anywhere logs automatically in its rlgx files are your best data source for monitoring and designing a plan of attack:
- Any oft-failing scripts should be put last during the daily run? how about length script needs to run?
- Any failing script parts could be modularized and during the daily run?
- any oft-failing scripts? E.g. here the top 8% of failing scripts have almost 30% of the errors.
- Any oft-failing approaches that might benefit from refactoring? Starting with which scripts? Main actions, then sub-actions:
- etc.
- Then this PowerShell script may help which
- extracts the non binary <runlog> items out of the binary rlgx files,
- and merges them into a single file
- which it wraps with an XML declaration and root level node that Excel can work with.
add-content -value '' -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 Get-childitem -path C:\td\testinganywhere\files\rlgx\arnold-pc1 | ? {$_.Extension -eq ".rlgx"} | % { $file = convertto-string $_.FullName $match = [regex]::Match($file,'\s+(.*)\s+',"SingleLine,IgnoreCase").value add-content $match -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 } add-content '' -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8
- Make this PowerShell script a Scheduled Task,
- So that you can auto-update said XML which you made the data source for your Excel monitoring/planning work book.
- The post-processing of the default error log messages that makes meaningful pivoting actually possible, is left as an exercise to the reader by Testing Anywhere
.
- The post-processing of the default error log messages that makes meaningful pivoting actually possible, is left as an exercise to the reader by Testing Anywhere
Categories: service-is-programming, service-is-testing
MS-Excel, ms-powershell, pivot-tables, rlgx, testing-anywhere, XML
How to split an Excel workbook into one file per sheet with VBA (reworked)
2015/09/24
1 comment
- There are other code snippets on stackoverflow.com, but I went with the top match in the Google search.
- Couldn’t get it to work (error # 424 Object required on xWS.copy in Excel 2013, with the VBA run from a separate utilities workbook) until I made these changes:
'TRP reworked http://www.extendoffice.com/documents/excel/628-excel-split-workbook.html Sub Workbook_Split() 'Updated by TRP 20150924 Dim xPath As String Dim myWorkSheet As Worksheet xPath = Application.ActiveWorkbook.Path 'the source workbook needs be saved first Application.ScreenUpdating = False Application.DisplayAlerts = False For Each xWS In ActiveWorkbook.Sheets 'trp: replaced ThisWorkbook.Sheets 'trp: replaced xWS.Copy 'toask: this fails with 424: object required Set myWorkSheet = xWS myWorkSheet.Copy 'If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet. Application.ActiveWorkbook.SaveAs FileName:=xPath & "\" & myWorkSheet.Name & ".xlsx" 'trp: replaced xWS.Name & ".xls" Application.ActiveWorkbook.Close False 'trp:savechanges:=False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Categories: service-is-programming
MS-Excel, VBA, worksheets
How to ease editing work in MS-Word by automating search/replace operations
2015/05/08
Leave a comment
- If you frequently have to edit documents according to a large number of editorial rules and regulations
- and if you can partially automate these edit operations (or at least highlight suspicious passages for human review) with Word’s search/replace,
- I can recommend an add-in that can automate even the repeated search/replace operations (like the 57 in the video below)
- and even help you manage your search/replace strings and regular expressions in a spreadsheet which it can load from:
- Greg Maxey’s VBA Find & Replace Word Add-in. See it in action (click for full size):
TwoThree Caveats: :- At this point, I cannot get the add-in to work only in Word 2010. Even if I lower Macro security and allow programmatic access to the VBA project, when trying to launch the add-in from the ribbon, Word 2013 complains: “The macro cannot be found or has been disabled due to your macro security settings”:
.
- The automation is only as good as your underlying search/replace operations. (Hint: “Some people, when confronted with a problem, think ‘I know, I’ll use regular expressions.’ Now they have two problems.”)
- I think I will refrain from search/replace during “Tracking changes” – as in the video – , and rather use “Compare documents” after the replace operations – too many quirks otherwise…
- At this point, I cannot get the add-in to work only in Word 2010. Even if I lower Macro security and allow programmatic access to the VBA project, when trying to launch the add-in from the ribbon, Word 2013 complains: “The macro cannot be found or has been disabled due to your macro security settings”:
Categories: service-is-documenting
2010, 2013, add-ins, automation, MS-Excel, MS-Word, regular-expressions, replacing, VBA
Doxygen config reformatted for Excel
2015/04/16
Leave a comment
- Apart from the almost 150 markup tags Doxygen 1.1. supports (not to mention HTML tags that are supported also),
- the Doxygen config file in version 1.8.8 has over 260 settings, many with complex interrelations.
- To facilitate working with so many options, especially when testing. I reformatted the default config file for import into Excel,
- where I can more easily sort, search and filter,
- and export columns L-R whenever I need to update the doxygen.config file:
Below is a live, downloadable view:
Categories: e-infrastructure, Spreadsheets
doxygen, MS-Excel
Line chart of GINI coefficients for select economies since 1980
2015/04/02
Leave a comment
Based on this Dataset from the World Bank, a bit more recent than the Wikipedia Chart currently.