Open document from variable

[amsbam2]

Hi

I am trying automatically open a document whose file and path name is in my original document. I am doing this by reading in the file name into VBA letting it equal to a variable and then telling VBA to open this variable.

However when I do this VBA says the location doesn’t exist. But it does! Any ideas why VBA isn’t letting me do this?

Thanks

This is my code:


Sub tempyd()
i = 1
Selection.GoTo What:=wdGoToBookmark, Name:="startvba"
Selection.MoveDown Unit:=wdLine, Count:=i
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

Dim Bernie As String
Bernie = Selection.Text

ChangeFileOpenDirectory _
"K:\Q2\Procedures and Forms\Official Procedures\Draft 3 of Procedures\"
Documents.Open FileName:=Bernie
End Sub

[fumei]

It is likely that you have some invalid characters, or perhaps a space in your selected text.

BTW, you probably do not need the ChangeFileOpenDirectory.

IF Bernie is valid (and obviously it is not), then:
Documents.Open FileName:="K:\Q2\Procedures and Forms\Official Procedures\Draft 3 of Procedures\" & Bernie
will open the file...again IF Bernie is valid (which obviously it is not).

[amsbam2]

Thanks for you response fumei.

The strange thing is that Bernie seems valid. It is equal to a string which if i then export it is able to open up the file.

So the problem isn't a space missing or any other text problem. I'm guessing the problem is something to do with the format of Bernie.

[amsbam2]

In case it helps the error i am getting is:Run time error 5174

[fumei]

Yes, it would be.

Nevertheless, either the folder path is incorrect, the filename is incorrect, the folder does not exist, or the file does not exist.

It MUST be one of those.

How about posting the EXACT string you are working with?

[amsbam2]

You're right fumei!!

Would you believe, word had automatically added in a space at the end of the file name when creating the variable. And this caused the problem!

Don't know why it happens. However i can overcome it by creating a new variable with the left and len functions.

[fumei]

"Would you believe, word had automatically added in a space at the end of the file name when creating the variable."

Sorry, but no, I do not believe it. Word would not automatically add a space. The space is there already.

Selection.EndKey Unit:=wdLine, Extend:=wdExtend

does it.

Oh, and you may want to look the use of the Trim function.

[Frosty]

Just an added note-- you will get various results using code like
Selection.EndKey Unit:=wdLine, Extend:=wdExtend

...depending on what settings you have in Word. There are a few options, but they all fall under the category of things like "Smart paragraph selection" and "Smart Cursoring" "When selecting, automatically select entire word" etc... Basically, any time you ask Word to be "Smart" or "Automatic" you may end up with something automatic you don't actually want.

[amsbam2]

Frosty, thanks for that. Yes it seems that my problem is to do with this "Smart paragraph selection" option in Word 2007 which means i am also selecting the paragraph mark. Hence my file name is one character too long. Strangely when i unclick this option it still seems to select the paragraph mark. Any suggestions for a code which would not select the paragraph mark?

Fumei, thanks for the trim function suggestion. Unfortunately it doesn't seem to be able to remove the space(i.e. paragraph mark) here. Only the left( ,len()-1) approach seems to do it.

[Frosty]

The TRIM functions (which include RTRIM and LTRIM) only deal with whitespace. I believe they will trim off a non-breaking space, but that's it. I agree with Fumei, that it would probably be helpful to read up on all of the string manipulation functions. There are a lot of ways to address this.

I'm not a fan of what you're doing at all (selecting text in a word document, and then using that text to open a filename), so I would ask a whole bunch of questions to change this process entirely. But that's probably more than you want to do.

For your purposes, using Left in this case is perfectly adequate.

You should probably only remove the last character if it is a "bad" character, i.e.,

Select Case Right(Bernie, 1)
Case vbcr, vblf 'paragraph mark, soft carriage return, as you find more "bad" characters, you can add to this list
Bernie = Left(Bernie, Len(Bernie) -1)
Case else
'do nothing
End Select
'Make sure no spaces
Bernie = Trim(Bernie)

But there are still going to be scenarios you're going to have issues with, which is why this isn't really a good approach. But without knowing a lot more, this will probably work in at least most of your circumstances.

[fumei]

1. I agree with Frosty about selecting text.

2. "Unfortunately it doesn't seem to be able to remove the space(i.e. paragraph mark) here" Unmmm, that is because a paragraph mark is not a space.

[amsbam2]

Thanks for the code Frosty, I have put it into my sub J
The purpose of what I am trying to do is twofold.
1. To have a list of certain key filenames (all word documents) which people can access from a word document (or other easily accessible way), and to have this list be easily edited.
2. Have a macro which is able to do stuff with these files (basically open them, print them, copy and paste their contents, and imbed them in another word document)
What process would you recommend for doing this?