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.
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, I have traipsed through the forums for advice and come up blank.... anyone got any ideas?
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...
Regards
Martin.