Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.word.tables,microsoft.public.word.vba.general
|
|||
|
|||
Show actual name of file path after you have renamed it
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't open file in Office 2003 Word or Excel from Windows Explorer | Microsoft Word Help | |||
How do I correct a corrupt MS WORD file (name or path invalid)? | Microsoft Word Help | |||
What is the maximum number of characters you can use to name a fil | Microsoft Word Help | |||
How do you set last file opened to show in file list 1st | Microsoft Word Help | |||
Macro that will add the file path & date to Word footnotes? | Microsoft Word Help |