Free Microsoft Excel 2013 Quick Reference

Macro to search word document Results

Hi,

I have the following Macro;

Sub Replacing()

    Dim sFile   As String
    Dim wrdApp  As Word.Application
    Dim wrdDoc  As Word.Document

    sFile = "Pack"
    Set wrdApp = New Word.Application

    With wrdApp
        .Visible = True
        Set wrdDoc = .Documents.Open("C:UsersAdminDesktop" + sFile + ".doc")

        With .Selection.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = "NAME1" + "NAME2"
            .Replacement.Text = Range("C2") + Range("C3")
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
            .Execute Replace:=wdReplaceAll
        End With
    End With
End Sub
.Text = "NAME1"
.Replacement.Text = Range("C2")

This works for all NAME1 entries in the Word document but I want to have multiple searches for example;

.Text = "NAME1" & "NAME2"
.Replacement.Text = Range("C2") & Range("C3")

So that it will replace NAME1 and 2 with what is in C2 and C3. I want to do about a hundred of these.

Also I would like some assistance on how to modify this macro so the user cannot interact with it and once the changes are complete it Prints and Closes the word document.

Many thanks.

Having trouble with macro. I want macro to search all sheets in
workbook, find a word, copy the cell and adjacent cells with that word,
then paste the data into another workbook.

Here are the steps I follow:
*Open two documents, one called Hierarchy.xls and one called
Harvest.xls
*In Harvest.xls, I have the appropriate cell focused
*Switch to working in Hierarchy.xls
*Cmd+f(find)>Find what:searchword>Within:Workbook>Search:By Rows>Look
in:Formulas (match case & find entire.. are not checked)>Find
Next>Close
*Hierarchy.xls has 33 sheets. I make sure that the cell focus is on A1
of each sheet.
*Select the first worksheet
*Tools>Macro>Record New Macro
*Macroname:Macro2>Shortcut Option+Cmd+q>Store in: This Workbook>OK
*Relative reference is depressed
*Cmd+f(find)
*Click Find Next
*Click Close
*Left arrow once>Hold Shift, right arrow twice (effectively selecting
the adjacent cells to the found cell)
*Cmd+c(copy)
*>Window>Harvest.xls(switches to other workbook)
*Cmd+v(paste)
*down arrow once (gets to next row, ready for future applications of
macro)
*>Window>Hierarchy.xls(switches to other workbook)
*down arrow once (gets to next row, ready for future applications of
macro)
*Esc (gets rid of marquee on selection)
*Click Stop Macro (macro is complete)
*Go back to first sheet (search switched to second sheet)
*Cmd+option+q (activates macro)

First couple times I use the macro, it works fine, but after the third
time, I get error: "Run-time error '1004' Method 'Offset' of object
'Range' failed.

*Click Debug

"ActiveCell.Offset(0,-1).Range("A1:C1").Select" is highlighted.

Here is the complete text of the vba macro:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/3/2006 by L
'
' Keyboard Shortcut: Option+Cmd+q
'
Sheets("Sheet2").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, -1).Range("A1:C1").Select
Selection.Copy
Windows("Harvest.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("HIERARCHY.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub

Notes: The first line troubles me, because I think the macro is
automatically switching to the second sheet, when it should just be
searching and not switching sheets.

Hi,

I have an excel sheet having some text in each row. i have to find these texts in a word document's header and footer if its there in header and footer it should be highlighted in excel sheet .My problem is how to find the text in header and footer of the word document through a vb macro??

Can you please suggest a vba code for the above query.

Thanks:

Sorry I am not sure if I should have posted this here or in the VB forum (I am sure someone will tell me). I am trying my initial attempts at doing a macro (only recording at present) and have hit a snag. On the spreadsheet attached I have re-done the macro back to where the snag is.
I need to keep the top row and only the rows which contain the word Trans in column O (In this case roes 53 to 59). If I was doing this manually I would sort by column O and delete all the other rows. The problem is that the number of rows in the original text documents containing "Trans" varies from 2 rows to approx 60 each time I run it so If I do the same in recorder as I do manually then obviously it deletes only a fixed number of rows. The way I see it (Tell me if I am wrong) is I need somehow to get the macro to search for the rows including the "Trans": and then select and delete all the other rows bar row 1. Basically I haven't a clue how to do this. If anyone could help it would be much appreciated. I have attached a text file with the current macro code, the spreadsheet as it stands,the original text file (ALCOHOL MASTER-copy.txt) and a final spreadsheet showing how it should look just in case anyone needs them. If it is possible I would like to be able to do it in recorder due to my lack of knowledge. Thanks in advance

Hi,

Is dere any vb macro that can copy the text from the header and footer of a word document.

Thanks:

Hey, I will try to explain what I want to do:

When I open a document, there will be prompt to enter the EmployeeID. The id entered will be used to search the data source, in this case a database table, and data like name and phone will be retrieved and displayed on the word document like shown:

EmployeeID: (from the prompt)
Name: (from database table)
Phone: (from database table)

I am able to do the first part. I did it by using a fill-in box. I recorded a macro which selects the fill-in from Quick Parts from Insert. But I have no idea on know to do the rest of the part.

Some help will be greatly appreciated.

Hello all!

I am using Word 2007 and the font Adobe Garamond Pro. Within that font are special text figures to use in place of regular numbers (lining figures). I have created a macro that searches the document and replaces lining figures with the text figures.

This it does with no problem. When I used Word '03, I could switch to the footer, run the macro, and it would replace my page number lining figures with the special text figures. It was swell. However, now in Word 2007, I seem unable to get any type of find-replace command (even when I do them manually in the pop-up box) to alter my page numbers, though they still work for text formatted as body text.

I have also created various find-replace macros for replacing f* combinations with f-ligatures. These still work in the headers and footers of my document. When I replace the {Page} with a simple 1 and run the macro... BAM it replaces it with the text figure no problem, so it would seem as though it is only the {Page} code in the footer that (in this version of Word, at least) is completely uncooperative .

A further, and potentially related problem: I am also unable to use find-replace to replace my lining figures in numbered lists of the body text with text figures.

So, my question: Is there a way to get Word '07 to allow me to replace my areas formatted as number areas? I know Word '03 could, but it seems '07 'locks' numbers from being replaced.

I appreciate any assistance you fine folks can give me in solving this!

Thank you,
Jon

Hi,

I have a process and needs to find a short cut.

I have around 20 users.These folks uses a custom crystal reports to export field from a database into excel files. All the excel files are saved in their own local machine.

Ex.
C:employee1.xls
C:employee2.xls
C:employee3.xls

From here they open the excel sheets and do what they need to. Data modifications, corrections and etc.

Exactly at 2pm in the afternoon a custom application is ran and searches through all of the defined user machines, it merges all the data into an online database. The other thing that the application does is it opens a custom word document and saves it into each user's desktop that tells tell how many company they had processed for the day.

I need a macro that is embedded into the word document so that at 3pm when they open the word document, the macro will clean out all the excel sheets from their c drive. If the document is not created on the user's desktop, no files should be deleted. Yes, I tried running a batch file but it wasn't working out.

Any help? Thanks.

Hi,

I've got an Excel document with over 7K entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 50 keywords so doing this using Ctrl+F would take some time. I'm new to Macros but I think that a macro may be the best solution for this task.
The data is in Sheet1 and the particular column that needs to be searched is "K". The contents of column "K" is made up of words and sentences. Sheet2 is where I would like the found results to be copied to. The list of Keywords that need to be searched for are located in Sheet3, starting from Cell A1. Below is a list of the sort of words that need to be searched for:

Jaguar
Landrover
Tata Motors
Volvo
Audi
Mazda
Vauxhall
Opel

Does anyone think they can help me create a macro to carry out this task or know of a better solution for doing it?

Any help would be much appreciated.

Thanks,

Hello,
I have a Word document that I need to search for a specific word, copy the entire sentence, and then paste into the next available row in excel. Is there a way to do this using Macros? I don't have much experience setting up the Macros, beyond, "record".

Any help would be appreciated.

Thanks.

Hey guy's im new here and my second question here starts off quite difficult.

First off:
My programs : Office 2010
My skills: limited ( i've been searching the web for quite some time now and leand a view things from what i've read)

My question:

For my work i want to create ( i was asked to do ) an database of machine testing specs.
They have 200/300 word documents with an table in it with the specs.
I want so import this info from the word document into an excel spreadsheet. ( easy data comparison and so on )

I've already asked the question how to automate the proces of exporting the data from word to an excel file ( in the word and excel help forum )
I think those guys can help me, but you guy's to ( its more excel then word vba ) so i'm asking the same here.

I've got an working macro for data extraction form the word talbe in to excel via VBA.

	VB:
	
 ImportWordTables() 
     'Imports cells (3,2) and (4,2) from Word document Tables 1-10
    Dim wdDoc         As Word.Document 
    Dim wdFileName    As Variant 
    Dim TableNo       As Integer 'number of tables in Word doc
    Dim iTable        As Integer 'table number index
    Dim iRow          As Long 'row index in Excel
    Dim iCol          As Integer 'column index in Excel
     
    wdFileName = Application.GetOpenFilename("Word files (*.doc*),*.doc*", , _ 
    "Browse for file containing table to be imported") 
     
    If wdFileName = False Then Exit Sub '(user cancelled import file browser)
     
    Set wdDoc = GetObject(wdFileName) 'open Word file
     
    With wdDoc 
        TableNo = wdDoc.tables.Count 
        If TableNo = 0 Then 
            MsgBox "This document contains no tables", _ 
            vbExclamation, "Import Word Table" 
        ElseIf TableNo > 10 Then 
            TableNo = 10 
             'Else TableNo is actual number of tables between 1 and 9
        End If 
         
        Range("A1") = "Table #" 
        Range("B1") = "Cell (3,2)" 
        Range("C1") = "Cell (4,2)" 
         
        For iTable = 1 To TableNo 
            With .tables(iTable) 
                 'copy cell contents from Word table cells to Excel cells in column B and C
                Cells(iTable + 1, "A") = iTable 
                Cells(iTable + 1, "B") = WorksheetFunction.Clean(.cell(3, 2).Range.Text) 
                Cells(iTable + 1, "C") = WorksheetFunction.Clean(.cell(4, 2).Range.Text) 
            End With 
        Next iTable 
    End With 
     
    Set wdDoc = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This macro works for me, but it will only get the data from 1 file ( via selection ) ,
I want it to do so automaticly ( and for later document to ) and i thought that i've it save the data to an single excel file is the best thing.

So: The extracted data from 1 word file is saved in 1 excel file.

And then later on i import the data from all of my excel files in to 1 master (database) excel file.
Preferbly via VBA Macro

Since i've not used macros or vba before it thought that was the best for me. And easyer to understand.
And dont get it all in one file with a huge macro wich is difficult for me to understand multiple steps.

I've already made an seperate excel file with the cell names that contains the data i need.
And made an range (with titles i want to have)
So i can copy it into the macro ( easy )

The code above works, and when i copy my ranges and cell info it works. But give's me an error on empty ( there i dont have the basic knowlegde to overwrite that )
I know it has something to do with the cleaning cell option. But dont have the knowlege ( also didnt have the time to find out ) how to overcome that.
For you it sure will be an easy task.

I hope i've been clear enough of what i want, i've not then please ask.

Thanks in advance!!!

Edit: The macro contains a fault,, i've changed the first line :Dim wdDoc As Word.Document
and changed Word.Document to Object to make it working

I need to make a macro for excel that opens a website and looks for a word or phrase. From there, it will perform an action if the word or phrase is present. I am new to programming but below is what I have. The below example will open www.yahoo.com but I would like it to search for a word or phrase.


	VB:
	
 finding() 
     
    Dim IE As Object 
    Dim PageText As String 
    Dim SearchText As String 
     
    Set IE = CreateObject("internetexplorer.application") 
    IE.Visible = True 
     
    IE.Navigate "[URL]http://www.yahoo.com/[/URL]" 
     
    While IE.Busy 
        DoEvents 
    Wend 
     
    SearchText = "example text" 
    PageText = IE.Document.Body.InnerText 
     
    If InStr(PageText, SearchText) > 0 Then 
        MsgBox "found" 
    Else 
        MsgBox "not found" 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Oddly enough, sometimes when I run this I get: Run-Time error '91': Object variable or With Block Variable Not Set
While other times, Excels mesbox indicates "not found" when the phrase I'm searching for is on the website.

Any ideas on how I should proceed?
thanks for your help.

Hi friends,

I have a database and I want the macro to search the data entered into the user input by looping through multiple sheets in the workbook. However, the important part is the how the result is displayed. I want to do it in a way we search items in, say, word of pdf documents. Once the search item is found, it should stop there, so that we can read the details of the item. Once we press enter, the second found item should be highlighted and so on.

However, another way could be to spot all the matching items and copy the entire rows to a temporary worksheet, so that user can read the details. It is not necessary to save the results though.

Can anybody help me here?

I searched through the forums and could not find anything that met with my current need. I am sure this must have a simple solution, but I am at a loss. I work with zips of Excel files all day. These zips all contain the same specific files, but have different ID #s for the file names. But the hearder names for each worksheet remains constant. Anyway, I wanted to be able to record a macro for sorting the worksheets. This may sound simple, but I spend so much time sorting each worksheet it gets out of hand. Anyway, having the macro attached to the actual file I am currently working in won't work, as I can't access it for the next file, not to mention that each zip has 10 seperate xls files, so I certainly don't want any extra files open. Anyway, is there a way to record a macro that would remain in a "default" document in Excel the way that you can in the "normal.dot" for Microsoft Word?

Hey all

Trying in vain to get a macro to work for my job!
It would make work a lot easier for me.

I have a report I must run at the end of each month and is downloaded as a huge
Spreadsheet. From this spreadsheet I want to extract selective data.

I need to search the A column of the spreadsheet and find words with the letters “prj”

I then want to look at the cells next to the words and import them figures also in to my
Report.

At the moment I am importing the entire A column through straight forward formula.

I am then using the following macro to extract the words into the next column and listing them together and in turn using vlookup to determine the figures in the C column afterwards


	VB:
	
 Auto_Open() 
    a = 3 
    With Worksheets(1).Range("a1:a5000") 
        Set c = .Find("prj", LookIn:=xlValues) 
        If Not c Is Nothing Then 
            firstAddress = c.Address 
            Do 
                c.Copy 
                Cells(a, 2).PasteSpecial 
                a = a + 1 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address  firstAddress 
        End If 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My problem is this!

The macro imports the information incorrectly unless the links between the column A in the new document and column A in the external document are broken.

Has anyone any idea of how to fix this problem

I'm trying to write a macro that will automatically create a Word document listing the data I need from an XLS sheet. I have figured out how to open the Word file from Excel and how to adjust the sheet to the Landscape format, now I need to know how to get the cells from Excel put in the spots I would like on the word document. I searched on here and couldn't find anything on how to place the cursor at set positions.

Pretty sure my attachment should clarify what I'm trying to do as I tried to make it as straight-forward as possible. If someone can just provide me with the method to insert a cell value to a certain position in the document, that will put me on the right path.

Oh, before I forget, the (Cell J14) is most likely going to be longer than the space provided and needs to be placed into the rows below rather than going beyond the 'Key Control', 'Frequency', etc.

Any Help Would Be Greatly Appreciated!
Thanks!

Hi,

I am facing a problem related to excel and word macro. I have a sheet in excel with data and charts. I want to copy the table data and chart from excel to word. for that i have created a macro and i successfully copy the data from excel to word tables. but i am facing problem in copying chart.

there are abt 12 charts in excel and i want to copy 2 charts. the same 2 charts are in word in form of image.
I have created bookmark on chart in word and then tried this code

	VB:
	
) 
    Dim curDoc As Document 
    Set curDoc = Application.ActiveDocument 
    Dim newDoc As Document 
    Dim i As Integer 
    Dim bName As String 
    Dim myRange As Range 
    objWorksheet1.Activate 
    For i = 1 To ActiveDocument.Bookmarks.Count 
        If ActiveDocument.Bookmarks(i).Name = "NPV_1" Then 
            objWorksheet1.ChartObjects("Chart 15").Activate 
            objWorksheet1.ChartObjects("Chart 15").Select 
            objWorksheet1.ChartObjects("Chart 15").Copy 
             
            curDoc.Activate 
            Selection.GoTo What:=wdGoToBookmark, Name:="NPV_1" 
            Selection.Delete Unit:=wdCharacter, Count:=1 
            Selection.PasteSpecial DataType:=wdPasteMetafilePicture 
            Selection.TypeParagraph 
             'Selection.PasteAndFormat Type:=wdChartPicture
            ActiveDocument.Bookmarks.Add Range:=Selection.Range, Name:="NPV_1" 
        End If 
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where objWorksheet1 is the object of excel worksheet and passed when the function is called..
NPV_1 is the bookmark created on chart in word which needs to be replaced.
Chart 15 is the chart title in excel
curDoc is the object of word document.

After running this macro from word the macro copies the chart from excel activate the word document search for the bookmark deletes the bookmark paste the object correctly. but the problem is below the chart is a table as soon as the chart is deleted the table shifts upwards and the chart is copied over the table. and the table doesn't shift down.

Sorry for such a long story but i want to clear the problem which i am facing.

Please if anyone can help.

Thanks
Nitin

Hi,

I have created an excel macro that opens up word documents and copies the data across to excel then in excel it looks for key words Start: and End: and copies all data in between them then pastes onto a new sheet. This works great but I am wondering, to preserve formatting is it possible to this all in word? (i.e. search for two words and copy all data in between into new word document?)

I don't know much about word macros and can't really find any resources online and was hoping someone here had some knowledge on the subject

As far as I can see, there no real equivalent in word (i.e. search down line by line to find words then copy and paste data in between)

Can anyone point me in the right direction?

Regards,

Jason

This macro searches for the phrase "S E G - D" and then copies the next 20 lines. It is supposed to continue doing this until the end of the document, stringing all of the occurances together, and then paste them all into a new document. This macro only does the first occurance.

Code:
Sub CopyParas()
Application.ScreenUpdating = False
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Text = "S E G - D"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Do While Selection.Find.Execute
Selection.StartOf Unit:=wdLine
Selection.MoveEnd Unit:=wdLine
Selection.MoveDown Unit:=wdLine, Count:=20, Extend:=wdExtend
sBigString = sBigString + Selection.Text
Selection.MoveStart Unit:=wdLine
Loop
Documents.Add DocumentType:=wdNewBlankDocument
Selection.InsertAfter (sBigString)
Application.ScreenUpdating = True
End Sub
other posts
http://www.pcreview.co.uk/forums/thread-3465201.php
http://www.ozgrid.com/forum/showthread.php?t=89720

Hi i was wondering if someone could help me with my macro. I know this is more a Word VBA question but there involves an element of VBA in this. I currently have a macro whereby
a userform is called. I enter a number in the field on the user form and
click insert. This will then update the textfield in every page of my
document. Please see below what the macro is currently.

Code:
Sub UpdateBHR()

Dim Title As String
Dim frmTitle As UserForm1
Dim oStory As Range

Set frmTitle = New UserForm1

With frmTitle
    .Show
    ActiveDocument.BuiltInDocumentProperties("Title").Value = .Title.Text
End With

Unload frmTitle

Set frmTitle = Nothing

For Each oStory In ActiveDocument.StoryRanges
    oStory.Fields.Update
    If oStory.StoryType < wdMainTextStory Then
      While Not (oStory.NextStoryRange Is Nothing)
        Set oStory = oStory.NextStoryRange
        oStory.Fields.Update
      Wend
    End If
Next oStory
Set oStory = Nothing
End Sub
I want to somehow extend this macro to do the following

I want to look into an excel file on the path G:FormsIndex.xls
With the document that i have opened i want to set the documents name as a variable.
In my Index.xls, i have two columns, A and B. Column A will contain name of documents.
I want to therefore search Column A with the variable of the document i have opened.
Once found i want to offset this to column B, take the value as another variable and open a form page in the following way G:FormsColumnBVariable.doc
When this is opened i want to insert the Title value from my current macro into the text field and then print off.

Would anybody know of how to possibly go about this.

Thanks