Archive

Archive for the ‘office-software’ Category

VBA script to more easily examine the properties of your Content Control-based forms

  1. The routine loops through the Content Controls and outputs properties as text into the VBA-Editor immediate window,
  2. from where it can be easily converted into an Excel-table which makes it easy t o spot and mark inconsistencies and outright oversights, like so: image
Sub ccPropertiesPrint()
On Error Resume Next
Dim strHeadings, strProps As String
Dim count As Integer
Dim response

strHeadings = strHeadings & "~" & "count"
strHeadings = strHeadings & "~" & "Tag"
strHeadings = strHeadings & "~" & "Title"
strHeadings = strHeadings & "~" & "Type"
strHeadings = strHeadings & "~" & "DefaultTextStyle"
strHeadings = strHeadings & "~" & "Application"
strHeadings = strHeadings & "~" & "BuildingBlockCategory"
strHeadings = strHeadings & "~" & "BuildingBlockType"
'strHeadings = strHeadings & "~" & "Checked"
'strHeadings = strHeadings & "~" & "Creator"
'strHeadings = strHeadings & "~" & "DateCalendarType"
'strHeadings = strHeadings & "~" & "DateDisplayFormat"
'strHeadings = strHeadings & "~" & "DateDisplayLocale"
'strHeadings = strHeadings & "~" & "DateStorageFormat"
'strHeadings = strHeadings & "~" & "DropdownListEntries"
strHeadings = strHeadings & "~" & "ID"
strHeadings = strHeadings & "~" & "LockContentControl"
strHeadings = strHeadings & "~" & "LockContents"
strHeadings = strHeadings & "~" & "MultiLine"
'strHeadings = strHeadings & "~" & "Parent"
strHeadings = strHeadings & "~" & "ParentContentControl"
strHeadings = strHeadings & "~" & "PlaceholderText"
strHeadings = strHeadings & "~" & "Range"
strHeadings = strHeadings & "~" & "ShowingPlaceholderText"
strHeadings = strHeadings & "~" & "Temporary"
'strHeadings = strHeadings & "~" & "XMLMapping"
Debug.Print strHeadings
  If ActiveDocument.ContentControls.count > 0 Then
    For Each oCC In ActiveDocument.ContentControls
    'debug
    'If oCC.Type = 9 Then 'debug: repeating
count = count + 1
If count = 50 Then
'dim response = vbmsgboxresponse
    Exit For
End If
    
' useless cannot access immediate window If count = 15 Then response = MsgBox("paused", vbOKOnly) End If
strProps = "" 'start over
strProps = strProps & "~"
strProps = strProps & count 'CStr(count)
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Tag, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Title, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Type, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.DefaultTextStyle, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Application, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.BuildingBlockCategory, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.BuildingBlockType, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.Checked, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.Creator, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateCalendarType, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateDisplayFormat, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateDisplayLocale, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DateStorageFormat, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.DropdownListEntries.count, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.ID, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.LockContentControl, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.LockContents, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.MultiLine, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.Parent, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.ParentContentControl, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.PlaceholderText, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Range, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.ShowingPlaceholderText, Chr(13), "#"), Chr(10), "#"))
strProps = strProps & "~"
strProps = strProps & CStr(Replace(Replace(oCC.Temporary, Chr(13), "#"), Chr(10), "#"))
'strProps = strProps & "~"
'strProps = strProps & CStr(Replace(Replace(oCC.XMLMapping, Chr(13), "#"), Chr(10), "#"))
Debug.Print vbCrLf & strProps & vbCrLf
'End If 'oCC.Type=9  then 'debug: repeating
 Next
 End If
End Sub

Has the “text effects” cheese been moved only in Word 2013 styles?

Used to have its own separate button – don’t despair, it is still there, now under the “Format” button:

word-styles-text-effects-

How to get Square brackets (and hide comments) with ISO690 in Word 2013 bibliography styles

2014/09/14 2 comments
  1. Lots of people online seem to be looking for square brackets with citations in ISO690 style in Word 2013, but having no luck with getting the Bibliography XSL  for older Word versions to work. Trying to edit the old XSL still results in it not loading into the MS-Word Citation Style dropdown.
  2. What is needed is a way to parse the XSL and debug load errors. In the meantime… Smiley
  3. I had better luck with starting from the current Word2013 ISO style. If you stream Office365, this is now in %appdata%\Microsoft\Templates\LiveContent\15\Managed\Word Document Bibliography Styles
    1. Puzzlingly, there is also a %appdata%\Microsoft\Bibliography\Style which some of your edited files get copied to – go figure….
    2. The ISO690 file  I based my variation on is called : TC102851224[[fn=iso690nmerical]].xsl
    3. Copy this file to  %appdata%\Microsoft\Templates\LiveContent\15\User\Word Document Bibliography Styles\
    4. Open it with a text editor (I use NotePad++).
    5. Change “Openbracket” section like so: And the corresponding for closebracket
      <!– trp:   –>
      [
    6. Same principle change for the corresponding for “Closebracket
      1. Lst time I carelessly introduced printing space characters before my closing brackets – just copy the leading chars from a working XML line if you run into this problem.
  4. I also needed to not print “Comment”-field of the source in my bibliography”
    1. Search for:
    2. Comment out the “print”-action inside (easier than changing each bibliographgy type):<!– trp:   
      –>
  5. Change the style name. MS-Word 2013 uses “StyleNameLocalized” instead of “StyleName”, so I added a qualifier to each localized name within the test:

    ISO 690YOURNAMEHERE

  6. Restart MS-Word, and with luck, your styles will show in the ribbon References section style dropdown: image. Apply them (using F9):image
  7. Download: TC102851224[[fn=iso690nmericalsquare0comments]]

How to open multiple windows in OneNote 2013 on Windows 8 (desktop)

CTRL-M still works… Reason I am jotting this down: On Windows 7, I used to open multiple windows by going through the OneNote program icon in the Start bar. But on Windows 8. the OneNote tile from “Tile world” just goes back to the last OneNote window. (As always: Live and learn.)

Categories: office-software Tags:

How to get dkRandomdata to run on 64-bit Excel

  1. Dick Kusleika made this very useful random data generator for Excel (more random data sources I plan to list here).
  2. To get this to run on 64-bit Excel 2013, I need to make a few quick changes in the source (which will break on 32-bit Excel):
    1. First, the usual “must be updated for use on 64-bit systems”: , fixed by this (but note the 1st line):
      Private mlParentPtr As LongPtr 'trp:Long 

       

      #If VBA7 Then 

       

      Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ 
      	(dest As Any, Source As Any, ByVal bytes As Long) 

       

      #Else 

       

      Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
      (dest As Any, Source As Any, ByVal bytes As Long) 

       

      #End If 
    2. Then, to fix the various “Type mismatch” errors , e.g. at , change Lomg into PtrLong: in these locations:
      1. above: Private mlParentPtr As LongPtr 'trp:Long 
      2. Private Function ObjFromPtr(ByVal pObj As LongPtr) As Object 
        'trp Private Function ObjFromPtr(ByVal pObj As Long) As Object 
  3. Then I am finally getting to the xmlDoc As MSXML2.DOMDocument error on Windows 8.1, but that has been solved in the comments here earlier this year (also for Windows 8.1, but apparently with Excel 32-bit): I changed MSXML2.DOMDocument to MSXML2.DOMDocument60 where I could:
    1. Dim xmlDoc As MSXML2.DOMDocument60 'trp: Dim xmlDoc As MSXML2.DOMDocument 
    2. WriteToFile: 
      
      
      
      1. Dim xmlDoc As MSXML2.DOMDocument60 
        'here it is already fixed? Dim xmlDoc As MSXML2.DOMDocument6 
      2. Dim xmlStyle As MSXML2.DOMDocument60 
      3. Dim xmlOutput As MSXML2.DOMDocument60 

    3. LoadFromFile(): Dim xmlDoc As MSXML2.DOMDocument60 
      'trp: Dim xmlDoc As MSXML2.DOMDocument 
    4. testxml() (looks like the XML issue has been worked on since the comments): 
      
      
      
      1. Dim xmlDoc As MSXML2.DOMDocument60 
        	'trp: Dim xmlDoc As MSXML2.DOMDocument 
      2. Set xmlDoc = New MSXML2.DOMDocument60 
        	'trp: New MSXML2.DOMDocument 

    5. test_writetofile(): 
      
      
      
      1. Set xmlDoc = New MSXML2.DOMDocument60 
        'here it is already fixed, and here 
      2. Set xmlStyle = New MSXML2.DOMDocument60 
      3. Set xmlOutput = New MSXML2.DOMDocument60 

  4. Finally, if you have trouble getting the add-in buttons to show in Excel 2013 ribbon, close all workbooks, and you may (but I did not) have to restart Excel (KB2761240):
    1. image
    2. Viola. And I sure like me some Seinfeld references in my sample data…. Smiley 

How to fix “Print to OneNote”, “OneNote cannot find a page on which to insert your printout”

2014/07/16 2 comments
  1. This error kept popping up for me on OneNote 2013, but I gather it is a classic.
  2. I should have just taken the error message seriously and literally: Could I do better than OneNote and find a page where OneNote says it cannot?
  3. First, to find out where I and OneNote are supposed to be looking for this page, go to File / Options/ Send To OneNote and check which “print to “location you have set.
  4. This one here looks obviously suspicious (and does not exist – one would wish still when this location goes out of scope this could be flagged with a more transparent warning):
  5. image
  6. Change the location :
  7. image
  8. to one that actually exists:
  9. image
  10. or better maybe, for lack of a more transparent error checking if page goes out of scope, chose one of the other,  hopefully more robust options: image

MS-OneDrive “Get link” incompatible with MS-Word “Always create backup copy”, use MS-OneDrive versioning instead

  1. Symptoms:
    1. If I edit my local MS-OneDrive copy with MS-Word, my collaborators lose access to the most recent copy via the link I shared with them.
    2. Worse, the new file MS-Word generates when the backup is created, won’t get automatically synched with MS-OneDrive, and no sync error seems to get flagged in Explorer.
  2. Root cause (presumably):
    1. When saving in MS-Word, I can see my focus moving to the backup fileclip_image001
    2. MS-Word, when creating a backup of the original file, actually rather creates a “fore-up”: The original file gets renamed (“Backup of…”) and the recent changes get copied into a new file.
    3. The MS-OneDrive link keeps pointing to the old file “Backup of…”, “when after (which one can see normally, if the user renames a file manually, is a desirable feature)
    4. Workaround: I managed to manually upload the files that got out off synch.
  3. Solution:
    1. Turn MS-Word backup off, it is not compatible with MS-OneDrive under File / Options /Advanced / section: Save: clip_image002
    2. Rather, rely on the MS-OneDrive versioning.

How to bind files to PDF with eXpert PDF Creator

  1. I tried PDF Editor (mind you, old version! 6 which I still have around from a promotion – nice software, though) first, including its help.
  2. Use Batch Creator instead,
  3. uncheck “Create multiple Documents”
  4. then you will be prompted for a (new) filename for the bound files.
  5. expert-pdf-editor
  6. Then click the “save” icon  in the upper right menu.