Posts Tagged ‘Microsoft’


So, just recently I was asked to work yet another miracle.

The Scope.

1. We need to go paperless.

2. We need to move a certain form to an online platform and make it easy for users to fill in. With a custom picture and add checkboxes on top of that.

3. We need this information to be saved onto a SharePoint site with unique filenames.

4. We need this to be simply and NOT require a user to use the traditional “Save As” or “Save to SharePoint”

So away I went and started to think. A few glitches, Point #2, a picture as in the background with control boxes over it. Um……

So, I managed to work around that with playing with the image size and using it as a “Watermark”. One sneaky little thing done, a few more to go. Then I went about adding all my controls for specific entries.

So, now onto the save to SharePoint with unique names. Enter a little bit of VBA coding and lots of Uncle Google. I was able to create a mapped drive in VBA, as you cannot save directly to SharePoint Sad smile. So, map a drive to the folder that can exposed via SharePoint, when you select “Explorer View”. I was then able to use VBA to read information from a TextBox to create the file name. Once that was completed, I remove the drive so that our End Users do not log calls about a new “Mapped drive”. Some of the coding below to help anyone else trying to do something similar.

VBA Code Below


Private Sub CommandButton1_Click()
Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.MapNetworkDrive "Z:", \\sharepointserver@portnumber\DavWWWRoot\libraryname
Set WshNetwork = Nothing
Dim sFileName As String
Dim sPath As String
sFileName = ControlName.Text
sPath = "z:\"
‘(Replace with the name of the formfield you want to use)
ActiveDocument.SaveAs FileName:=sPath & sFileName, FileFormat:=wdFormatDocument
Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.RemoveNetworkDrive "Z:", True
End Sub

Hope this helps someone else.

Follow me

facebook-small32222 twitter-small32222