SP.Writer

Fixing SP.Writer Run Time Error 70

If you’re like me, you haven’t used SP.Writer in a while but then if you’re also like me, you will at some point need to use it to generate a project customised shared parameters file. The first thing you do is pull out your trusty SP.Writer only to find that when you create a new parameter you get a run-time error 70.

If you hit the debug button, you will be taken to the mod_GUID module and the 4th line will be highlighted as the problem child.

The function used to create the GUID in SP.Writer was patched in the July 2017 security patches for Microsoft Office so if you’re up to date with your security patches, this is working as intended. That doesn’t help us in the slightest with creating shared parameters though, so what’s the solution?

First if you haven’t already done so, you’ll need to enable the developer toolbar in Excel which you can do by following the instructions over at the Microsoft website.

Once enabled, select Visual Basic from the developer tab on the ribbon. Alternatively you can use the ALT+F11 shortcut on your keyboard.

On the left hand side of your screen, you will see a window titled Project – VBA Project, scroll down until you find Modules. Right click on modules and from the contextual menu select Insert.. -> Module.

Copy and paste the following code into the new module.

Private Type GUID_TYPE
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type

Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As LongPtr
Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpsGUID As LongPtr, ByVal cbMax As Long) As LongPtr

Function CreateGuidString()
Dim guid As GUID_TYPE
Dim sGUID As String
Dim retValue As LongPtr
Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
retValue = CoCreateGuid(guid)
If retValue = 0 Then
sGUID = String$(guidLength, vbNullChar)
retValue = StringFromGUID2(guid, StrPtr(sGUID), guidLength)
If retValue = guidLength Then
CreateGuidString = sGUID
End If
End If
End Function

You can also rename the module, I renamed mine to mod_CoCreateGUID as this is will be method we’re using to generate the GUID. If you can’t see your properties window, press the F4 button.

 

Next, open the module mod_GUID and replace the existing code with the code below


Sub GetGUID()
Dim sGUID As String
sGUID = CreateGuidString()
wsData.Cells(LastRow + 1, 2) = "PARAM"
wsData.Cells(LastRow, 3) = Replace(Replace(sGUID, "{", ""), "}", "")

End Sub

 

The final step is to make a small change to the UserParameter form. In the VBA project, find the UserParameter form and right click and select View Code from the contextual menu.

Search for the code


'generate the GUID
GUID

and replace with the code


'generate the GUID
GetGUID

and that’s it! You’re done! You can now start creating shared parameters again. The GUID looks a little longer but it’s actually just the difference in length between upper and lower case with the font used.