Skip to content

Fixing the Foscam Port Forwarding Blues

I recently started experimenting with home automation (using a ).
One part of this was adding a Foscam FI8910W, which (in theory) I could use to monitor my home while I’m travelling, as well as using it to provide alarm triggers to the Vera itself.

The simplest part of the setup should have been the router port forwarding. I’m not going to spell out the procedure here, since there are a ton of sites doing that already. What I *did* find is that the router was for some reason blocking internet traffic from reaching the camera. This was obvious from an open port check using the tool, which reported ‘closed’ for the port I was trying to forward.

My router is a Linksys E3200, and in the camera’s web interface I had set the camera up with a static IP (192.168.2.94), outside my normal DHCP range (which starts at .105).

I checked all the obvious things:

  • I tried port forwarding using both “Single Port Forwarding” and “Port Range Forwarding”.
  • I tried putting the camera into the DMZ
  • I changed the port to a low port (less than 1024); I also tried port 80.
  • I tried moving the camera to an IP inside the DHCP range, i.e. 192.168.2.196

Nothing made any difference; pretty aggravating! I did a lot of searching, and there were a lot of people with other issues which seemed to mainly revolve around dynamic DNS setup, or loopback problems inside the LAN.

What finally solved it was removing the camera from the router’s “Clients Already Reserved” DHCP reservation table.

This is accessed from the “Basic Setup” tab:
router1

Then, at the bottom of the pop-up window, remove the entry for the camera:
router2

I had added the camera in here as an early step in the setup, to try to force it to get the correct IP address; after manually entering the IP address into the camera’s web interface I never bothered removing it from here. As soon as I removed it from this table, everything started working as it should.

Change Outlook’s Proofing Language on the Fly

I’m working for a company that does business in the U.S. and several other countries, and I usually attempt to ensure that my spelling is in line with who I’m communicating with. As a result, email communication often ends up looking like this:

For years I just did my best to grit my teeth and ignore it, but finally I got around to writing a macro that allows a single button-click to change the language of the email you’re working on:

Option Explicit

Private Const wdEnglishUS = 1033
Private Const wdEnglishUK = 2057

Public Sub SetLangEngUS()
	SetDocLang wdEnglishUS
End Sub

Public Sub SetLangEngUK()
	SetDocLang wdEnglishUK
End Sub

Sub SetDocLang(ALangID As Integer)
	Dim wordEditor As Object

	If Inspectors.Count = 0 Then Exit Sub
	If ActiveInspector.CurrentItem.Class <> olMail Then Exit Sub
	If Not ActiveInspector.IsWordMail Then Exit Sub
	If ActiveInspector.EditorType <> olEditorWord Then Exit Sub
	Set wordEditor = ActiveInspector.wordEditor

	On Error Resume Next
	ActiveInspector.wordEditor.Styles("Normal").LanguageID = ALangID
	ActiveInspector.wordEditor.SpellingChecked = False
End Sub

Then, just add two icons to the Quick Access Toolbar, each pointing to one of the SetLangEngUS and SetLangEngUK calls.

Counting Null Values in a Row

On StackOverflow today someone asked how to get the number of null values in a table’s row; i.e. if there are 10 columns, and 4 of the columns contain data, the answer would be 6.

The requirement was that it would work in sqlite, which I don’t have a generic solution for, but it did prompt me to play around with this in SQL Server.  The result is fairly tidy:

with xmltab(xmlcol) as
    (select cast(
        (select
            *
        from
            myTable
        where
            myIdCol = 1
        for xml path('MYTABLE'), elements xsinil
    ) as xml) as xmlcol
)
select cast(cast(xmlcol.query('count(//*[@xsi:nil="true"])') as varchar) as int) from xmltab

What is Visual Studio Really Saying?

Image

Using XSLT to Extract SSIS Variables From a Package

Over my last few SSIS projects I’ve tried to build a standard logging system into each, with one of the side effects being I’ve ended up with variables in each package that need to be documented in an external file.

Rather than go down the more extreme route of using the .NET API (which is appealing for the future, but overkill for what I need at the moment), it’s a lot easier to build a small XSL transform script to pull out the user variables into a CSV file.  The structure of the .dtsx package file is:

<?xmlversion="1.0"?>
<DTS:Executable>
    <!-- bunches of stuff -->
    <DTS:Variable>
        <DTS:Property DTS:Name="xxx"/>
        <DTS:VariableValue>123</DTS:VariableValue>
    </DTS:Variable>
</DTS:Executable>

Note that the ‘Property’ entries are repeated several times, with the ‘xxx’ containing various values for the various properties we’re interested in; the only thing that doesn’t follow that rule is the variable value, which has its own node.  Here’s one sample variable:

<DTS:Variable>
    <DTS:Property DTS:Name="Expression"/>
    <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
    <DTS:Property DTS:Name="Namespace">User</DTS:Property>
    <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
    <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
    <DTS:VariableValue DTS:DataType="3">30010</DTS:VariableValue>
    <DTS:Property DTS:Name="ObjectName">EventCustInsert</DTS:Property>
    <DTS:Property DTS:Name="DTSID">{3057CEC1-68C8-4651-AED6-E75558C9A15E}</DTS:Property>
    <DTS:Property DTS:Name="Description"/>
    <DTS:Property DTS:Name="CreationName"/>
</DTS:Variable>

All I needed was the name, description and value, so a small XSLT script (which I just run in Notepad++) pulls these out very easily:

<?xmlversion="1.0"encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:DTS="www.microsoft.com/SqlServer/Dts" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" encoding="utf-8" />
    <xsl:template match="/">
    <xsl:text>Name,Description,Value</xsl:text>
    <xsl:text>
</xsl:text>
    <xsl:for-each select="//DTS:Variable[DTS:Property[@DTS:Name='Namespace']='User']">
      <xsl:value-of select="DTS:Property[@DTS:Name='ObjectName']"/>
      <xsl:text>,</xsl:text>
      <xsl:value-of select="DTS:Property[@DTS:Name='Description']"/>
      <xsl:text>,</xsl:text>
      <xsl:value-of select="DTS:VariableValue"/>
      <xsl:text>
</xsl:text>
    </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Extending this to include other properties should be trivial – just add the field names to the fifth line, and extend the current <xsl:value-of> series to include other fields as needed.

The result is a nice CSV file that’s easy to load into, say, Excel or Word for documentation purposes.

SSIS 2005 Custom Task… From VS 2010

I just started a new project that needs to be run under a SQL Server 2005 environment, but I already had a custom logging task written for SSIS 2008 that I wanted to use. The component wouldn’t appear in the 2005 toolbox, however, despite all my best efforts.

To compound it, I didn’t have a VS 2008 dev environment available (I’ve changed laptops), and installing it didn’t seem to make sense, given that it seemed to be the wrong version anyway – especially when I had something else JUST as wrong already installed in the form of VS 2010.

So up upgraded the project and compiled the custom task in VS 2010, and tried everything to try to make it happy about being used in SSIS 2005′s toolbox:

  • I changed the target .NET framework version to 2.0
  • I changed the post-build script to store the resulting .DLL in the appropriate directory in both Program Files and Program Files (x86) for good measure (it already registered into the GAC):
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe" -u $(TargetName)
COPY "$(TargetDir)$(TargetName).*" "%ProgramFiles%\Microsoft SQL Server\90\DTS\Tasks\"
COPY "$(TargetDir)$(TargetName).*" "%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\Tasks\"
COPY "$(TargetDir)$(TargetName).*" "%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\" 
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe" -if "$(TargetDir)$(TargetName).DLL"
  • I made sure the assembly was signed

After a half day of screwing around with it (how much time do you really save by reusing components?),  the answer turned out to be a simple thing: the version of the main DTS referenced assembly needed to be changed.  It’s not obvious from the solution explorer:

But when you examine the properties, it is:

All that’s needed to solve it is to remove that assembly from the References, and re-add it from the correct place.  In my case this turned out to be:

C:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

Finally, the assembly appeared in the toolbox.  For reference, this shows in the ‘SSIS Control Flow Items’ section of the ‘Choose Toolbox Items’ dialog.

Scaling Textile Image Links

This is something that has been driving me crazy; plenty of sites (including Basecamp) use Textile still, and I often want to link an image in without formally downloading it, resizing it and re-uploading it.

All the Textile examples I found all show how to display an image inline via a URL, but none of them show how to resize the image, at least not in a fashion I could make work.  Some digging finally turned this up, and I figured I’d post this here for when I forget next time, and in case it’s useful for someone else.

A plain image is linked in as follows:

!http://farm6.static.flickr.com/5306/5627310027_eb64230826_t.jpg!

Resulting in: 

To force a specific size:

!{width: 50px; height: 33px;}http://farm6.static.flickr.com/5306/5627310027_eb64230826_t.jpg!

Resulting in: 

Whoosh!

Feet wet with the Arduino: Debouncing

My lovely family got me for Xmas this year an Arduino Uno, and some associated bits like a breadboard.  I’ve always been interested in how things work, but intimidated by component-level electronics, so I figured this would be a good place to get over it and learn something at the same time.

I’d already done a bunch of reading, including the Getting Started guide (thanks, Douggie, Hannah!), so next came a quick trip to RadioShack, where I rifled through their parts bins looking for anything interesting to hook up.  Maybe not the cheapest solution, but it was a neat experience to be able to dig through and find stuff that might be fun to hook up, but not be sure what it’s useful for yet.  In a world where everything you buy has an exact purpose, and generally only one way of hooking it up, it was an eyeopener!

Button and two LEDs

Small beginnings

Among other things I ended up with a couple of plain old green LEDs, and a momentary switch (275-646).  I had to crimp the switch’s legs with a pair of pliers to get them to fit into my breadboard, since in my RadioShack fugue I wasn’t savvy enough to imagine this might be something I’d want to do.

I’ve done some programming, and am comfortable digging and researching until I understand something, so I was happy enough with the coding side of it – which in fact is very similar to c#, given its c / c++ heritage.  Something that fascinates me is that in coding, everything is very deterministic most of the time, but in hardware, there was a whole side I’d never guessed at; contacts can ‘bounce‘, or a potentiometer can flick back and forth between two values, or any number of other mysterious and distressing behaviours can manifest.  I decided to combine the LEDs and a button, and explore the whole thing to get a better handle on it.

Every Arduino LED tutorial starts off with a LED (or two), and indicates you needed a resistor in line with each LED.  A bit of digging turned up the underlying reason: the LEDs just don’t handle the current that results from a 5V source. That’s probably not exactly worded correctly, and of course there’s more to it than that, but the end result is that I needed 100 ohm resistors in series with each LED.  The resistance calculation was pretty straightforward using a handy online calculator; doing it manually is a bit more work, but definitely useful.  It took me a while to find a site that actually explains the calculation, which works out as follows:

The Arduino’s digital pins provide 5V, and my LEDs have a forward voltage (voltage level needed to make them light) of 2.1 volts.  So we need to reduce the 5V DOWN to 2.1V, i.e. reduce it by 2.9V – this is the amount the resistor has to drop the voltage by.  The LEDs have a forward current (how much current we want to drive through the LED) of 30 mA.

So for the resistor, since V=IR, i.e. 2.9 = (0.030 * R), then R = 2.9 ÷ 0.030 = 96.6667.  The next ‘size up’ for resistors is 100Ω, so we select that. Whew!

The only other thing that I found non-intuitive is that I needed a resistor between the switch and ground.  After some research to understand it, I found it helps to think of it this way: When the switch is open, the digital pin ‘sees’ the ground normally, through the resistor.  In this case, the resistor is redundant; there’s only one path, so the digital pin is ‘low’.  When the switch is closed, however, the pin can see two paths; one to ground, and one to the 5V source.  The fact that one path (the ground path) has a resistor causes the pin to have a ‘preferred path’ the other way, through to the 5V source – i.e. the resistor on the ‘ground path’ makes it a less ‘desirable’ path.  I’m sure that’s not technically accurate, but it makes it easier for me to remember!

So, the circuit looks like this in Fritzing:

 

The tidy version - thanks Fritzing!

The tidy version - thanks Fritzing!

And less attractive, but keepin’ it real:

The Board

All good!

The Arduino code to control all this is as follows:

const int led0Pin = 11;            // LED1 is connected to pin 11
const int led1Pin = 12;            // LED2 is connected to pin 12
const int btnPin = 2;              // Momentary button is connected to pin 2
const int minTime = 30;            // The minimum time the button must be pressed (in ms) before we consider it a valid press
int lastReadBtnState;              // Used to store the last state that we read from the button (this could flop all over the place)
int acceptedBtnState;              // Used to store the last ACCEPTED state that we read from the button (this should be very stable)
int ledLit = 0;                    // Which LED is lit; 0 = led0, 1 = led1
unsigned long lastChangeTime;      // Used to store the last time that we observed a change in the button's state
boolean inTransition = false;      // If this is true, we've observed a button change, and are waiting to see if it is stable

void setup()
{
Serial.begin(9600);                      // Initialise the serial output so that we can write out debug messages
pinMode(led0Pin, OUTPUT);                // Set the LED0 pin as output
pinMode(led1Pin, OUTPUT);                // Set the LED1 pin as output
pinMode(btnPin, INPUT);                  // Set the button pin as input
acceptedBtnState = digitalRead(btnPin);  // Initialise the stable button state to whatever state it actually is
lastReadBtnState = acceptedBtnState;     // Indicate that we're in a stable state
}

void loop()
{
lastReadBtnState = digitalRead(btnPin);
if (!inTransition &amp;&amp; (lastReadBtnState != acceptedBtnState))
// Bam, we registered a button state change
{
lastChangeTime = millis();  // Mark the time we observed the change
inTransition = true;        // Note that we're now in a transition state
Serial.println("Button state changed from " + StateToStr(acceptedBtnState) + " to " + StateToStr(lastReadBtnState) + ".  Going into transition");
}
else if (inTransition &amp;&amp; ((millis() - lastChangeTime) &gt; minTime))
// The button state hasn't changed for at least minTime milliseconds, so assume we're stable now
{
Serial.println("Met minimum time requirement: going from " + StateToStr(acceptedBtnState) + " to " + StateToStr(lastReadBtnState));
if (lastReadBtnState == LOW)
// Only toggle the LED if the button is in a LOW state
{
Serial.println("New state is LOW; toggling LED");
ledLit = 1 - ledLit;
}
// Move the last-read value into the accepted state variable
acceptedBtnState = lastReadBtnState;
// Note that we're no longer in transition
inTransition = false;
}

// Turn on the appropriate LED
if (ledLit == 0)
{
digitalWrite(led0Pin, HIGH);
digitalWrite(led1Pin, LOW);
}
else
{
digitalWrite(led0Pin, LOW);
digitalWrite(led1Pin, HIGH);
}
}

String StateToStr(int AState)
// Given a pin state variable, returns a text equivalent of its value
{
return (AState == HIGH ? "HIGH" : "LOW");
}


The LEDs alternate smoothly when the button is pressed, so mission accomplished.  Back to the sack of goodies to see what's next!

VBA Date Picker

I often work within the suffocating constraints of VBA (in my case, a derivative called WinWrap), and the control set is fairly limited.

I often poke around in the Windows API looking for better ways to do things, and today I put together some old code and set up a routine to display a date picker control on a dialog – and was almost surprised when it worked.

The trick is to use CreateWindowEx to create the date control, and SendMessage to set and read the value, with the rest just being VBA/API plumbing.

Note that the code below expects pixel coordinates for the control position instead of the funky values used by WinWrap.

Here’s the complete source:

Option Explicit
Private Type SYSTEMTIME
   wYear As Integer
   wMonth As Integer
   wDayOfWeek As Integer
   wDay As Integer
   wHour As Integer
   wMinute As Integer
   wSecond As Integer
   wMilliseconds As Integer
End Type

Declare Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" ( _
      ByVal dwExStyle As Long, _
      ByVal lpClassName As String, _
      ByVal lpWindowName As String, _
      ByVal dwStyle As Long, _
      ByVal x As Long, _
      ByVal y As Long, _
      ByVal nWidth As Long, _
      ByVal nHeight As Long, _
      ByVal hWndParent As Long, _
      ByVal hMenu As Long, _
      ByVal hInstance As Long, _
      lpParam As Any) As Long

Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function SendMessage Lib "user32.dll" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As SYSTEMTIME) As Long

Private Const DTM_GETSYSTEMTIME As Long = &H1001
Private Const DTM_SETSYSTEMTIME As Long = &H1002
Private Const DATETIMEPICK_CLASS = "SysDateTimePick32"
Private Const WS_BORDER As Long = &H800000
Private Const WS_CHILD As Long = 1073741824
Private Const WS_VISIBLE As Long = 268435456

Private hWndDatePicker As Long

Sub Main
   Begin Dialog UserDialog 160,77,.evMain ' %GRID:10,7,1,1
      OKButton 40,42,90,21
   End Dialog
   Dim dlg As UserDialog
   Dialog dlg
End Sub

Rem See DialogFunc help topic for more information.
Private Function evMain(DlgItem$, Action%, SuppValue?) As Boolean
   Select Case Action%
   Case 1 ' Dialog box initialization
      hWndDatePicker = CreateDateControl SuppValue&, 10, 10, 100, 25
      SetDateControl hWndDatePicker, #12/25/2012#
    Case 2 ' Value changing or button pressed
      Dim pickedDate As Date
      pickedDate = ReadDateControl(hWndDatePicker)
      MsgBox "You selected: " + CStr(pickedDate)
       DestroyWindow hWndDatePicker
      Rem evMain = True ' Prevent button press from closing the dialog box
   Case 3 ' TextBox or ComboBox text changed
   Case 4 ' Focus changed
   Case 5 ' Idle
      Rem Wait .1 : evMain = True ' Continue getting idle actions
   Case 6 ' Function key
   End Select
End Function

Sub SetDateControl(ControlHWnd As Long, AValue As Date)
   Dim wMsg As Long
   Dim wParam As Long
   Dim lParam As SYSTEMTIME
   Dim result As Long

   wMsg = DTM_SETSYSTEMTIME
   wParam = 0
   With lParam
      .wYear = Year(AValue)
      .wMonth = Month(AValue)
      .wDay = Day(AValue)
      .wHour = Hour(AValue)
      .wMinute = Minute(AValue)
      .wSecond = Second(AValue)
      .wMilliseconds = 0      'not supported in Date type
   End With

   SendMessage(hWndDatePicker, wMsg, wParam, lParam)
End Sub

Function ReadDateControl(ControlHWnd As Long) As Date
   Dim wMsg As Long
   Dim wParam As Long
   Dim lParam As SYSTEMTIME
   Dim result As Long

   wMsg = DTM_GETSYSTEMTIME
   wParam = 0
   SendMessage(hWndDatePicker, wMsg, wParam, lParam)
   With lParam
      result = CDate(CStr(.wMonth) + "/" + CStr(.wDay) + "/" + CStr(.wYear))
   End With
   ReadDateControl = result
End Function

Function CreateDateControl(DlgHWnd As Long, AX As Long, AY As Long, ADX As Long, ADY As Long) As Long
   Dim dwExStyle As Long
   Dim lpClassName As String
    Dim lpWindowName As String
    Dim dwStyle As Long
    Dim x As Long
    Dim y As Long
    Dim nWidth As Long
    Dim nHeight As Long
    Dim hWndParent As Long
    Dim hMenu As Long
    Dim hInstance As Long
    Dim lpParam As Long

   dwExStyle = 0
   lpClassName = DATETIMEPICK_CLASS
   lpWindowName = ""
   dwStyle = WS_BORDER Or WS_CHILD Or WS_VISIBLE
   hWndParent = DlgHWnd
   hMenu = vbNull
   hInstance = MainFormHandle
   lpParam = vbNull
   CreateDateControl = CreateWindowEx(dwExStyle, lpClassName, lpWindowName, dwStyle, AX, AY, ADX, ADY, hWndParent, hMenu, hInstance, lpParam)
End Function

Option Explicit
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Declare Function CreateWindowEx Lib “user32″ Alias “CreateWindowExA” ( _
ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hWndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) As Long

Private Declare Function DestroyWindow Lib “user32″ (ByVal hwnd As Long) As Long
‘Private Declare Function DateTime_GetSystemtime Lib “Comctl32″ (ByVal hwndDP As Long, ByRef lpSysTime As SYSTEMTIME) As Long
Private Declare Function SendMessage Lib “user32.dll” (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As SYSTEMTIME) As Long

Private Const DTM_GETSYSTEMTIME As Long = &H1001
Private Const DTM_SETSYSTEMTIME As Long = &H1002
Private Const DATETIMEPICK_CLASS = “SysDateTimePick32″
Private Const WS_BORDER As Long = &H800000
Private Const WS_CHILD As Long = 1073741824
Private Const WS_VISIBLE As Long = 268435456

Private hWndDatePicker As Long

Sub Main
Dim userChoice As Integer
Begin Dialog UserDialog 160,77,.evMain ‘ %GRID:10,7,1,1
OKButton 40,42,90,21
End Dialog
Dim dlg As UserDialog
Dialog dlg
End Sub

Rem See DialogFunc help topic for more information.
Private Function evMain(DlgItem$, Action%, SuppValue?) As Boolean
Select Case Action%
Case 1 ‘ Dialog box initialization
hWndDatePicker = CreateDateControl SuppValue&, 10, 10, 100, 25
SetDateControl hWndDatePicker, #12/25/2012#
Case 2 ‘ Value changing or button pressed
‘DateTime_GetSystemtime hWndDatePicker, pickedDate
Dim pickedDate As Date
pickedDate = ReadDateControl(hWndDatePicker)
MsgBox “You selected: ” + CStr(pickedDate)
DestroyWindow hWndDatePicker
Rem evMain = True ‘ Prevent button press from closing the dialog box
Case 3 ‘ TextBox or ComboBox text changed
Case 4 ‘ Focus changed
Case 5 ‘ Idle
Rem Wait .1 : evMain = True ‘ Continue getting idle actions
Case 6 ‘ Function key
End Select
End Function

Sub SetDateControl(ControlHWnd As Long, AValue As Date)
Dim wMsg As Long
Dim wParam As Long
Dim lParam As SYSTEMTIME
Dim result As Long

wMsg = DTM_SETSYSTEMTIME
wParam = 0
With lParam
.wYear = Year(AValue)
.wMonth = Month(AValue)
.wDay = Day(AValue)
.wHour = Hour(AValue)
.wMinute = Minute(AValue)
.wSecond = Second(AValue)
.wMilliseconds = 0      ‘not supported in Date type
End With

SendMessage(hWndDatePicker, wMsg, wParam, lParam)
End Sub

Function ReadDateControl(ControlHWnd As Long) As Date
Dim wMsg As Long
Dim wParam As Long
Dim lParam As SYSTEMTIME
Dim result As Long

wMsg = DTM_GETSYSTEMTIME
wParam = 0
SendMessage(hWndDatePicker, wMsg, wParam, lParam)
With lParam
result = CDate(CStr(.wMonth) + “/” + CStr(.wDay) + “/” + CStr(.wYear))
End With
ReadDateControl = result
End Function

Function CreateDateControl(DlgHWnd As Long, AX As Long, AY As Long, ADX As Long, ADY As Long) As Long
Dim dwExStyle As Long
Dim lpClassName As String
Dim lpWindowName As String
Dim dwStyle As Long
Dim x As Long
Dim y As Long
Dim nWidth As Long
Dim nHeight As Long
Dim hWndParent As Long
Dim hMenu As Long
Dim hInstance As Long
Dim lpParam As Long

dwExStyle = 0
lpClassName = DATETIMEPICK_CLASS
lpWindowName = “”
dwStyle = WS_BORDER Or WS_CHILD Or WS_VISIBLE
hWndParent = DlgHWnd
hMenu = vbNull
hInstance = MainFormHandle
lpParam = vbNull
CreateDateControl = CreateWindowEx(dwExStyle, lpClassName, lpWindowName, dwStyle, AX, AY, ADX, ADY, hWndParent, hMenu, hInstance, lpParam)
End Function

Word 2007 Document Language Change

I frequently have to switch between working on documents for a UK/NZ/US audience, and often want to change the document’s language to match.

Spelling has never been an issue for me, but there’s something just wrong with having the document set to a language different to what you’re writing.  Words like ‘colour’ and ‘neighbour’ get the annoying red squiggle, and it’s irritating that real typos get mixed in with the (ahem) original English spellings of words.  Every now and then I have to work out how to beat this, and I always forget, so think of this as a note-to-self that may be useful to others too.

Some googling will tell you that the most common solution is to do a select-all (ctrl-A), then use the language selector in the status bar (or in the Review ribbon).  This only sets the selected text, though, and you’ll have to re-do it every time you add new text.  How could that ever be good enough?

Another solution you’ll see is to change the default language for the Normal template by clicking the ‘Default…’ button in the language dialog.  As far as I know, this will affect all new documents from that point on – obviously crazy talk.

Fortunately, there’s a way that feels a lot more right, in that it changes the entire document (but just the current document), and any new text that you add.  It’s easy to do at any time during the document editing process, and you don’t have to keep reapplying it.

It just boils down to changing the Normal style for the current document. Not for the next document, or all future documents, or all the documents in your housing subdivision… just for this one.  Steps are easy:

1. From the ‘Home’ ribbon, open the style editor:

Open the Style Editor

Open the Style Editor

2. Modify the Normal style:

Modify the Normal Style

Modify the Normal Style

3. Change the language:

Change the language

Change the language

Select the language you need, and you’ll notice that it appears in the description of the style (see the screenshot above).  A nice side affect is that most (all?) of the other styles are based on Normal, so they get the change too.

Finally, just OK your way back out to the document.

Follow

Get every new post delivered to your Inbox.