The trick:
When you open the workbook again, the last cell of the data should be the last cell on the worksheet.
Which means that the “Last cell” only changes after a reload.
–jeroen
via Locate and reset the last cell on a worksheet.
Archive for the ‘Office’ Category
Locate and reset the last cell on a worksheet
Posted by jpluimers on 2015/08/19
Posted in Excel, Office, Power User | Leave a Comment »
Excel: replace function is named SUBSTITUTE – via: Stack Overflow
Posted by jpluimers on 2015/08/03
Thanks DarkAjax for answering this:
what you’re looking for is
=SUBSTITUTE(A2,"Author","Authoring")Will substitute Author for Authoring without messing with everything else.
I always forget that SUBSTITUTE is a synonym for replace.
There are REPLACE and REPLACEB, but these replace content of a cell, not of a string.
–jeroen
via Excel: replace part of cell’s string value – Stack Overflow.
Share this:
Posted in Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User | Leave a Comment »
Download link for Office 2013 Language Pack Options
Posted by jpluimers on 2015/07/20
In case I need to re-download some language related Office 2013 things again: Office 2013 Language Pack Options.
–jeroen
Share this:
Posted in Excel, Office, Office 2013, Outlook, Power Point, Power User, Word | Leave a Comment »
Multiple instances of Word 2013 launching when opening files from Explorer window
Posted by jpluimers on 2014/09/12
If you ever have this problem, check if the [WayBack] ddeexec key word opening Word documents has a [REM_DDE_EXEC] prepended.
If so: remove it.
Background
ddeexec is used by the Windows Explorer to open a file through DDE (because that usually is a lot faster than starting a new proces) as explained by [WayBack] Taming Microsoft Word 2007’s File Associations and Document Windows, Part One — Within Windows, which – since it is now only in the WayBack machine – I quote in full:
One of the gripes Paul had was with the z-order of his open windows being tampered with when opening two (or more) Microsoft Word documents. At first I thought of saying, what the hell are you talking about Paul, but resorted to the less abrasive: What?
Paul explained that if you open a Word document, minimize it, open an Excel spreadsheet, then finally open another document, both Word windows come to front, preventing you from ALT-Tab’ing to the spreadsheet you were working in. Come again, right?
Okay, here’s a video to make things a little easier to understand.
Get the latest Flash Player to see this player.
Given this appears to be an issue with the invocation of Word through a file type association (i.e. double-clicking), I jumped into HKEY_CLASSES_ROOT\Word.Document.12\shell\Open\command to check out the file association keys and parameters. The default value was set to:
C:\Program Files (x86)\Microsoft Office\Office12\WINWORD.EXE” /n /dde
As per KB210565, /n instructs Word to starts a new instance of itself with no document open (would normally be a blank page). /dde, sadly undocumented, instructs Word to fire up its “DDE server”. Seeing as there is no %1 argument here, you’re probably wondering how Word opens the document you double-clicked…
Back in the 16-bit computing era, an inter-process communication method called Dynamic Data Exchange (DDE) was created, with the purpose of allowing data to be shared between applications. Today, with the advent of technologies such as COM and OLE, DDE isn’t very useful, but you’ll still find it in use deep within the bowels of Windows. You can read more about DDE on MSDN, Wikipedia, and on Raymond Chen’s blog.
Alright, so to instruct the shell that it must speak old DDE tongue with Word, an additional key must be present: HKEY_CLASSES_ROOT\Word.Document.12\shell\Open\ddeexec. This key, and the values beneath, instruct the shell how to inform Word, via DDE, that the user is trying to open a document. You’ll notice the default value for this key has FileOpen(“%1”), an instruction that merely simulates clicking File –> Open.
In layman’s terms, here’s what happens in Paul’s case. Bear with me folks.
First document invocation:
- Paul double-clicks a .docx file
- The shell (Windows Explorer) checks the registry for information on dealing with the ‘open’ action, notices ddeexec key, and goes down the DDE speak route
- The shell sends out a broadcast asking for someone to step forward and handle Word requests
- A DDE server accepting Word requests doesn’t respond (as it’s not running), therefore the shell fires up a new instance of a DDE server using the command value (i.e.
winword /n /dde)- The shell sends the FileOpen(%1) instruction to the new instance of Word.
- Document opens.
- Paul minimizes the document and opens other windows…
Second document invocation:
- Paul double-clicks a .docx file
- The shell (Windows Explorer) checks the registry for information on dealing with the ‘open’ action, notices ddeexec key, and goes down the DDE speak route
- The shell sends out a broadcast asking for someone to step forward and handle Word requests
- A DDE server accepting Word requests responds (Paul’s previous document).
- The shell sends the
FileOpen(%1)instruction to the existing instance of Word.- The existing instance of Word brings its window to front to handle the FileOpen instruction
- Paul screams.
Make sense now?
Same for [WayBack] Taming Microsoft Word 2007’s File Associations and Document Windows, Part Two – Within Windows:
Due to the way Word was designed, there doesn’t appear to be an easy solution to preserve the use of the DDE command and keep window z-order intact (on our end). In Connect lingo, this would have probably been marked WILLNOTFIX. Thankfully, we can tweak the registry a bit to alter the open behavior, therefore making Paul Thurrott happy.
Here is some very important information I’m required to inform you about:
- Each document opened will now spawn a new winword.exe process.
- Changing behavior for other actions (e.g. print) is out of this guide’s scope
- Changing behavior for other applications (e.g. Powerpoint, Excel) is out of this guide’s scope.
- Backing up the registry, undoing the changes, or other otherwise covering your ass is out of this guide’s scope.
Okay, let’s dig in! First, let’s brush up on some registry terminology with a cleverly produced picture.
That committed to non-pageable memory, let’s continue by altering the open behavior for legacy documents (.doc files)…
- With an elevated registry editor, navigate to HKEY_CLASSES_ROOT\Word.Document.8\shell\Open key.
- Rename the ddeexec sub-key to ddeexec.disabled.
- Navigate to the HKEY_CLASSES_ROOT\Word.Document.8\shell\Open\command key and rename the command value to command.disabled. Do not confuse the command value with the command key!
- Double-click the (Default) value and append “%1” (including quotes)to the end of the data string.
- Verify that legacy documents open in a new process upon every invocation.
… and now lets alter the open behavior for new documents (.docx files) …
- With an elevated registry editor, navigate to HKEY_CLASSES_ROOT\Word.Document.12\shell\Open key.
- Rename the ddeexec sub-key to ddeexec.disabled.
- Navigate to the HKEY_CLASSES_ROOT\Word.Document.12\shell\Open\command key and rename the command value to command.disabled. Do not confuse the command value with the command key!
- Double-click the (Default) value and append “%1” (including quotes)to the end of the data string.
- Verify that new documents open in a new process upon every invocation.
Okay, what did we just do? We simply disabled the DDE-related keys (just in case you need to restore them) and altered the launch command to include the document, you double-click, as a command line parameter, since the DDE FileOpen command will no longer be sent. Easy!
For those looking to automate this process, say for an entire enterprise-wide deployment, cough, you can use my PowerShell script. Just paste it into the PowerShell window and pray you don’t see red.
The PowerShell Script
[WayBack] uncouple_dde_word.txt:
New-PSDrive -Name HKCR -PSProvider Registry -Root HKEY_CLASSES_ROOT
Rename-Item -Path HKCR:\Word.Document.8\shell\Open\ddeexec -NewName ddeexec.disabled
Rename-Item -Path HKCR:\Word.Document.12\shell\Open\ddeexec -NewName ddeexec.disabled
Rename-ItemProperty -Path HKCR:\Word.Document.8\shell\Open\command -Name command -NewName command.disabled
Rename-ItemProperty -Path HKCR:\Word.Document.12\shell\Open\command -Name command -NewName command.disabled
Set-ItemProperty -Path HKCR:\Word.Document.8\shell\Open\command\ -Name "(default)" `
((Get-ItemProperty -Path HKCR:\Word.Document.8\shell\Open\command\)."(default)" + " ""%1""")
Set-ItemProperty -Path HKCR:\Word.Document.12\shell\Open\command\ -Name "(default)" `
((Get-ItemProperty -Path HKCR:\Word.Document.12\shell\Open\command\)."(default)" + " ""%1""")
–jeroen
via: [WayBack] Multiple instances of Word launching when opening files from Explorer window.
Share this:
Posted in Office, Power User, Word | 4 Comments »
Some notes on Word automation and showing Bookmarks
Posted by jpluimers on 2014/08/27
Though I’ve done this automation in Delphi, this applies to automation from any development platform. In this particular project, the documents used Bookmarks. Those have changed over time, so has the Word support for it.
From Word 2000 until Word you could disable the showing of Bookmarks by setting the ShowBookmarks property to False like this:
<br />ActiveDocument.ActiveWindow.View.ShowBookmarks := False;<br />
Well, when doing this, Office 2013 can throw an error like this:
EOleException: The ShowBookmarks method or property is not available because this command is not available for reading
ErrorCode: 0x800A11FD
Source: Microsoft Word
HelpFile: wdmain11.chm
The full error message does not give any useful search results. But a partial search finds a Word 2013 issue towards the top of the results:
sometimes Words opens the document in Reading layout. Reading layout does not allow all operations in Word 2013.
If a document is protected, and you try to change something you should not, you get an error message like this:
This method or property is not available because the document is a protected document.
Usually, Cindy Meister is very accurate. However this time here code
...ActiveWindow.View = wdPrintView
should have been like
...ActiveWindow.View.Type = wdPrintView
Of course you also have to save/restore this property while you are enabling the ShowBookmarks property.
Share this:
Posted in Delphi, Delphi XE2, Delphi XE3, Delphi XE4, Development, Office, Office 2000, Office 2003, Office 2007, Office 2010, Office 2013, Office Automation, Power User, Software Development, Word | Leave a Comment »
Some notes on Word automation and protected documents
Posted by jpluimers on 2014/08/26
Though I’ve done this automation in Delphi, this applies to automation from any development platform. In this particular project, I had to update Word documents. That is fine, unless your documents are protected. You can find out if a document is protected by probing the ProtectionType property of a Document object.
If a document is protected, and you try to change something you should not, you get an error message like this:
This method or property is not available because the document is a protected document.
If you have control of the documents or templates involved, then you can take the recommended steps from Document Handling with protected Microsoft Office Word Template:
Microsoft Office Word offers the possibility to enable document protection only to certain sections of a document. So you can place Bookmarks used by Dynamics AX in a section that is kept unprotected and the Form Controls in a section where Document Protection is enabled.
If you don’t, then you have to check for protection, unprotect, do the modifications, then re-protect the document. Read the rest of this entry »
Share this:
Posted in Delphi, Delphi XE2, Delphi XE3, Delphi XE4, Development, Office, Office 2000, Office 2003, Office 2007, Office 2010, Office 2013, Office Automation, Power User, Software Development, Word | Leave a Comment »
Word API Documents collection documentation: moved and changes with the introduction of Word 2013
Posted by jpluimers on 2014/08/05
I’ve done a bit of WinWord automation and came across different locations for the API:
New Style:
- Word 2013: Documents Object (Word) – http://msdn.microsoft.com/en-us/library/office/ff840891(v=office.15).aspx
- Word 2010: Documents Collection Object (Word) – http://msdn.microsoft.com/en-us/library/office/ff840891(v=office.14).aspx
Old Style:
- Word 2010: Documents Interface (Microsoft.Office.Interop.Word) – http://msdn.microsoft.com/en-us/library/ms263641(v=office.14)
- Word 2003: Documents Interface (Microsoft.Office.Interop.Word) – http://msdn.microsoft.com/en-us/library/ms263641(v=office.11)
Fun fact: there is no Old Style Word 2007 documentation any more. You might expect it at http://msdn.microsoft.com/en-us/library/ms263641(v=office.12) but it is not there.
Not so fun fact (and the reason I was looking for the Documents documentation), is because of this big bug ONLY in Office 2010 (it does not occur in Office 2000 .. 2007, and is fixed in 2013): The WordMeister » Bug Word 2010: Documents collection is not correctly maintained.
The only workaround is to run an Office Add-in, or VBA Macro. Both not feasible for external clients.
–jeroen
Share this:
Posted in Delphi, Delphi XE, Delphi XE2, Delphi XE3, Delphi XE4, Development, Office, Office 2007, Office 2010, Office 2013, Office Automation, Office VBA, Power User, Scripting, Software Development, VBScript | Leave a Comment »
Multiple office versions on one computer: it is possible, but you should not do it
Posted by jpluimers on 2014/08/01
Wow, I didn’t even know this was possible, but I recently came across a few people that had actually done this: run multiple versions of Office on one computer.
Microsoft even has a couple of knowledge base articles on it and indicate it is not recommended (wow!), installation/update orders, and potential issues you will face.
I’ve added the respective office version ranges for each link:
- How to run multiple versions of Office on one computer: 4.5, 95, 97, 2000
- How to run multiple versions of Office on one computer: 4.2/4.3, 95, 97, 2000, XP
- Information about running multiple versions of Office with Office 2003: 97, 2000, XP, 2003
- Information about using 2007 Office suites and programs on a computer that is running another version of Office: 2000, XP, 2003, 2007
- Information about how to use Office 2010 suites and programs on a computer that is running another version of Office: XP, 2003, 2007, 2010
- Information about how to use Office 2013 suites and programs (MSI deployment) on a computer that is running another version of Office: x86 2003, 2007, 2010, 2013; x64 2010, 2013
–jeroen
Share this:
Posted in Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Outlook, Power Point, Power User, Word | Leave a Comment »
.NET/C#: Interesting DocX library to create OOXML documents for Word
Posted by jpluimers on 2014/07/10
In case I need to export DocX in .NET again: DocX – Home which is also at github.com/WordDocX/DocX
(I did it both with Word Automation and OOXML, they were a pain).
–jeroen
Share this:
Posted in .NET, .NET 4.0, .NET 4.5, C#, C# 4.0, C# 5.0, Development, Office, Office 2007, Office 2010, Office 2013, Office PIA, Software Development | Leave a Comment »
Excel worksheet function – How do I get the weekday name of a date?
Posted by jpluimers on 2014/07/04
At first sight you’d think that getting the weekday name of a date in Excel is as easy as this simple example using the [Wayback/Archive] WEEKDAY function as an intermediate:
A1 cell: 1/8/2009
B1 cell: =TEXT(WEEKDAY(A1),"dddd")This will, for the given date, print the corresponding day.
The outcome for the 1st of August 2009 (we do dd/mm/yyyy over here) is Saturday, and you might think it is the right way to do it.
Well, as [Wayback/Archive] AdamV explains in [Wayback/Archive] it is not: the outcome is OK on certain systems, but not OK on other systems.
The actual solution is even simpler, but before we go there, lets first explain what is potentially wrong with the above code:
A1has a date value=WEEKDAY(A1)
obtains an integer value
in this case:7=TEXT(7, "dddd")
obtains the weekday name of the integer value7
in this case:SATURDAY- The latter is only because of two things:
- First the way
TEXToperates:
=TEXT(B1,"dddd, yyyy-mm-dd")
returns this full date:
Saturday, 1900-01-07
Which means that if you don’t fill in a month or year, it usesJanuaryof1900. - Second:
Excel thinks the1st of January 1900is aSunday(actually, it is Monday)
so for Excel, the7th of January 1900is aSaturday.
- First the way
Excel has a [Wayback/Archive] bug where weekdays before the 1st of March 1900 are wrong (it is the famous 1900 problem, which somewhat is the inverse of the – also famous – [Wayback/Archive] 2000 is not a leap year problem) which won’t get fixed as [Wayback/Archive] Excel wants to stay compatible with Lotus-1-2-3 which also has the bug.
So it is by luck that Excel gets the above way right.
To make sure it is always right, just format your date as "dddd" as AdamV suggests:
=TEXT(A1,"dddd")
This is much shorter than the first example, and always works well.
--jeroen
PS – via: worksheet function – How do I get the day name into a cell in Excel? – Super User.
Share this:
Posted in Excel, Office, Power User | Leave a Comment »
The Wiert Corner - irregular stream of stuff 