Excel UserForm Data to Word Bookmark - Not working on network

[MBACON]

Good Afternoon Folks,


Apologies if I miss a convention rule, this is my first post here... (much information from the collective from searching here however... thnak you.)


So, to the problem in hand.


I am writing a new Laboratory request system in excel to raise and track work through my department (not an IT professional) which will remove duplicity and unrequired data capture. So far this works exactly how I want with the data flow being as follows:


User selects the Work Type Required.
USER FORM pops up specific to this type for them to complete.
User clicks submit at which point the routine takes over and validates the data filled in for type&content, if correct it continues, if not it kicks it back to the user to try again. (with stern message).
Continues the routine transfers the USER FORM data into a new line on the LOG sheet within excel.
Then it opens a specific WORD template (defined by the Work Type selected in step 1) and populates the bookmarks contained within from the USER FORM data.
This word document is then saved in a specific place but left open for additional editing.


Now as mentioned, when running I was constructing this I had everything locally in My Docs (C:), and everything worked as planned:thumb.


I have tried now to move this to a network location where it needs to be so that all members in the company can access it (an open network drive location), and the routine just bypasses the opening of the word document.


I have tried the Local Drive name (which is common through out the organisation) and the UNC path, neither work and I am completely stuck:banghead:, I have traipsed through the forums for advice and come up blank.... anyone got any ideas?:help


Below is the code extract relevant: (Note: S_PATH is the variable for hte address, I have the two names here and turn comments on/off to flick between, I have removed the UNC address here for simplification).




'Defining WORD application
Global WORD_Doc, WORD_App As Object


'Word Document Path [Master/ReportDate]
Public TEMPLATE, T_PATH, S_PATH As String

Public TEST_CHOICE, E_REQ, E_DESC As String


Sub NewReport()


On Error Resume Next


Call Populate_Excel.UF2EXL 'Transfer data from User Form to Excel Log




'Exit Sub ' <<<<<<<<< Temporary Exit during testing


'##########################
'Word Document Path
'##########################


'Reports Path
S_PATH = "C:\Users\15515\Documents\LIMS\"
'S_PATH = "O:\COMPANY\LABSYS\PENDING\LIMS\"


'Templates Location
T_PATH = S_PATH & "_Templates\"
TEMPLATE = TEST_CHOICE & ".dotx"


MsgBox (T_PATH & TEMPLATE) ' Temporary Notification to check which path is being used




' Now you start opening the Word application and the document
Set WORD_App = GetObject(T_PATH & TEMPLATE, "word.application")



If WORD_App Is Nothing Then

Set WORD_App = GetObject(T_PATH & TEMPLATE, "word.application")

End If


On Error GoTo 0


With WORD_App
Set WORD_Doc = Documents.Add(T_PATH & TEMPLATE)


'Transfer the Userform data to the Word Template Bookmarks

Call Report_Data.KONSTANT_FIELDS

WORD_Doc.Application.Visible = True

'Save the template with data in by RQ number and description in the Reports Path

WORD_Doc.SaveAs (S_PATH & E_REQ & " - " & E_DESC)


End With


'Reset word References
Set wdapp = Nothing
Set wddoc = Nothing


End 'Clears All VARS


Exit Sub


End Sub



Thank you for taking time to read this, hopefully one of you wonderful people might have an idea on a solution...:think:

Regards
Martin.

[Dave]

Hi MBACON and welcome to this forum. Your code to create the Word application and open the template seems off. Here's some code that might help. Dave

'open Word application
On Error Resume Next
Set WORD_App = GetObject(, "Word.Application")
If Err.Number <> 0 Then
On Error GoTo 0
Set WORD_App = CreateObject("Word.Application")
End If
'open doc
WORD_App.Documents.Open Filename:=T_PATH & TEMPLATE
'or....
Set WORD_Doc = WORD_App.Documents.Add(T_PATH & TEMPLATE)

[MBACON]

Thanks Dave.

I tried the alterations, however if I use your suggested code then it stops working locally.
Testing through it, if I change this:

Set WORD_App = GetObject(T_PATH & TEMPLATE, "word.application")
then nothing happens.(ie.. word does not open)
Thanks for your suggestion anyway> M

[gmayor]

Your code does not set the value of the variable TEST_CHOICE so the variable TEMPLATE is invalid and thus the document cannot be created.
Similarly you have not set the values for E_REQ & E_DESC

You referenced two variables that don't exist

Set wdApp = Nothing
Set wdDoc = Nothing

[MBACON]

Thanks for pointing that out Graham.

The code has those TEST_CHOICE set elsewhere (and E_REQ & E_DESC), I have had a watch during execution on those VARs and they do set to what they are supposed to.
As I mentioned, the core programming works, but only when the template is held on my local drive, as soon as I try to use a network path it doesn't.

[Paul_Hossler]

Do you get an error message if you just try to manually create a DOCX from the network DOTX?

[MBACON]

BINGO.:clap:

Thank you Paul, surprise surprise its something so simple that I overlooked.

Whilst I can create the DOCX from the DOTX (on network) without error messages.

HOWEVER, what it does do (guessing I'm going to have to have a different discussion with our IT team now:wot..... Unless anyone knows away around this within the routine?).
is open the document in 'Protected View' as, for some reason, our network files think they are an internet location. As soon as I turned this off in Protected View settings it all worked.


I'll hang fire marking this solved for a bit in-case anyone knows if it's possible to bypass the PV setting?

Regards
Martin.