VBA problem: path to word file is not correct

[WalterInOz]

Hi all,

I'm stuck with some VBA code and am hoping you can give me some suggestions what the problem might be and (more importantly) how to solve it.

I have a template file on the server (StudyPlan.doc) that people need to fill in before they can start a new study. There's a button on the frmNewStudyPlan with the code below behind it. What I want to achieve is that when users click that button a copy of the template is placed in the folder ~Study_Plans on the server and the name of the new plan is username_date_StudyPlan. That part works fine.

Next thing I'd like to see is for MSWord to open that file. That part doesn't work. There's a problem with the path but I cannot figure it out. I suppose the problem lies in the variable part (username or date) of the path. If I replace the filepath in the code above with the name of the file it works fine. Cannot a path deal with these variables?

Anyone any suggestions?


Dim strMsg As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim LWordDoc As String
Dim oApp As Object
Dim fileObj As Object
Dim strFileName As String
Dim strBackupFileName As String

'strFileName is Name of the Study Plan you want to save, add the .doc extension
strFileName = "StudyPlan.doc"
strBackupFileName = Format$(Nz(Environ("username")) & Format$(Now(), "yyyymmdd") & strFileName)

'path to database to back-up
strFileName = "R:\GLP_Study_docs\" & strFileName
'path to folder in which the back-up should be saved
strBackupFileName = "R:\~Study_Plans\New_Study_Plans\" & strBackupFileName
Set fileObj = CreateObject("scripting.filesystemobject")
fileObj.copyfile strFileName, strBackupFileName, True
DoEvents 'give system time to copy the file
'Path to the word document
LWordDoc = "R:\~Study_Plans\New_Study_Plans\ " & strBackupFileName

If Dir(LWordDoc) = "" Then
MsgBox "Document not found."
Else
'Create an instance of MS Word
Set oApp = CreateObject(Class:="Word.Application")
oApp.Visible = True
'Open the Document
oApp.Documents.Open FileName:=LWordDoc
End If

 

[Bat17]

Hi walter
I would not bother copying the file. Save it as a proper Word template .dot file
then just open it and save it to the directory you want it to be in.

With your structure I would add some debug code to check just what pathe you age getting
LWordDoc = "R:\~Study_Plans\New_Study_Plans\ " & strBackupFileName
debug.print LWordDoc

if you can't see the debug pane the ctr-g

HTH

Peter

 

[boblarson]

Looks like there may be an unwanted space in this between the last \ and the &:

_Plans\ " &

 

[WalterInOz]

I would not bother copying the file. Save it as a proper Word template .dot file
then just open it and save it to the directory you want it to be in.

That's what I do now and that gives problems. Too many sloppy users that "Save" and forget to "Save as". The copy routinr works great but then they'll have to browse to the New Study Plan. Not that much trouble but if I can get that automated it'd be better.

With your structure I would add some debug code to check just what pathe you age getting
LWordDoc = "R:\~Study_Plans\New_Study_Plans\ " & strBackupFileName
debug.print LWordDoc

This is the path that's returned: R:\~Study_Plans\New_Study_Plans\wvanheumen20070219StudyPlan.doc

Exactly as expected. So why won't it open?


Edit: Now that's weird! When I switch to edit mode of this message there is no space between the date and Studyplan at the end of the pathlans\wvanheumen20070219StudyPlan.doc

However, there's a space visible in the posting???

 

[boblarson]

Edit: Now that's weird! in the edit mode there is no space between the date and Studyplan at the end of the pathlans\wvanheumen20070219StudyPlan.doc

However, there's a space visisble in the posting???

Yeah, that happens here kind of frequently, but we still gotta ask because you don't know if it is the site or the code.

If you have the Word Reference set, try rewriting the code to this:

Dim oApp As Word.Application
Dim wdDoc as Word.Document

Set oApp = New Word.Application

oApp.Visible = True
'Open the Document
Set wdDoc = oApp.Documents.Open LWordDoc

Access doesn't really use the VBA like the Word VBA (for example using the := )

 

[WalterInOz]

Looks like there may be an unwanted space in this between the last \ and the &:

Unfortunately that's doesn't make a difference.

 

[Moniker]

There's the issue. Your filename has an unresolved space. You are currently using this:

LWordDoc = "R:\~Study_Plans\New_Study_Plans\ " & strBackupFileName

That will debug print correctly, but it won't store the space correctly. You said the correct path is this:

R:\~Study_Plans\New_Study_Plans\wvanheumen20070219 StudyPlan.doc

The space between ...0219 Study... is the issue. To test that theory, go to that file and replace the space with an underscore. (Make sure you replace the space in the variable strBackupFileName as well.) That should correct it.

 

[Bat17]

Originally Posted by Bat17
I would not bother copying the file. Save it as a proper Word template .dot file
then just open it and save it to the directory you want it to be in.

That's what I do now and that gives problems. Too many sloppy users that "Save" and forget to "Save as". The copy routinr works great but then they'll have to browse to the New Study Plan. Not that much trouble but if I can get that automated it'd be better.

If you use a DOT file not DOC file then there is no deifference between "Save" and "Save As" the first time it is saved and anyway you can save it for them in code so that it is saved to the right place first time.
Peter

 

[WalterInOz]

Problem is solved! And that could have done much quicker if only I had looked a bit more careful at this reply!

With your structure I would add some debug code to check just what path you age getting
LWordDoc = "R:\~Study_Plans\New_Study_Plans\ " & strBackupFileName
debug.print LWordDoc

The solution was very simple following the suggestion pointed out by Bat17.
This is the code that opens the correct file:
LWordDoc = strBackupFileName

Thanks for helping. I've got it working now.