excel to word?
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: excel to word?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am just learning about excel and macros. Is it possible to write a macro from an excel spreadsheet to copy the spreadsheet into a word document? This would need to be from a specific file (in a certain directory) to a specific word doc (in a specific directory).

    The trick is that the excel file would be used over and over with different information each time, and this information would need to go to the corresponding new word doc each time.???

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you really need a macro?

    Can you just insert the Excel spreadsheet into Word using:

    Insert|Object "Create from file" then browsing to your spreadsheet and making sure that "link to file" is checked? This will update your Word document whenever the spreadsheet is updated.

    HTH

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    This code copies the usedrange on Sheet1 from a specified file and pastes it into a specified Word document.

    IMPORTANT: You need to go to Tools, References in the VB editor and make sure the Microsoft Word n.x Object Library is checked. n.x will depend on the version you're using - for Word 2000 it 9.0.

    HTH,
    Dan

    Code:
    Sub PasteToWord()
    Dim wdApp As Word.Application, wdDoc As Word.Document
    
    Dim strSourceFile As String, strDestFile As String
    
    
    strSourceFile = "C:tempbook1.xls" 'Change to suit your needs
    
    strDestFile = "C:tempyourdoc.doc" 'Ditto
    
    'Open the Excel file
    Workbooks.Open strSourceFile
    ActiveWorkbook.Sheets("sheet1").UsedRange.Copy
    
    'Open the Word document
    Set wdApp = CreateObject("Word.Application")
    wdApp.Documents.Open strDestFile
    wdApp.Selection.Paste
    
    End Sub

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark - this implies that each time the word doc is updated, it overwrites the current info. I need a separate file each time from excel to word. ?

    Dan - where do I put this code? in the excel file as a module? I tried it that way, when i click run is does something, but does not create the file I need. Also, the source file and dest file will change with each set of data. Is this possible with the code as written? Please excuse my stupid questions, but this is my second week learning (all self taught) this stuff...

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you create a Word Template that has links to the Excel sheet, it will open as a NEW document each time and update with the current contents of the Excel sheet.

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-01 07:51, nancyo wrote:
    Mark - this implies that each time the word doc is updated, it overwrites the current info. I need a separate file each time from excel to word. ?
    Yup, you're correct. I missed the phrase "new Word" in your original post. I'd follow the VBA code or lenze's suggestions here.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nancy,

    What area will of the Excel worksheet will you be copying? Will it be the same each time? Do you want the user to be able to select both source and destination files e.g. with a standard Open dialog box? Or do you want a new Word document created each time you run this macro?

    Regards,
    Dan

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dan - the excel folder has several spreadsheets. Only one needs to be copied into word, and will be the same each time.

    The excel file will be saved as a different filename with each new set of data. From there, I want to be able to just copy one of the spreadsheets into a word document, which will also have a new filename (either a template which keeps bening renamed, or?).

    I do not know what an Open dialog box is (I am SO new to this). If the tech could select a source and destination, what about filenames? Would they need to be created in word ahead of time?

    Ideally, the data is generated and saved into a specific spreadsheet in excel (to be resaved as another filename with each new set of data).

    Once this is done, rather than copying and pasting by hand into a new word document, I would like to be able to have either a macro or some other way to do this automatically. There are way too many techs errors, and I want to eliminate this problem.

    I apologize for my ignorance!!! I am leaving for the day...any resonses will be tomorrow. THANKS TO ALL!!!

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Nancy

    Yuuuck Did i hear that WORD thingy...???

    PK i hat word for no reason bar its not excel and i WONT allow it on any PC i go near...

    But thats not the point.. check for an EXCEL file called Samples (the world will hit find files Samples.xls best i can remember thats what its called, suggest S*.xls search just in case)

    I have sheets one has all the code to trans from A to B in Office Excel to Word is there for sure if Excel was loaded correctly.. this will get you started...

    Now you have the text over.. the nasty stuff comes into plat WordBasic not VBA.. and ive no idea at that.. Excel can com and Word but?? Not got a cluse..

    MY opinion is to didtroy word and do it all in Excel Shes muh nicer and better lloking, and can do all Word can withthe addition of Math.. and i fancy her!

    If you need more hlep im sure this case be solved you producing letters or a report??? i guess..

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Dan - I have tried many variations on a theme with your code. Any suggestions?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com