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 |
#2
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 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 :-) |
#3
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
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. wrote: 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 :-) |
#4
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 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 :-) |
Reply |
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 |