Variable document name
Hi all.
I am trying to find what code I should use, at the moment I have a template file that lots of people are using so it launches under several names depending how many other blank un saved documents they have open such as
Document 1, Document 2, etc
The template has a command button that opens up a different template same again the name can change, Document 1, Document 2, etc the macro the goes back and forth between the documents copying and pasting bits of information.
My question is can I replace the file name of what document to go to once they are open with the name that the system as assigned it.
Hope this makes sense
here is my current code
Code:ChangeFileOpenDirectory "O:\In development - not to be used yet\Master Documents\Master Letter Templates\" Documents.Open FileName:="C1.dot", ConfirmConversions:=False, ReadOnly:= _ False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _ "", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _ Format:=wdOpenFormatAuto Windows("letter loader").Activate ActiveDocument.Shapes("Text Box 19").Select Selection.WholeStory Selection.Copy Windows("C1").Activate If ActiveWindow.View.SplitSpecial <> wdPaneNone Then ActiveWindow.Panes(2).Close End If If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _ ActivePane.View.Type = wdOutlineView Then ActiveWindow.ActivePane.View.Type = wdPrintView End If ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader Selection.MoveDown Unit:=wdLine, Count:=1 Selection.Paste ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument Windows("letter loader").Activate Selection.ShapeRange.Select ActiveDocument.Shapes("Text Box 2").Select Selection.WholeStory Selection.Copy Windows("C1").Activate Selection.MoveDown Unit:=wdLine, Count:=53 Selection.MoveUp Unit:=wdLine, Count:=14 Selection.MoveDown Unit:=wdLine, Count:=1 Selection.Paste ActiveWindow.ActivePane.VerticalPercentScrolled = 21 Windows("letter loader").Activate Selection.ShapeRange.Select ActiveDocument.Shapes("Text Box 9").Select Selection.WholeStory Selection.Copy Windows("C1").Activate Selection.Paste
As soon as the new document opens, immediately grab and store the new workbook name as a string, then feed that string into your "Activate" codes:
Code:Option Explicit Sub Test() Dim Nw As String Application.Screenupdating = False ChangeFileOpenDirectory "O:\In development - not to be used yet\Master Documents\Master Letter Templates\" Documents.Open Filename:="C1.dot", ConfirmConversions:=False, ReadOnly:=False, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ WritePasswordDocument:="", WritePasswordTemplate:="", Format:=wdOpenFormatAuto Nw = ActiveWorkbook.Name Windows("letter loader").Activate ActiveDocument.Shapes("Text Box 19").Select Selection.WholeStory Selection.Copy Windows(Nw).Activate If ActiveWindow.View.SplitSpecial <> wdPaneNone Then ActiveWindow.Panes(2).Close End If If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _ ActivePane.View.Type = wdOutlineView Then ActiveWindow.ActivePane.View.Type = wdPrintView End If ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader Selection.MoveDown Unit:=wdLine, Count:=1 Selection.Paste ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument Windows("letter loader").Activate Selection.ShapeRange.Select ActiveDocument.Shapes("Text Box 2").Select Selection.WholeStory Selection.Copy Windows(Nw).Activate Selection.MoveDown Unit:=wdLine, Count:=53 Selection.MoveUp Unit:=wdLine, Count:=14 Selection.MoveDown Unit:=wdLine, Count:=1 Selection.Paste ActiveWindow.ActivePane.VerticalPercentScrolled = 21 Windows("letter loader").Activate Selection.ShapeRange.Select ActiveDocument.Shapes("Text Box 9").Select Selection.WholeStory Selection.Copy Windows(Nw).Activate Selection.Paste Application.Screenupdating = True End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
when I run the code it stops at
I am sorry if I am doing somthing wrong here but i cannot get it to workCode:Nw = ActiveWorkbook.Name
can you help please?
I have to admit, I just noticed you were trying to open a word document template. I've ever done that, not from inside Excel.
The technique I demonstrated is for getting the name of the "just opened Excel document" and doesn't satisfy your needs. My apologies.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
sorry if i was not clear,
i am in a word document opening a word document
Perhaps this then:
Code:Nw = ActiveDocument.Name
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks