Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-15-2009, 01:13 AM
justziggy justziggy is offline How to transfer multiple cells from excel to word by formulas Windows XP How to transfer multiple cells from excel to word by formulas Office 2007
Novice
How to transfer multiple cells from excel to word by formulas
 
Join Date: May 2009
Posts: 3
justziggy is on a distinguished road
Default How to transfer multiple cells from excel to word by formulas

Hello


Im wondering how would I transfer 1 part of cell with text, from Excel, to specific spot in Word document, by just showing the destination with formula in Word file. For fast swaping text in word files after i write new texts in excel. Thank you!


Reply With Quote
  #2  
Old 05-18-2009, 11:55 PM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline How to transfer multiple cells from excel to word by formulas How to transfer multiple cells from excel to word by formulas Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

Can you give an example of the rules that need to be followed to allow this to work?

ie:
  1. check all cells in column A for a special character - if found move to step 2.
  2. all text in cell A, AFTER special character goes to bookmark 1 in the document.
  3. text to the left of left of the special character goes to bookmark 2.
  4. text in columns B, C & D go to bookmarks 3, 4 & 5
To make a formula that will take the right details you will need to give specific rules - looking at your image, I'm not sure what the rules are going to be!? Can you clarify? I think that it would make it a bit easier to help you that way!
Reply With Quote
  #3  
Old 05-19-2009, 12:20 AM
justziggy justziggy is offline How to transfer multiple cells from excel to word by formulas Windows XP How to transfer multiple cells from excel to word by formulas Office 2007
Novice
How to transfer multiple cells from excel to word by formulas
 
Join Date: May 2009
Posts: 3
justziggy is on a distinguished road
Default

Quote:
Originally Posted by Bird_FAT View Post
Can you give an example of the rules that need to be followed to allow this to work?

ie:
  1. check all cells in column A for a special character - if found move to step 2.
  2. all text in cell A, AFTER special character goes to bookmark 1 in the document.
  3. text to the left of left of the special character goes to bookmark 2.
  4. text in columns B, C & D go to bookmarks 3, 4 & 5
To make a formula that will take the right details you will need to give specific rules - looking at your image, I'm not sure what the rules are going to be!? Can you clarify? I think that it would make it a bit easier to help you that way!
those numbers are not steps. this is just the text i want to copy from excel file. exp: red number 2 means, where it should be put in word (blue number 2) etc.

i know how to crate excel link. but when i create new row in excel file. i wanna warn the word that it should look 1 row lower where i can put new informations for difretn project.
Reply With Quote
  #4  
Old 05-19-2009, 11:39 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline How to transfer multiple cells from excel to word by formulas How to transfer multiple cells from excel to word by formulas Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

Quote:
Originally Posted by justziggy View Post
those numbers are not steps. this is just the text i want to copy from excel file. exp: red number 2 means, where it should be put in word (blue number 2) etc.

i know how to crate excel link. but when i create new row in excel file. i wanna warn the word that it should look 1 row lower where i can put new informations for difretn project.

I know the numbers on your picture are not steps - but WHAT ARE THE STEPS! I can't help and write code if you don't have rules for the data to be handled - you pictures are no help, because I have no idea what the data shown is, nor what you want it to do - how then am I to help???

What I'm trying to point out is that your original post is not clear enough in the details for me to be able to understand what you want - I'm asking that you clarify it for me so that I can see if I can help.

Please see my previous post, and try to use a similar format to state the rules that you are trying to follow - If you can provide that information, I can maybe try to help you - without that information, I don't see how I can, sorry!


Let me give you an example of what I mean:

From the images you posted I will use the example of (1) - I can see that you want the last part of Cell A to be moved across to the Word document. I can do that because I can see it. But, how am I to tell Word and Excel to recognise that?

Cell A145 = Igerc - [Ravne na Koroskem-Center Ravne] - You want to see ONLY [Center Ravne] moved to the Word document.

Cell A148 = [HOTEL DIANA 100kg] - What, if anything, is going to move from this cell? - How am I supposed to know that? How is EXCEL supposed to know that?

So by what rules do you want to move [Center Ravne], but NOT [HOTEL DIANA 100kg]? OR do you WANT to move [HOTEL DIANA 100kg]? Is there something that shows excel that the cell contains valid text for the move (a special character, for example)? Or maybe you only want cells with [Center Ravne] to be included in the move, and so all the rest can be ignored!

Please remember that none of us here has any idea what this data relates to, nor how it is all supposed to work! You MUST be clear and detailed in your wants and needs if you want us to be able to understand and respond!

I hope this helps clarify things, and that you can now clarify things for us!

Last edited by Bird_FAT; 05-19-2009 at 11:53 AM. Reason: added an example!
Reply With Quote
  #5  
Old 05-22-2009, 01:56 AM
justziggy justziggy is offline How to transfer multiple cells from excel to word by formulas Windows XP How to transfer multiple cells from excel to word by formulas Office 2007
Novice
How to transfer multiple cells from excel to word by formulas
 
Join Date: May 2009
Posts: 3
justziggy is on a distinguished road
Default

ok i will explain one more time. i dont need to move half of the cell to document. i just need to move whole. i made another pic:



and when i create new row in the excel, i just wanna let word know that it should look 1 row lower and transfer all text in the specific spots in word.
Reply With Quote
  #6  
Old 05-23-2009, 02:33 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline How to transfer multiple cells from excel to word by formulas How to transfer multiple cells from excel to word by formulas Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

OK - Here goes,

I'm hoping that I have understood you this time, and that this is what you want - if not, then you will have a little tweaking to do!

The following code should do the following when run:
  • go to the last cell in column A
  • Take the information and hold it in memory
  • repeat the process for the other three (B,C,E) columns
  • Open your Word Template as a document
  • Paste the information in memory to specified points in the document
  • leave the document open for any additional editing needed
to set it up you will need to do the following:

EXCEL:
  • Open the VBA window (Alt+F11)
  • Double-click on 'This Worksheet' (in the left-hand explorer window) to open the code page
  • Paste the code from below
  • Click on 'Tools > References > tick Microsoft Word xx.x Object Library (xx.x is a number) > OK'
WORD:
  • In your document you will need to insert bookmarks into the places where you want the information to go - I will explain where to change the code to fit these.
  • Save the Document as a NORMAL file (NOT as a template) - you will need the full path details for this file.
VBA:
  • Once the code is in place, you will need to edit the information in the code to fit your situation - look at the green lines below for explanation.
Code:
Option Explicit

Sub ExportFinalColumnToWord()
    On Error GoTo errorHandler

    Dim wdApp As Word.Application
    Dim myDoc As Word.Document
    Dim mywdRange As Word.Range
    
    Dim MyColumnA As Excel.Range
    Dim MyColumnB As Excel.Range
    Dim MyColumnC As Excel.Range
    Dim MyColumnE As Excel.Range

    On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
        If Err.Number <> 0 Then
            Set wdApp = CreateObject("Word.Application")
        End If
    On Error GoTo 0

' You need to change the path details here -
' This will create a new document based on your original.
    Set myDoc = wdApp.Documents.Add(Template:="C:\Tempo\ExWd.doc")

' Here you need to change the following:
' MySheet - this is the tab name of the sheet in the spreadsheet
' A1,B1,C1,E1 - these are the columns where the data is found
    Set MyColumnA = Sheets("MySheet").Range("A1").End(xlDown)
    Set MyColumnB = Sheets("MySheet").Range("B1").End(xlDown)
    Set MyColumnC = Sheets("MySheet").Range("C1").End(xlDown)
    Set MyColumnE = Sheets("MySheet").Range("E1").End(xlDown)


' This is where you get the information pasted into your document.
' Below there are four references to bookmarks - inside the ("").
' You will need to change the names to those of YOUR bookmarks.
' If you want data entered into more than one place, you need to
' create a new line, as below, and change the bookmark name,
' choosing the correct reference at the end 'MyColumnX'
' For example to have the information from Column A in another place,
' you would add the line in green that is below this section.
        With myDoc.Bookmarks
            .Item("bmMyColumnA").Range.InsertAfter MyColumnA
            .Item("bmMyColumnB").Range.InsertAfter MyColumnB
            .Item("bmMyColumnC").Range.InsertAfter MyColumnC
            .Item("bmMyColumnE").Range.InsertAfter MyColumnE
'           .Item("bmMyColumnA_2").Range.InsertAfter MyColumnA
        End With

' At this point the Word Application will appear in your taskbar
    wdApp.Visible = True
    Exit Sub

errorHandler:
    wdApp.Quit
    Set wdApp = Nothing
    Set myDoc = Nothing
    Set mywdRange = Nothing

End Sub
If you then place a new macro button on your taskbar, you could link it to the macro and run it whenever you want it.

Hope that does the job!

Last edited by Bird_FAT; 05-23-2009 at 07:01 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shading in Cells in Word Tables Cindy Word Tables 1 01-04-2013 02:38 PM
How to transfer multiple cells from excel to word by formulas Opening multiple Excel files within the same Excel window. lost9471 Excel 2 05-01-2010 01:57 PM
How to transfer multiple cells from excel to word by formulas Word 2007 Password not staying after file transfer. grumalt Word 1 04-17-2009 12:30 PM
Populate cells in Word from a database hotlilshan Word 3 12-09-2008 01:51 PM
Automatically entering/fill data in cells in Excel 2003 dipdog Excel 0 08-17-2006 08:37 AM


All times are GMT -7. The time now is 08:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft