Show actual name of file path after you have renamed it

[hornbecky83]

Hi everyone-

I am writing a code in excel VBA for word. I am linking tables from
various excel files to one word document. I need to specifiy which
excel file table is placed into the word document. The code opens up
several excel files of the users choosing, renames that path to
something more general (Study Opt1FE1), and then this is where the
difficulty comes in. I am running for loops and when a certain
combination of numbers in the for loops matches what I renamed my path
files to, the code will link that files tables to my word document.
The code for linking the tables is as follows without the renamed file
path:

Wdoc.Fields.Add Range:=rng, _
Text:="LINK Excel.Sheet.8 ""C:\\Documents and
Settings\\shornbec\\Desktop\\sarah\\excel
templates\\Mission.FY07Q1_Sarah.xls"" ""Report Tables!CostSummaryK"" \a
\f 4 \h", PreserveFormatting:=True

I renamed the C:\\ path to something like "Study Opt1FE2". But the
link will not work when C:\\...is replaced with Study Opt1FE2. Is
there a way that I can have the renamed value display the file path
name value, so I can put the old name value into my text link?

This is the code that I was trying to get the study Opt1FE1 to work.

Dim Wdoc As Word.document
Dim Wapp As Word.Application
Dim rng As Word.Range
Dim x As Integer
Dim y As Integer
Dim vrtSelectedItem As Variant
Dim lngCount As Long
Dim tablestring As String

Sub create_document()

Set Wapp = CreateObject("Word.Application")
Wapp.Visible = True
Wapp.Documents.Add
Set Wdoc = Wapp.ActiveDocument

Call UseFileDialogOpen()

End sub

Public Sub UseFileDialogOpen()

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

'renames file path to more generic names with slight number variations
For Each vrtSelectedItem In .SelectedItems
Workbooks.Open vrtSelectedItem
a = Worksheets("Report Tables Delta V").Range("B1") ' Options
b = Worksheets("Report Tables Delta V").Range("D1") 'Flight
Elements
vrtSelectedItem = "Study Opt" & a & "FE" & b
MsgBox vrtSelectedItem
Next
End With

ThisWorkbook.Activate
Worksheets("more options").Activate
x = Worksheets("more options").Range("A1")
y = Worksheets("more options").Range("A2")

For i = 1 to x
For j = 1 to y

'i was putting together a string that could go in the text link below,
but the Study Opt1FE1 remains. Is
'there a way to get it to display its pathname? I just want the
studyopt1fe1 there so the code now
'where to get the specific excel file's table from.

tablestring = "LINK Excel.Sheet.8" & Chr(34) & Chr(34) & "Study Opt" &
i & "FE" & j & Chr(34) & Chr(34) & """ Report Tables Delta
V!MissionTimelineDeltaVBudgetTable"" \a \f 4 \h"

Wdoc.Fields.Add Range:=rng, _
Text:=tablestring, PreserveFormatting:=True

 

[Cindy M.]

Hi Hornbecky83,

I renamed the C:\\ path to something like "Study Opt1FE2". But the
link will not work when C:\\...is replaced with Study Opt1FE2.

Word requires a "real path", that means a drive or network server
designation, followed by a valid path. "Study Opt1FE2" is not a valid
drive or server name, so you don't have a valid path. A LINK field
does not support a relative path; the path information must be a full
path.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[hornbecky83]

Thanks Cindy for all your help on the earlier posts I had! You've
really helped bring along my VBA code.

I realized what my problem was with this question. I needed to make an
array that would identify with the opened file. I pasted the code I
used at the end. I then used the arrayPath(i,j) and placed it into my
link field.

tablestring = "LINK Excel.Sheet.8" & " " & Chr(34) & arrayPath(i, j) &
Chr(34) & " " & Chr(34) & "Report Tables Delta
V!MissionTimelineDeltaVBudgetTable"" \a \f 4 \h"

Wdoc.Fields.Add Range:=rng, _
Text:=tablestring, PreserveFormatting:=True

This above code pasted it into my document, however, the path name did
not have two back slashes throughout it. It looked like this:
C:\Documents and Settings\Desktop\excel
templates\Mission.FY07Q1_Opt1FE1.xls, instead of this: C:\\Documents
and Settings\\Desktop\\excel templates\\Mission.FY07Q1_Opt1FE1.xls. The
single back slashes would not create a link with the table or allow me
to paste a table at all. Why does it matter if there are double back
slashes or not. I even tried my old code, prior to adding arrayPath,
and took out the double back slashes and it wouldn't work either.
Which tells me the issue must be with the double back slashes. Is
there a way to work around this? Or add double back slashes to my
privious arrayPath(i,j) method? I think this is my final issue with my
code, unless I can't get this figured out. I hope you can help me.
THank you.

Public Sub UseFileDialogOpen()

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount
i = 0
j = 0

For Each vrtSelectedItem In .SelectedItems

Workbooks.Open vrtSelectedItem

' loop through arrayOption to see if this option exists
For Each Ai In arrayOption
' see if we are at the end of the array - if so, add to the
array
Aoption = Worksheets("Report Tables Delta V").Range("B1")

If Ai = "" Then
arrayOption(i) = Aoption
Exit For 'added Option to array
End If

' check to see if we already have the option
If Ai = Aoption Then
Exit For 'already in array - we're done
End If

i = i + 1
Next Ai

' loop through arrayFltElem to see if this option exists
For Each Aj In arrayFltElem

FltElem = Worksheets("Report Tables Delta V").Range("D1")
' see if we are at the end of the array - if so, add to the
array
If Aj = "" Then
arrayFltElem(j) = FltElem
Exit For 'added Option to array
End If

' check to see if we already have the option
If Aj = FltElem Then
Exit For 'already in array - we're done
End If

j = j + 1
Next Aj
arrayPath(arrayOption(i), arrayFltElem(j)) = vrtSelectedItem
Next

End With
End Sub

 

[Cindy M.]

Hi Hornbecky83,

The
single back slashes would not create a link with the table or allow me
to paste a table at all. Why does it matter if there are double back
slashes or not.

This is due to how Word was originally coded, back in the late 1980's.
It was written with C, and in the C-languages a single backslash is an
"Escape character". That means that whatever follows the backslash has a
special significance. You can see that with the switches in Word fields:
a switch is always preceded by a backslash to indicate it is a switch,
so Word should do something special, depending on the character
following the backslash.

In order to indicate that you want to use a backslash, it therefore
needs to be doubled. So paths in Word fields always require double
backslashes.

Or add double back slashes to my
privious arrayPath(i,j) method?

Sure you can double the backslashes. As long as you don't need to
support Word 97, the simplest way is with the Replace function. That
would look something like this

filePath = Replace(filePath, "\", "\\")

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail