Run macro after InsertFile

Tutorials Win: Microsoft Windows Forum

- Previous
  - 1
    - How to speed up merging tables? (with code sample) Hi all, I'm working on a project that loops through this chunk of code dozens or hundreds of times, and it's taking a long time to complete. Any ideas about how to do this faster? I'm merging each row in the table and then calling a find and replace operation in certain circumstances. Here's the code. (By the way, the reason I'm declaring everything as a generic object is that this code is inside a VB.Net project that late-binds to Word. That's another slow-down: when this code runs inside an instance of Word instead of calling Word through automation, it's much faster. But I have to keep my project in VB.Net, ) _______________________ Sub MergeTable(ByRef oTable As Object) ' merge all cells on each row together ' then handle one of the following situations: ' 1) the row is NOT metadata, so replace the first para mark (put in by merging) with a tab ' 2) the row is metadata, and originally "Ans:X" was in its own cell, so there's no para mark ' to worry about removing (the next metadata field will have been put on its own line, but that's ' what we want ' 3) the row is metadata, but the file was set up so that "Ans:" was its own cell; so we need ' to remove the first para mark so "Ans:" and the answer are together on the same line Dim i As Integer With oTable For i = 1 To .rows.count .rows(i).Cells.Merge() ' chr(13) is a para mark ' if the row has "Ans:[para mark]" or DOESN'T have "Ans:" at all, then call ParaMarkToTab If InStr(.rows.item(i).range.text, "Ans:" & Chr(13)) <> 0 Or InStr(.rows.item(i).range.text, "Ans:") = 0 Then Call ParaMarkToTab(.rows.item(i).Cells.item(1)) End If Next i End With End Sub Sub ParaMarkToTab(ByRef oCell As Object) ' turns the extra paragraph mark made when merging cells into a tab ' it stops after the first one, so it won't mess up any existing paragraph marks oCell.range.Find.Execute(FindText:="^p", Replacewith:="^t") End Sub Tag: Run macro after InsertFile Tag: 103560
  - 2
    - Populate a VBA variable from text on the page I'm currently copying a text with numbered verses in each chapter from a web site and pasting to Word. There are three translations of each verse. I created a Do loop to remove 2 of the translations from each verse. The loop length is controlled by an integer variable I populate via an InputBox function. It occurred to me that it would be easy enough to capture that number after the paste (it's the number of the last verse), and then put it into the code itself and dispense with the input box. Sort of. I can get the cursor to locate that number and select it, but I'm stumped as to how to get that number into the VBA code to populate the variable. If anyone knows how to do this, much appreciated if you could share. Thanks. Tag: Run macro after InsertFile Tag: 103553
  - 3
    - Insert PageBreak before certain Styles I would like to insert a page break before any paragraph that has a style starting with "Heading". This code Inserts XXPBXX and is later search/replaced by a page break. This code runs very slow, and there must be an easier way. Dim doc As Document Set doc = ActiveDocument pCount = doc.Paragraphs.Count Application.ScreenUpdating = False For p = 1 To pCount If Left(doc.Paragraphs(p).Style, 7) = "Heading" Then doc.Paragraphs(p).Range.Select Selection.InsertBefore "XXPBXX" End If Next Application.ScreenUpdating = True Tag: Run macro after InsertFile Tag: 103542
  - 4
    - Email OnSave? searched Google, MSDN Blogs, MVPs & here w no result Scenario: Word 2007, WindowsXP: I create a Table of Contents (or any doc) and save it, THEN, a person in my group modifies the document and doesn't tell me. I create and print tabs that match the TOC I created only to discover that the TOC or outline was changed which wastes my time and $$ for expensive direct apply tabs. There are too many projects with too many people to effectively 'monitor' all the TOCs and outlines. Solution: Ideally, I would 'subscribe' to the document or programatically have an alert sent to me anytime the document is modified. Is there a way to write a macro to Send Email on Save or possibly another solution? -- Thank you, scrowley(AT)littleonline.com Tag: Run macro after InsertFile Tag: 103541
  - 5
    - Microsoft Office Controls Not In Office 2007 I had posted a message back on July 20 asking if anybody knew what happened to the VBA controls "Microsoft Office Chart" and "Microsoft Office Spreadsheet." Nobody responded, so apparently nobody else knew either. I submitted a paid-for question to Microsoft, and even they had a very difficult time coming up with the answer, which is that Microsoft intentionally omitted these controls in the "Office Web Components" category from Office 2007. Microsoft's rationale has to do with the Web capabilities of the components. However, I'll bet many, if not most, people used these simply as ways of interacting between a VBA program and a chart or spreadsheet, locally on the PC. There is a download to put the Office 2003 components in Office 2007, but if you are like me, I operate on a University computer system, and the system administrator is not about to get concerned about downloading special software such as this. I think Microsoft would be eager to enhance the ability to interact between VBA and Office components, and think there ought to be controls to do that, and that they don't need to address the more complex issue of operating on the Web, if that's a concern. If you think likewise, it might help to have as many as possible contact Microsoft, and get them to add the appropriate controls into Office 2007. Here are some links on the issue: http://en.wikipedia.org/wiki/Office_Web_Components http://blogs.msdn.com/excel/archive/2006/07/17/668544.aspx http://support.microsoft.com/kb/319793 http://technet2.microsoft.com/Office/en-us/library/0dee24b3-09af-485b-b5ed-d4b879dcc8f61033.mspx?mfr=true You can download the OWC pack for Office 2003 and use it, below is the link for download: http://www.microsoft.com/downloads/details.aspx?FamilyId=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en Tag: Run macro after InsertFile Tag: 103539
  - 6
    - Array Question Can you add a hard return in an array? So when picked it would look like this in my document: This is a test How does it look Instead of this: This is a test How does it look My macro is as follows: Dim myArray myArray = Split("This is a test How does it look", "|") With Cmb1 .List = myArray .ListIndex = 0 .MatchRequired = True End With LEU Tag: Run macro after InsertFile Tag: 103536
  - 7
    - Custom Property - what am I doing wrong? This is driving me crazy! I have a .dot I'm using to create forms that connect to an Excel spreadsheet (to populate a userform drop down box). I want to have three custom properties I can use anywhere in the document (one is for the path to the Excel data source, one is the location to save the completed forms and one is the location of the document template for opening new blank forms). Two of my three custom properties work fine (data source and destination path). I cannot get the document template location to work. I want to have these set in one place so if we need to move/copy the form, data source or change the destination path, all we need to do is change the custom properties, not the code. Here's my custom properties: Name: SourceFile Type: Text Value: G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls Name: TemplateFile Type: Text Value: G:\GS-220\SLATE\CORRECTION_SHEETS\Correction_Sheet.dot Name: CompletedDestination Type: Text Value: G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\ The SourceFile and CompletedDestination work fine, but the TemplateFile gives me a Run-time error 13, Type mismatch. Here's the code where I call it from: Sub NEWFORM() Dim CURRENTDOCNAME As String CURRENTDOCNAME = ActiveDocument.FullName Word.Documents.Add ActiveDocument.CustomDocumentProperties("TemplateFile") Documents(CURRENTDOCNAME).Close End Sub Can anyone tell me what it is that I'm doing wrong?!? Tag: Run macro after InsertFile Tag: 103535
  - 8
    - Access an Excel Form from Word Hello, I would like to call upon a form in global.xlm from a form in word. Is there any way to do this? Thank you very much, Tim Tag: Run macro after InsertFile Tag: 103532
  - 9
    - VBA code (Where does it live) When you open a template there is no vba code. However when you hit alt f11 the VBA editor comes up and there is code. Where does that code live? Is there a file that contains the code? Tag: Run macro after InsertFile Tag: 103528
  - 10
    - VBA code Word Templates The company I work with uses inline forms (static .dot files) as resources that can be added to a working template. In this context I use the document.add command and bring these forms into the "working" template environment. We are now merging with another company and have to make changes to all these references. And I am not talking about the standard references that vb programmers are used to. I am talking about places in the VBA code that do something like this: document.add "\\root\formdir\form1.dot" With the merger the form1.dot (in this case) needs to be changed to some other name. However, there are thousands of these type of references. If the VBA code was in a text file then this would not be an issue. But as it is now we need to open each and every .dot to find out where these references are. What I want to know is this. Is there a way that I can find these references "enmass" and even change form1 as a part of these search process? Tag: Run macro after InsertFile Tag: 103524
  - 11
    - Change the Excel source for an embedded chart - Word 2002 Changing the Excel source for an embedded chart - Word 2002 Hi, I have a word document with numerous links to individual cells, ranges and charts in an Excel sheet. I'm trying to change the links programmatically so that all Excel objects will point to a different spreadsheet. I've written the code below to do this : Public Sub ChangeSource Dim intCount As Long Dim strNewLink As String strNewLink = "\\bwci-server-1\svr\_Template\ValnTemplate_CI_Test.XLS" With ActiveDocument ' Loop through all the fields in document. For intCount = 1 To .Fields.Count With .Fields(intCount) ' If the field's type is an excel link then... If .Type = 56 Then With .LinkFormat ' change the source to the new source .SourceFullName = strNewLink End With End If End With Next intCount End With End Sub The code works fine for individual cells or ranges of cells but falls over on the charts with error 6083 "Objects in this document contain links to files that cannot be found. The linked information will not be updated." This seems to be connected with the way Word writes the field code for a linked chart as opposed to a linked cell The linked cell field code has only one reference to the filename eg. { LINK Excel.Sheet.8 "\\\\bwci-server-1\\SVR\\_Template\\ValnTemplate_CI.XLS" "MemChngeData!R8C3" \a \t } Whereas the linked Chart field code has 2 eg. { LINK Excel.Sheet.8 "\\\\bwci-server-1\\SVR\\_Template\\ValnTemplate_CI.XLS" "MemChngeGraph![ValnTemplate_CI.XLS]MemChngeGraph Chart 3" \a \p } I suspect that my code is changing the first occurrence of the filename but not the second for the charts. Iâ??d like to know 2 things: 1) What is the property or code that I need, to change the links for charts? 2) How do I detect that Iâ??m looking at a chart and not cells (as they both seem to have the same type - 56)? Thanks for your help Simon Tag: Run macro after InsertFile Tag: 103523
  - 12
    - Inlineshapes I am using an inlineshape as a signature on letters. The user can automatically insert a scanned signature. (controlled by Access 2003) The problem I have is removing the signature if someone else want to sign the document. I have a routine which removes all inlineshapes from a document but there is a danger that a user may insert their own inlineshapes and these would also be deleted. How can I identify a particular shape and then delete it? Alex Tag: Run macro after InsertFile Tag: 103517
  - 13
    - How to identify current format data for use in macros? I have macros for inserting specific characters, but in some cases I need to use a font that is different from the main body of the text. I want to make the macro universal, so that no matter what the original font is, the macro will identify that font, then insert the symbol which may specify a different font, and then after the insert reset the font back to the original. Stated otherwise I need to know how to identify the default font type, so I can return to it. Tag: Run macro after InsertFile Tag: 103509
  - 14
    - How can I automatically change the path names of Excel OLE objects in Word Document Hi! I created a Microsoft Word document that contains dozens of linked objects (OLE's). I am trying to change the path name in the "embedded objects" within the Word. I want the macro to scan through each page in the document and find each embedded object (which are linked to an excel file), then find the pathname: "C:\drive\documents\file1.xls" and replace it with "C:\drive\documents\file2.xls". I have the following as a beginnig point but I could not finish it. This is my first attempt to write a VBA code. Any help you could provide will be greatly appreciated. Here is my incomplete code: Sub ChangeSource() Dim k As Long 'Create a variable to store the object reference string. Dim strLink As String With ActiveDocument ' Loop through all the floating shapes in document. For k = 1 To .Shapes.Count With .Shapes(k) ' If the shape's type is an OLE object then... If .Type = msoLinkedOLEObject Then ' Change the path to new source and set the update ' type to Automatic. With .LinkFormat ' Get the source path in a string strLink = .SourceFullName ' Do something to strLink to modify: 'Code to modify strLink .SourceFullName = strLink .Update End With End If End With Next k End With Tag: Run macro after InsertFile Tag: 103505
  - 15
    - double merge made the following macro got almost all I want except. When it goes to printer is the problem. What I am after is for it to go to printer without user input. Presently it stops at the screen for the printer options. If you choose properties to get next screen for fast draft when you check ok it injects all the merge fields above the ones in place. These are in a straight line not above one another as those all ready in place. Somwhere in the action to printer sec0ond set of fields appear. Got pass the selection for the excel sheet needed. now double merge HELP Thanks I have left in all of my (') lines after it works right I will clean tyhem out Here it is Also I ask does it matter the order of the fields as they place on letterhead in the macro. It would seem that the placement on doc is the factor. Thanks to all Heres hopeing Sub Macro3() ' ' Macro3 Macro ' Macro recorded 7/21/2007 by Curtiss A. Greer ' ChangeFileOpenDirectory "C:\Parade" Documents.Open FileName:="LetterHead.doc", ConfirmConversions:=False, _ ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _ PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _ WritePasswordTemplate:="", Format:=wdOpenFormatAuto = 0 ' Selection.TypeParagraph ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\MailEcopy.xls", _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="entire sheet", SQLStatement:="", SQLStatement1 _ :="" ActiveDocument.MailMerge.EditMainDocument ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Contact_Person" ' Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Address" ' Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "CityState" ' Selection.TypeText Text:=" " ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Zip_" ' Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & _ ' vbTab Selection.InsertDateTime DateTimeFormat:="M/d/yyyy", InsertAsField:=True, _ DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _ InsertAsFullWidth:=False ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM C:\Parade\MailEcopy.xls WHERE ((Contact_Person IS NOT NULL ))" & "" With ActiveDocument.MailMerge .Destination = wdSendToPrinter ' .MailAsAttachment = False ' .MailAddressFieldName = "" ' .MailSubject = "" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=True End With End Sub Tag: Run macro after InsertFile Tag: 103502
  - 16
    - Open and Save to iManage I'm going to need to build an application using Word 2003 that will be able to open documents stored in iManage, then save them back in iManage after they have been edited. I want to do this using VBA code. I've done a lot with Access and Excel VBA coding, but this is my first attempt with Word. Any suggestions would be appreciated. Thanks, Steve Tag: Run macro after InsertFile Tag: 103499
  - 17
    - ShapeRange Selection The following snippet is part of a larger procedure which places AutoText graphics in a header and then moves them to the correct location: ActiveWindow.ActivePane.View.SeekView = wdSeekFirstPageHeader 'enters the autotext graphic located in the attached Template ActiveDocument.AttachedTemplate.AutoTextEntries(pString).Insert _ Where:=Selection.Range, RichText:=True 'first select the graphic ActiveDocument.Sections(1).Headers(wdHeaderFooterFirstPage).Range.ShapeRange.Select With Selection.ShapeRange .RelativeHorizontalPosition = _ wdRelativeHorizontalPositionPage .RelativeVerticalPosition = _ wdRelativeVerticalPositionPage .Left = CentimetersToPoints(2.5) .Top = CentimetersToPoints(1.99) .WrapFormat.AllowOverlap = True End With If I run the template all works correctly. However, if I open the template and then run this procedure to test the code I get an error 4605 telling me that the "RelativeHorizontralPosition method or property is not available because the drawing operation cannot be applied to the current selection" The graphic (ShapeRange) is shown as selected. I then Exit at the error message dialog, close the Header/Footer view and try it again. It now works correctly from the second time onwards. Could someone explain why this is happening, please? Roderick O'Regan Tag: Run macro after InsertFile Tag: 103498
  - 18
    - Macro variables availability Can you establish variables and have them accessible to all macros within a document - if you set them up in a .dot file? I'd like to have one location to store the following variables: Dim DataSourceFile As String Dim DocumentTemplateFile As String Dim CompletedFormsPath As String DataSourceFile = "G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls" DocumentTemplateFile = "G:\GS-220\SLATE\CORRECTION_SHEETS\Correction Sheet.dot" CompletedFormsPath = "G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\" I'd like to have them accessible to the entire module, userforms, etc. This way, if we move the documents around in the network (or copy them for that matter) we only have to change the paths in one area (and I can even set up a userform or macro to adjust them as needed). Is this possible? Tag: Run macro after InsertFile Tag: 103494
  - 19
    - Update Custom Property - saved by history I have a problem with a Word doc. We have users that try to open the doc while someone is working on it. They open a copy, then make their changes, but instead of merging the changes, they overwrite the document. I'm trying to find a way (maybe with custom properties?) that I can write a macro that runs on save. This macro would updtae some kind of custom property with who is saving and when. This property will build on itself...so ever time it runs it updates the CustomProperty to CustomProperty + "|" + currentuser + currentdatetime. Does anyone have any idea how I can tackle this? I'm kind of getting tired of being the one blamed for not updating it when I'm the first one in and everyone else keeps overwriting my changes. Thanks!! Tag: Run macro after InsertFile Tag: 103479
  - 20
    - Macro o collect titles and dates In the Document Properties I use the title field for the title of the document and the comments field for the effective date. I need to create a document with both the titles and effective dates for all the documents in the directory. Is this possible with VBA? Thanks, Mickey Tag: Run macro after InsertFile Tag: 103473
  - 21
    - Is there a faster way to do this? This sub lets the user input a date & then proceeds to list all of the Insert/Delete/Replace chnages that have been made a Microsoft Word Document since that date. However, this takes a long time to complete, even with small documents & with large docs, it takes a prohibitive amount of time. The results are listed in a Word Document, with a tab printed in between the "columns" (no table was used, as I found that using a table for the output made this run even slower). Does anyone know of a more effiicient way to tally up the differences? Here's the code I have so far & thanks in advance! Sub TrackByDate() Dim srcDoc As Document, destDoc As Document Dim oRev As Revision Dim strCkDate As String Dim ChangeTxt As String Dim FileName As String Dim CkDate As Date Dim RevType As Variant Dim nRows As Long RevType = Array("NoRevision", "Insert", "Delete", _ "Property", "ParagraphNumber", "DisplayField", _ "Reconcile", "Conflict", "Style", "Replace", _ "ParagraphProperty", "TableProperty", _ "SectionProperty", "StyleDefinition") strCkDate = InputBox$("Enter date (MM/DD/YYYY) to find all changes made since:") If strCkDate = "" Then Exit Sub If Not IsDate(strCkDate) Then Exit Sub CkDate = CDate(strCkDate) Set srcDoc = ActiveDocument Set destDoc = Documents.Add destDoc.PageSetup.Orientation = wdOrientLandscape destDoc.Sections(1).Headers(wdHeaderFooterPrimary) _ .Range.Text = "Revisions in " & srcDoc.FullName nRows = 1 destDoc.Range.Text = "Revisions in " & _ srcDoc.FullName & " since " & strCkDate & _ vbCr & vbCr & "Date" & " " & " " & "Time" & " " & vbTab _ & "Page" & vbTab & "Line" & vbTab & "Change" & vbTab & "Text" & vbCr & vbCr For Each oRev In srcDoc.Revisions nRows = nRows + 1 If CDate(Left$(Format(oRev.Date, "MM/DD/YYYY"), 10)) _ >= CkDate Then If (RevType(oRev.Type) = "Insert" Or _ RevType(oRev.Type) = "Delete" Or RevType(oRev.Type) = "Replace") Then ChangeTxt = oRev.Range.Text ChangeTxt = Replace(ChangeTxt, vbCr, " ") ChangeTxt = Replace(ChangeTxt, vbLf, " ") ChangeTxt = Replace(ChangeTxt, vbCrLf, " ") destDoc.Range.InsertAfter _ oRev.Date & " " & vbTab & _ oRev.Range.Information(wdActiveEndAdjustedPageNumber) & vbTab & _ oRev.Range.Information(wdFirstCharacterLineNumber) & vbTab & _ RevType(oRev.Type) & vbTab & " " & ChangeTxt & vbCr End If End If Next oRev End Sub Tag: Run macro after InsertFile Tag: 103471
  - 22
    - Custom Toolbar Editing Word 2002 & Migration to 2007 We previously had templates written that included a custom toolbar with a drop down list for personal letterhead. I have found the letterhead macro and have been able to edit it (Global.dot), but I do not see the toolbar macro or where in the macro the toolbar is referenced in order to edit the drop down list. Can anyone help me? The people who wrote the script have resigned and given us absolutely no instruction on how they developed the macros, etc. -- BA Pease -- BA Pease Tag: Run macro after InsertFile Tag: 103469
  - 23
    - how do I change measurement from cms. into inches can anyone advise how I change ruler measurements from cms. into inches? Tag: Run macro after InsertFile Tag: 103459
  - 24
    - Use current heading level in a field Is there a way to get the current heading level from a field? I can't use a StyleRef field because I don't know what style to look for (i.e. Heading 1, Heading 2 etc.) Thanks (I realize this is the VBA forum) Tag: Run macro after InsertFile Tag: 103444
  - 25
    - UserForm and macro woes Ok, here's where I'm at. I have a document template with a userform with three check boxes and a drop down. When the form loads, the code has it go to a spreadsheet and return all schools from column 1 ('schools') of a 4 column table. I want the users to be able to 'filter' the list returned by clicking one or more of the checkboxes. When the user clicks checkbox1 (STAFFcheckbox) to True, I want the list to 'filter' to only those schools. I have the code working, and I've set it up so that it checks the status of all three check boxes (so I can use one code when any one of the three buttons are changed). Problem is, I should have this code in a module and not tied to each of the three buttons (making three copies of the code). I can't seem to get the buttonclick action to run the macro. Here's my code on the userform: Sub autonew() Load UserForm1 Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long ' Open the database Set db = opendatabase("G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls", False, False, "Excel 8.0") ' Retrieve the recordset 'Set rs = db.OpenRecordset("SELECT * FROM `schools` WHERE SCHOOLS <> ''") Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> ''") ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset UserForm1.ComboBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords) 'Me.Repaint ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing UserForm1.Show End Sub So when the form is new, I get my userform just fine. Now, let's set a checkbox: Sub UPDATELIST() Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long Dim CONDITION As Integer ' Open the database Set db = opendatabase("G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST_NEW.xls", False, False, "Excel 8.0") ' Retrieve the recordset Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> ''") 'Stafford Only If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox = False Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> '' AND STAFF = 'X'") End If 'PLUS Only If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox = False Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> '' AND PLUS = 'X'") End If 'GRAD PLUS Only If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox = True Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> '' AND GPLUS = 'X'") End If 'Stafford/PLUS If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = False Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> '' AND STAFFORD = 'X' and PLUS = 'X'") End If 'PLUS/GRAD PLUS If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox = True Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> '' AND PLUS = 'X' and GPLUS = 'X'") End If 'Stafford/GRAD PLUS If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox = True Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> '' AND STAFFORD = 'X' and GPLUS = 'X'") End If 'Stafford/Plus/GRAD PLUS If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = True Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> ''") End If 'NONE If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox = False Then Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE Schools <> ''") End If ' Determine the number of retrieved records With rs .MoveLast NoOfRecords = .RecordCount .MoveFirst End With ' Set the number of Columns = number of Fields in recordset UserForm1.ComboBox1.ColumnCount = rs.Fields.Count ' Load the ListBox with the retrieved records UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords) 'Me.Repaint ' Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub Now, I can put that code in each button click, but that would be 3 areas I'd have to update if our source list changes (adds columns, etc.). I want have the userform's checkboxclick action run this code: Private Sub GRADCheckBox_Click() Application.Run (UPDATELIST) End Sub Private Sub PLUSCheckBox_Click() Application.Run (UPDATELIST) End Sub Private Sub STAFFCheckBox_Click() Application.Run (UPDATELIST) End Sub When I put the code in this way, nothing happens when I click a check box, but the code runs when it's in each checkbox click action. What am I doing wrong? Thanks! Tag: Run macro after InsertFile Tag: 103438
- Next
  - 1
    - Keystrokes Hello I would like to know how to handle some keystrokes. I have a textbox on a form. If that textbox has the focus, characters enter into the textbox when I hit the keyboard. However, if the textbox doesn't have the focus, where is the information of the keystrokes going to? And can I still use those keystrokes? Is there a way to use the keystrokes that seems to go into 'space'? Another question is (perhaps related) I am working on a form and in a document almost simutaneously. I type text continiously, then I just want to hit a check box or an option box, so that the text that I type goes solely into the document and not in the textbox and when I hit the checkbox again, the text should go solely into the textbox and not in the document. The form should not unload or hide while I am typing text into the document. Is that possible? Thanks in advance, Sunouchi Tag: Run macro after InsertFile Tag: 103437
  - 2
    - How can I programmatically relink my linked Excel worksheets in MS I created a Microsoft Word document that contains dozens of linked Excel worksheets. I am searching for a method to programmatically search and replace all these links. In searching online, I found code to perform this task for both Access and PPT ... but not for MS Word. I have also found a program called "ReplaceMagic," but I need this solution for the workplace and they'll never let me download third-party software for this. I need some Visual Basic code. Here are the solutions I discovered for Access and PPT, available on the Microsoft website: Access: "ACC2000: How to Programmatically Link or Embed an Object on a Form (Article ID 209990)" PowerPoint: "PPT2000: Sample Code to Change Source of Linked Excel Worksheet (Article ID 222708)" If anyone has a solution for Word, I'd really appreciate it! Tag: Run macro after InsertFile Tag: 103436
  - 3
    - Closing inactive document I have a template that I set up that links to a spreadsheet. The userform in the .dot gets a list of school names from the Excel document, then presents those in a userform for the user to select. When they select the school, it populates the bookmarks I set up and the user completes the form. When they are done, I have a macro/toolbar setup to save the document using some of the bookmarks inthe document. Now comes the fun part...if the user wants to complete another form, I want to open a new document, but then immediately close the saved document. .dot opens a doc (document1) User completes document1 User saves document1 as uniquenameddoc1 Macro needs to have .dot open a new doc (document2), then close uniquenameddoc1 Here's my code: Sub SaveFormAs() Dim pFileName As String pFileName = Year(Now()) & Format(Month(Now()), "00") & _ Format(Day(Now()), "00") & "-" & _ ActiveDocument.FormFields("SchoolName").Result & "-" & _ ActiveDocument.FormFields("APP_NAME").Result ActiveDocument.SaveAs "G:\Forms\" & pFileName & ".doc" End Sub Sub newform() Dim CURRENTDOCNAME As String CURRENTDOCNAME = ActiveDocument.FullName Word.Documents.Add Template:="G:\forms\Correction Sheet.dot" Windows(CURRENTDOCNAME).Close End Sub This opens the new form, but it doesn't close the old form(s) until I close my last document. Am I missing something here? Thanks! Tag: Run macro after InsertFile Tag: 103433
  - 4
    - preventing printing on certain printer Hi, I have 5 networked printers. Is there a way I can use VBA to prevent printing on a certain printer? ie: on print if chosen printer = xyz then msgbox("this printer is broke") stop print send end if Thanks. Tag: Run macro after InsertFile Tag: 103430
  - 5
    - extract text from form dropdown to Excel Client is using Excel 2003/Win XP. He received an 8 page Word form with many dropdown boxes. He is looking for a way to extract the data from the dropdowns into Excel. Thanks for your help!! -- maryj Tag: Run macro after InsertFile Tag: 103428
  - 6
    - Can't exit design mode issue with Word 2003 to Word 2002 We have a Word document that is set up as a form, it contains several ActiveX controls and does have a few VBA macro's associated with the controls. The document has had a number of revisions using Word 2002 without any issues. And the form gets loaded to our corporate portal for users to download and use. Our problem is the document had been edited in Office 2003, some text had been deleted, and text added to a few Drop-Down Form Fields. The document is protected when finished. What we found was if the document is opened by users of Word 2002 (XP) they get a MS Visual Basic dialog box warning: Can't exit design mode because Control '<control name>' can not be created. When the document is unprotected we find that the various ActiveX controls have had the Name property changed, by appending a 1 to the end of the control name. Naturally if the document is saved when opened in Word 2002, the name change to the ActiveX control is also saved, corrupting the document for all users. Track changes is turned off for the document. And I've been able to consistently replicate the same behavior on different machines. And all machines use XP Pro. Questions of course are why is this occurring - as this is consistent this isn't just the usual 'Office having problems with ActiveX copy and paste to a new document' situation. And of course a suggestion as to a workaround? Thanks in advance! Tag: Run macro after InsertFile Tag: 103427
  - 7
    - Prevent macros that add menus from updating normal.dot Hello all, I've written macros that create a menu list on the menu bar, modified some shortcut menus, and adds a toolbar, all custom designed to run other macros that I have written. I saved the document as a .dot template and stored in the word start-up directory. All menus are created fine. The problem is, when I load it as a global template, Word saves all the menu modifications to normal.dot. I've written macros to delete the menus on exit, but they still exist in the normal.dot file. This is a problem particularly with the toolbar menu which gets duplicated in normal.dot everytime word is started with the gloabl template I designed. Is there a way to prevent Word from modifying the normal.dot file when I run macros that create custom menus, or is there another way to prevent this problem? The problem persists with Word 2000 and 2003. I tried deleting the normal.dot file in case it was corrupted, but that didn't fix the problem. Thanks in advance, Microsmith Tag: Run macro after InsertFile Tag: 103425
  - 8
    - MacroButton to Accept all changes shown I am trying to program a new template that will allow end users to view the template, see instructions to explain what should be contained in each paragraph. The MacroButton to allow Changes only allows one line of instructions. Is there a way to override this and allow for more than one line of instructions. I need to be very detailed in the instructions. Tag: Run macro after InsertFile Tag: 103421
  - 9
    - How can I programmatically relink OLE's in a Word document? I created a Microsoft Word document that contains dozens of linked objects (OLE's). I am searching for a method to programmatically search and replace all these links. In searching online, I found code to perform this task for both Access and PPT ... but not for Word. I have also found a program called "ReplaceMagic," but I need this solution for work and they'll never let me download third-party software for this. I need a macro. Here are the solutions I discovered for Access and PPT: MS Access: "ACC2000: How to Programmatically Link or Embed an Object on a Form (Article ID 209990)" http://support.microsoft.com/kb/209990 MS PowerPoint: "PPT2000: Sample Code to Change Source of Linked Excel Worksheet (Article ID 222708)" http://support.microsoft.com/kb/222708 If anyone has a solution for Word, I'd really appreciate it! Tag: Run macro after InsertFile Tag: 103420
  - 10
    - Arrow Keys and Forms In all the windows and web form designers that I have used you can use the arrow keys to position the controls on the forms. However in the VBA user forms it does not seem that you can use the arrow keys. Any suggestions as to how you can use them? Tag: Run macro after InsertFile Tag: 103417
  - 11
    - Run a remote application Hi, I have an applicattion that copies several documents to a server, that server has a batch file that copies the documents to other locations that only it can see, I need to be able to run the batch file on the server but when I try it runs the batch file locally on my PC instead of the server, could someone point me in the right direction please. Thanks David Tag: Run macro after InsertFile Tag: 103414
  - 12
    - Word drop down form field - how to use macro to look up data from access Hi there. I am trying to make a macro to use within a dropdown form field in word. I have a word doc with a form and a dropdown form field, in where I would like to use this macro to lookup the costumers names and adresses in a acces db. In the dropdown form field options in word, it seems I can use a macro, but Im not sure how to program it i VB. Can anyone please help. Many thanks MHO Tag: Run macro after InsertFile Tag: 103410
  - 13
    - Adding entries to 2007 QAT Hoping that VBAwould record my steps, I added one of my macros to an icon that would sit in the 2007 Quick Access Toolbar. I got the icon in place, but none of my steps were recorded. Does anyone know the VBA code (in general) that will allow me to add different macros to the QAT? Ed (in Virginia) Tag: Run macro after InsertFile Tag: 103409
  - 14
    - Word Macro Processor Bug? I am writing in regard to what appears to me to be a bug in the macro processor for Word... though it may be that I am doing something wrong. The macro I have recorded/written is as follows: Sub changeRaisedtoSuperscript() Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "([0-9]*)" .Font.Position = "2.5" .Replacement.Text = "\1" .Forward = True .Format = True .MatchWildcards = True End With With Selection.Find.Replacement.Font .Position = 0 .Size = 8 .Superscript = True .Subscript = False End With Selection.Find.Execute Replace:=wdReplaceAll End Sub As you can see, this is a very simple macro to convert numeric characters which are raise by 2.5 points (to simulate superscript) into real Word superscripts. The trouble is that whenever the macro runs, the "2.5" is interpreted as "2". I tried all means to get the ".5" into the macro without success. I encountered this bug in Word 2003, and now in Word 2007. I would really appreciate if someone could suggest a solution to the problem. Thanks /John Tag: Run macro after InsertFile Tag: 103395
  - 15
    - Good News Mr 142.179.22.210 flood microsoft.public.word.vba.general Cox and Telus will not act unless they have a police file number so you may as well flood microsoft.public.word.vba.general my home Usenet group. have a nice day.Me I am nearing about TrueCrype -- Her delegation was naked, respectable, and conveys out of the background. -- Michael Yardley Tag: Run macro after InsertFile Tag: 103392
  - 16
    - Good News Mr 142.179.22.210 flood microsoft.public.word.vba.general Cox and Telus will not act unless they have a police file number so you may as well flood microsoft.public.word.vba.general my home Usenet group. have a nice day.Me I am nearing about TrueCrype -- They foster the dramatic lb and finish it near its database. -- Michael Yardley Tag: Run macro after InsertFile Tag: 103390
  - 17
    - Good News Mr 142.179.22.210 flood microsoft.public.word.vba.general Cox and Telus will not act unless they have a police file number so you may as well flood microsoft.public.word.vba.general my home Usenet group. have a nice day.Me I am nearing about TrueCrype -- Allahdad binds the institute in support of hers and effectively places. -- Michael Yardley Tag: Run macro after InsertFile Tag: 103383
  - 18
    - select information to print in template I am trying to determine if this is possible in Word 2000 and if so, figure out which tool would best support this type of automation. Basically I am trying to create a word template that can be used as a form letter where the user would select particular verbiage depending on the terms of the transaction. The letter starts and ends in basic paragraph format but the body is separated into sections. For example: Dear Joe Schmoe, We are pleased to offer you the following. Line: $<<Insert field>> Term: << field>> months End of Term Options: ??This is where I am lost?? Additional Conditions: ???Another spot like above??? There are many variable End of Term Options/Additional Conditions etc. The users would like to select just one option (which can be almost a paragraph long in some cases) and insert it into this proper space for printing as if it had been typed. I have been looking at using Forms drop-downs, Pop-up lists and Active X controls but seem to be going in circles. I am quite new at VB and generally depend on recording a macro and tweeking it for specific usage. Any direction would be GREATLY appreciated. Sorry so long winded. Thank you for your time! Tag: Run macro after InsertFile Tag: 103379
  - 19
    - Problems with Command Button with VBA code Hello, I created a form in Word. It is saved as a read-only template and it's protected so that users can only change the form fields. There is a command button called "E-Mail Document" that users can click and the document is e-mailed to a specific address. The form is meant to be used over and over again. I've tested the form repeatedly and the control button always works. However, my users are reporting that it is not working. When I go into the forms they are sending back, the VBA code is gone. How is this happening?? My code is below. Thank you! --------------------------------------------------------------------------------------------- Private Sub EMailDocument_Click() Dim bStarted As Boolean Dim oOutlookApp As Outlook.Application Dim oItem As Outlook.MailItem On Error Resume Next If Len(ActiveDocument.Path) = 0 Then MsgBox "Document needs to be saved first" Exit Sub End If Set oOutlookApp = GetObject(, "Outlook.Application") If Err <> 0 Then Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If Set oItem = oOutlookApp.CreateItem(olMailItem) With oItem .To = "REDACTED" .Subject = "Site Visit Document" 'Add the document as an attachment, you can use the .displayname property 'to set the description that's used in the message .Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue, _ DisplayName:="Document as attachment" .Send End With If bStarted Then oOutlookApp.Quit End If Set oItem = Nothing Set oOutlookApp = Nothing End Sub Tag: Run macro after InsertFile Tag: 103378
  - 20
    - Fill combo boxes from Access tables I have a Word template (dot) with a UserForm that opens and has several combo boxes for the user to make selections. It then uses these selections to build a letter. I've also created an Access database with tables and fields to populate the various combo boxes on the UserForm in Word. I've managed to figure out how to do this with 1 combo box (cboHOName) on the form and 1 table/field from Access but now want to populate the other combo boxes as well from the other tables inside the same Access database. For example in tblOffices I have an Address1 and Address2 field with corresponding combo boxes (cboAddress1 & cboAddress2) on the Word UserForm and tblTitles with the EETitle field and combo box cboEETitle. I'm just not sure how to modify my code to incorporate these as well. Here's what I have, please help! Private Sub UserForm_Initialize() Dim dbDatabase As Database Dim rsTemplates As Recordset Dim i As Integer Dim aResults() 'Activates the Database connection. Set dbDatabase = OpenDatabase("K:\AcsData\ROTemplatesDB.mdb") 'Opens the Offices table. Set rsROTemplates = dbDatabase.OpenRecordset("tblOffices", dbOpenSnapshot) i = 0 With rsROTemplates 'Populate the combobox with the values in the cboHOName field. Do Until .EOF cboHOName.AddItem (i) cboHOName.Column(0, i) = .Fields("HOName") .MoveNext i = i + 1 Loop End With End Sub Tag: Run macro after InsertFile Tag: 103377
  - 21
    - Encoding I am trying to learn about encoding, and have read a lot about it. However, what I can't find much on is some very practical issues that would explain a lot (at least to me). I figured that the programmers' forum would be the place to ask this simple question. It may seem naive, but I do not think it is. I have Word 2000 (Office 2000). When I open a new document, what is it that determines the encoding of that document? And, a follow-up. I can open rtf files with a text editor and see the encoding (or code page, a term I understand some people do not like). But how do I see the same for a .doc file? Believe me, this means a lot to me. Thanks for your serious answer. Hans L Tag: Run macro after InsertFile Tag: 103375
  - 22
    - Medical Form I am creating a form in Word and would like to have two fields First one Provider Type Second one Specialty.... The only problem is that I would like the Second one to filter down to options that are available by the choice selected in the first one. Basically if you choose Physician in the first drop down menu I would like it to only supply the Specialties for the Physicians... meaning it would take out Hospital or Ambulatory and leave Pediatrics or Internal Med. Any suggestions?? Tag: Run macro after InsertFile Tag: 103373
  - 23
    - How do you disable updating of links through VBA Hi, I am working on an app which (among other things) is resonsible for making a PDF of Word docs. That PDF part is fine. The problem is, the Word docs often contains OLE links for which the source is not available. When my print routine opens these docs, the links are converted to "Error! Not a valid link." in the same way that happens when I choose 'Update Links' when opening a Word doc that contains missing links, through Word. My printing routine is very simple. I just call Open(), PutActivePrinter() amd PrintOut(). My question is: is there any way to open a word doc in VBA and force Word to explicitly not update links? Thx in advance. Peter Tag: Run macro after InsertFile Tag: 103372
  - 24
    - Access 2007 ->Word 2007->Access 2007 I posted this in the Access section but have not had much luck. I would like from a form in Access to launch a new Doc in Word. After the doc has been saved I would like to pass the path and filename to the Access field from where it was launched. I am hoping to use a Hyperlink or the new Attachment data type to store this info. Thank you in advance. Tag: Run macro after InsertFile Tag: 103371
  - 25
    - Macro Execution is Too Slow -- Help! I have written a macro (with lots of help from this board) that copies the contents of two Excel worksheets into Word. Once the copy is finished, I need Word to go through a table and delete all rows where the font is formatted white. Below is the code. I've tried everything I can think to turn off screen updating and Word visibility, but it does nothing to speed up the code. Any help you pros can provide would be very much appreciated. Thanks! Sub PrintFlowchart() Dim oRow As Row Dim oRows As Range Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Add wdApp.Visible = False Word.Application.ScreenUpdating = False Excel.Application.ScreenUpdating = False Sheets("Flowchart").Range("A1:V57").Copy wdApp.Selection.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture, _ Placement:=wdInLine, DisplayAsIcon:=False wdApp.Selection.InsertBreak Type:=wdPageBreak Sheets("Key").Activate Range("B2").Select ActiveCell.CurrentRegion.Copy wdApp.Selection.PasteSpecial link:=False, DataType:=wdPasteRTF wdApp.Selection.Goto What:=wdGoToTable, Which:=wdGoToFirst, Count:=1, Name:="" wdApp.Selection.Tables(1).Select If wdApp.Selection.Information(wdWithInTable) = False Then Exit Sub For Each oRow In wdApp.Selection.Tables(1).Rows If oRow.Cells(1).Range.Font.Color = wdColorWhite Then oRow.Delete End If Next oRow wdApp.Visible = True Excel.Application.ScreenUpdating = True Word.Application.ScreenUpdating = True Word.Application.Selection.HomeKey Unit:=wdStory End Sub Tag: Run macro after InsertFile Tag: 103367

Hi,

I want to do the following:

1. open a new document based on a template
2. in this document, insert another word document (which I select
using a listbox)
3. after the word-document is inserted, run the code that is inside
the inserted word document

Step 1 and 2 are not a problem. However, I use

Selection.InsertFile FileName:=myName, Range:="" to insert the new
word document.

In this document I want to use the code that is in this document to
run, the moment the insertion is done. I used AutoOpen, AutoNew,
Document Open in the document to insert, but nothing works. I think
this is because the document is inserted, not opened!

Is there a solution?

Thanks, Mike

Top

Re: Run macro after InsertFile by Jonathan

Jonathan
Fri Aug 03 08:32:55 CDT 2007

"Mike" <Mike_Gator14@yahoo.com> wrote in message
news:1186135715.479335.163660@q75g2000hsh.googlegroups.com...
> Hi,
>
> I want to do the following:
>
> 1. open a new document based on a template
> 2. in this document, insert another word document (which I select
> using a listbox)
> 3. after the word-document is inserted, run the code that is inside
> the inserted word document
>
> Step 1 and 2 are not a problem. However, I use
>
> Selection.InsertFile FileName:=myName, Range:="" to insert the new
> word document.
>
> In this document I want to use the code that is in this document to
> run, the moment the insertion is done. I used AutoOpen, AutoNew,
> Document Open in the document to insert, but nothing works. I think
> this is because the document is inserted, not opened!

You are correct.

>
> Is there a solution?
>
>

In order to make the macro available for use, after inserting the file into
the current document, temporarily load the file as an add-in, using the Add
method of the AddIns collection. You can then use the Application.Run method
to run any macro in that document. Afterwards, unload the add-in and use the
Remove method to remove it from the add-ins list in the Tools, Templates and
Addins dialog.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Top