word vba to set sPath as current directory

Question

Simple code to loop paste .emf files into word:

Sub LoopEMF()
Dim sPic As String
Dim sPath As String

sPath = "C:\Users\me\Desktop\Test2\"
sPic = Dir(sPath & "*.emf")

Do While sPic <> ""
    Selection.TypeParagraph
    Selection.InlineShapes.AddPicture _
      FileName:=sPath & sPic, _
      LinkToFile:=False, SaveWithDocument:=True
    sPic = Dir
    Selection.TypeParagraph
Loop
End Sub

Rather than a specified directory, I simply want to look in the active directory in which the word file (that is open) is located. Much searching has yielded no clue - which is surprising, embarrassing and probably means I'm not using the right key words.

Help?

score 0 · Answer 1 · edited May 23 '17 at 11:52

When I open an Excel document D:\db\tmp\test1.xlsm:

CurDir() returns C:\Users\[username]\Documents

ActiveWorkbook.Path returns D:\db\tmp

So CurDir() has a system default and can be changed.

ActiveWorkbook.Path does not change for the same saved Workbook.

For example, CurDir() changes when you do "File/Save As" command, and select a random directory in the File/Directory selection dialog. Then click on Cancel to skip saving. But CurDir() has already changed to the last selected directory.

From:
How to get current working directory using vba?

score 0 · Answer 2 · answered Jun 13 '16 at 13:02

Even more embarrassing - I had been using the correct code:

Sub NewLoopEMF()
Dim sPic As String
Dim sPath As String

sPath = ActiveDocument.path & "\"
sPic = Dir(sPath & "*.emf")

Do While sPic <> ""
    Selection.TypeParagraph
    Selection.InlineShapes.AddPicture _
      FileName:=sPath & sPic, _
      LinkToFile:=False, SaveWithDocument:=True
    sPic = Dir
    Selection.TypeParagraph
Loop
End Sub

Unfortunately, the active directory is in a synced SharePoint folder, so the returned name is in hypertext (http://all the rest/) and with this, all you know what breaks loose. I figured this out by using this code:

Sub GetActiveDocumentPath()

MsgBox ActiveDocument.path

End Sub

So it seems the simple solution is to not use a sharepoint folder to store the items. Anyone have a clever solution for those of us working in the SharePoint environment?

Stack Overflow is not a discussion "forum" - it's a Q&A site. So you shouldn't post a follow-up question in an "Answer" (and it's unlikely you'll get a response in such a situation). One option is to use the [edit] link to modify the title and content of your original question. Another is to start a new question and this one can be closed as "off-topic/not reproducible" (or you can delete it). — Cindy Meister, Jun 14 '16 at 18:07

word vba to set sPath as current directory

2 Answers2