Show actual name of file path after you have renamed it

Giganews Newsgroups
Subject: Show actual name of file path after you have renamed it
Posted by:  hornbecky83 (hornbecky…@gmail.com)
Date: 8 Jan 2007

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

Replies