Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Excel data into Word forms
I have an Excel form and a Word form that need to go together. The Excel form
has to be in excel format because of complicated calculations. After the Excel form was filled out, currently the users have to take some of the results from the Excel form and duplicate them into a letter that is in Word format. Both the Excel form and letter were later sent out together. Is there a way for have this programmed to eliminated having to cut and paste data from Excel to Word? Maybe a macro in Excel that allows the generation of the letter.doc after done with the Excel form? Or the other way around? I don't know. I want to make this process short and simple as possibles because the users of the forms are not computer savy. Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Excel data into Word forms
YOu can control word documents through excel. Here is some starter code
Sub Test() ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next 'Get word object if application already is opened. Set WDApp = GetObject(, "Word.Application") If Err.Number < 0 Then Set WDApp = CreateObject("Word.Application") WordWasRunning = False End If WDApp.Visible = True 'at least for testing! Set WDDoc = WDApp.documents.Open(Filename:=FName) Set WordTable = WDDoc.tables(1) WDDoc.Close End Sub "Kaylen" wrote: I have an Excel form and a Word form that need to go together. The Excel form has to be in excel format because of complicated calculations. After the Excel form was filled out, currently the users have to take some of the results from the Excel form and duplicate them into a letter that is in Word format. Both the Excel form and letter were later sent out together. Is there a way for have this programmed to eliminated having to cut and paste data from Excel to Word? Maybe a macro in Excel that allows the generation of the letter.doc after done with the Excel form? Or the other way around? I don't know. I want to make this process short and simple as possibles because the users of the forms are not computer savy. Any help is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Excel data into Word forms
Hi,
Perhaps it would be better for Kaylen to explore mail-merge first and, if it does not meet its requirements, turn to OLE automation. Interaction between Word and Excel is not hard to accomplish, but it requires you to know well Word and Excel objects. Kaylen, if you wish, I can post some sample code to transfer data from an Excel table (a range) to a Word table. Having said that, I think one must exhaust simple solutions (mail-merge) before moving to more complex ones (OLE automation). -- Carlos "Joel" wrote: YOu can control word documents through excel. Here is some starter code Sub Test() ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next 'Get word object if application already is opened. Set WDApp = GetObject(, "Word.Application") If Err.Number < 0 Then Set WDApp = CreateObject("Word.Application") WordWasRunning = False End If WDApp.Visible = True 'at least for testing! Set WDDoc = WDApp.documents.Open(Filename:=FName) Set WordTable = WDDoc.tables(1) WDDoc.Close End Sub "Kaylen" wrote: I have an Excel form and a Word form that need to go together. The Excel form has to be in excel format because of complicated calculations. After the Excel form was filled out, currently the users have to take some of the results from the Excel form and duplicate them into a letter that is in Word format. Both the Excel form and letter were later sent out together. Is there a way for have this programmed to eliminated having to cut and paste data from Excel to Word? Maybe a macro in Excel that allows the generation of the letter.doc after done with the Excel form? Or the other way around? I don't know. I want to make this process short and simple as possibles because the users of the forms are not computer savy. Any help is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Excel data into Word forms
Thank you Carlos for your insights. I am not a pro with programming, actually
I'm a beginner trying to learn. I have thought of mail merge but that is a little too complicate for the users to do for they are very limited with computer knowledge. My idea is to have a command button somewhere in Excel where the users can just click on after entering all the data in Excel and all of the relevant infomation( info in certain cells only) is duplicated or merged into the letter that is in Word format. After the merge, the completed letter is then open for the users to see and double check or do any final edits. If possible, can you post me a sample OLE automation code that I can try to use? And where would I put this code in Excel? I'm pretty new with this therefore the more instructions the better. THank you so much for your help! "Carlos" wrote: Hi, Perhaps it would be better for Kaylen to explore mail-merge first and, if it does not meet its requirements, turn to OLE automation. Interaction between Word and Excel is not hard to accomplish, but it requires you to know well Word and Excel objects. Kaylen, if you wish, I can post some sample code to transfer data from an Excel table (a range) to a Word table. Having said that, I think one must exhaust simple solutions (mail-merge) before moving to more complex ones (OLE automation). -- Carlos "Joel" wrote: YOu can control word documents through excel. Here is some starter code Sub Test() ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next 'Get word object if application already is opened. Set WDApp = GetObject(, "Word.Application") If Err.Number < 0 Then Set WDApp = CreateObject("Word.Application") WordWasRunning = False End If WDApp.Visible = True 'at least for testing! Set WDDoc = WDApp.documents.Open(Filename:=FName) Set WordTable = WDDoc.tables(1) WDDoc.Close End Sub "Kaylen" wrote: I have an Excel form and a Word form that need to go together. The Excel form has to be in excel format because of complicated calculations. After the Excel form was filled out, currently the users have to take some of the results from the Excel form and duplicate them into a letter that is in Word format. Both the Excel form and letter were later sent out together. Is there a way for have this programmed to eliminated having to cut and paste data from Excel to Word? Maybe a macro in Excel that allows the generation of the letter.doc after done with the Excel form? Or the other way around? I don't know. I want to make this process short and simple as possibles because the users of the forms are not computer savy. Any help is appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Excel data into Word forms
Kaylen:
Here is the code. For the code to run, you must have a folder named "C:\OLE Automation\" containing an Excel workbook named "Excel.xls" and a Word document named "Word.doc". Excel.xls contains data in the range "A1:E11" and Word.doc must be empty. "data" is the name of the worksheet were the data is. The code will look messy in the chat, so copy and paste it into a Visual Basic module IN Excel.xls. Option Explicit Public Sub TransferData() 'This macro transfers the data range "A1:E11" to a table in Word ' 'Constants: 'docFullName = The full name of an already existing Word document ' 'Variables: 'doc = The Word document (assumed to be empty) 'i = A counter (for rows) 'j = A counter (for columns) 'tbl = A Word table 'wdRng = A Word range (the first paragraph of doc) 'wks = The worksheet "data" that contains the data range ' 'Const docFullName = "C:\OLE Automation\Word.doc" 'Only if you want a specific document Dim doc As Object Dim i As Long Dim j As Long Dim tbl As Object Dim wdApp As Object 'Only if you require a new document each time Dim wdRng As Object Dim wks As Worksheet 'Assing Word objects 'Only if you require a new document each time Set wdApp = CreateObject("Word.Application") wdApp.Visible = True Set doc = wdApp.Documents.Add 'Assign variables and objects 'Set doc = GetObject(docFullName) 'Only if you want a specific document Set wdRng = doc.Paragraphs(1).Range Set tbl = doc.Tables.Add(wdRng, 11, 5) Set wks = ThisWorkbook.Worksheets("data") 'Transfer the data With tbl For i = 1 To 11 For j = 1 To 5 .Cell(i, j) = wks.Cells(i, j) Next j Next i End With 'Save and close doc 'Only if you want a specific document 'Call doc.Save 'Call doc.Close(False) 'Clean Set doc = Nothing Set wks = Nothing End Sub -- Carlos "Kaylen" wrote: Thank you Carlos for your insights. I am not a pro with programming, actually I'm a beginner trying to learn. I have thought of mail merge but that is a little too complicate for the users to do for they are very limited with computer knowledge. My idea is to have a command button somewhere in Excel where the users can just click on after entering all the data in Excel and all of the relevant infomation( info in certain cells only) is duplicated or merged into the letter that is in Word format. After the merge, the completed letter is then open for the users to see and double check or do any final edits. If possible, can you post me a sample OLE automation code that I can try to use? And where would I put this code in Excel? I'm pretty new with this therefore the more instructions the better. THank you so much for your help! "Carlos" wrote: Hi, Perhaps it would be better for Kaylen to explore mail-merge first and, if it does not meet its requirements, turn to OLE automation. Interaction between Word and Excel is not hard to accomplish, but it requires you to know well Word and Excel objects. Kaylen, if you wish, I can post some sample code to transfer data from an Excel table (a range) to a Word table. Having said that, I think one must exhaust simple solutions (mail-merge) before moving to more complex ones (OLE automation). -- Carlos "Joel" wrote: YOu can control word documents through excel. Here is some starter code Sub Test() ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next 'Get word object if application already is opened. Set WDApp = GetObject(, "Word.Application") If Err.Number < 0 Then Set WDApp = CreateObject("Word.Application") WordWasRunning = False End If WDApp.Visible = True 'at least for testing! Set WDDoc = WDApp.documents.Open(Filename:=FName) Set WordTable = WDDoc.tables(1) WDDoc.Close End Sub "Kaylen" wrote: I have an Excel form and a Word form that need to go together. The Excel form has to be in excel format because of complicated calculations. After the Excel form was filled out, currently the users have to take some of the results from the Excel form and duplicate them into a letter that is in Word format. Both the Excel form and letter were later sent out together. Is there a way for have this programmed to eliminated having to cut and paste data from Excel to Word? Maybe a macro in Excel that allows the generation of the letter.doc after done with the Excel form? Or the other way around? I don't know. I want to make this process short and simple as possibles because the users of the forms are not computer savy. Any help is appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Excel data into Word forms
Take a look at this:
http://word.mvps.org/FAQs/InterDev/C...WordFromXL.htm This too: http://addbalance.com/usersguide/fields.htm And this: http://gregmaxey.mvps.org/Word_Fields.htm Finally, once you get the DocVariable fields set up in Word (hit Alt + F9 to see all fields), run this code from Excel. Sub PushToWord() Dim objWord As New Word.Application Dim doc As Word.Document Dim bkmk As Word.Bookmark sWdFileName = Application.GetOpenFilename(, , , , False) Set doc = objWord.Documents.Open(sWdFileName) With doc ..Variables("VarNumber1").Value = Range("VarNumber1").Value ..Variables("VarNumber2").Value = Range("VarNumber2").Value 'etc ..Range.Fields.Update End With 'ActiveDocument.Fields.Update objWord.Visible = True End Sub Note: This code runs in Excel; pushes Excel variables (assigned as Named Ranges) to Word. Regards, Ryan-- -- RyGuy "Carlos" wrote: Kaylen: Here is the code. For the code to run, you must have a folder named "C:\OLE Automation\" containing an Excel workbook named "Excel.xls" and a Word document named "Word.doc". Excel.xls contains data in the range "A1:E11" and Word.doc must be empty. "data" is the name of the worksheet were the data is. The code will look messy in the chat, so copy and paste it into a Visual Basic module IN Excel.xls. Option Explicit Public Sub TransferData() 'This macro transfers the data range "A1:E11" to a table in Word ' 'Constants: 'docFullName = The full name of an already existing Word document ' 'Variables: 'doc = The Word document (assumed to be empty) 'i = A counter (for rows) 'j = A counter (for columns) 'tbl = A Word table 'wdRng = A Word range (the first paragraph of doc) 'wks = The worksheet "data" that contains the data range ' 'Const docFullName = "C:\OLE Automation\Word.doc" 'Only if you want a specific document Dim doc As Object Dim i As Long Dim j As Long Dim tbl As Object Dim wdApp As Object 'Only if you require a new document each time Dim wdRng As Object Dim wks As Worksheet 'Assing Word objects 'Only if you require a new document each time Set wdApp = CreateObject("Word.Application") wdApp.Visible = True Set doc = wdApp.Documents.Add 'Assign variables and objects 'Set doc = GetObject(docFullName) 'Only if you want a specific document Set wdRng = doc.Paragraphs(1).Range Set tbl = doc.Tables.Add(wdRng, 11, 5) Set wks = ThisWorkbook.Worksheets("data") 'Transfer the data With tbl For i = 1 To 11 For j = 1 To 5 .Cell(i, j) = wks.Cells(i, j) Next j Next i End With 'Save and close doc 'Only if you want a specific document 'Call doc.Save 'Call doc.Close(False) 'Clean Set doc = Nothing Set wks = Nothing End Sub -- Carlos "Kaylen" wrote: Thank you Carlos for your insights. I am not a pro with programming, actually I'm a beginner trying to learn. I have thought of mail merge but that is a little too complicate for the users to do for they are very limited with computer knowledge. My idea is to have a command button somewhere in Excel where the users can just click on after entering all the data in Excel and all of the relevant infomation( info in certain cells only) is duplicated or merged into the letter that is in Word format. After the merge, the completed letter is then open for the users to see and double check or do any final edits. If possible, can you post me a sample OLE automation code that I can try to use? And where would I put this code in Excel? I'm pretty new with this therefore the more instructions the better. THank you so much for your help! "Carlos" wrote: Hi, Perhaps it would be better for Kaylen to explore mail-merge first and, if it does not meet its requirements, turn to OLE automation. Interaction between Word and Excel is not hard to accomplish, but it requires you to know well Word and Excel objects. Kaylen, if you wish, I can post some sample code to transfer data from an Excel table (a range) to a Word table. Having said that, I think one must exhaust simple solutions (mail-merge) before moving to more complex ones (OLE automation). -- Carlos "Joel" wrote: YOu can control word documents through excel. Here is some starter code Sub Test() ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next 'Get word object if application already is opened. Set WDApp = GetObject(, "Word.Application") If Err.Number < 0 Then Set WDApp = CreateObject("Word.Application") WordWasRunning = False End If WDApp.Visible = True 'at least for testing! Set WDDoc = WDApp.documents.Open(Filename:=FName) Set WordTable = WDDoc.tables(1) WDDoc.Close End Sub "Kaylen" wrote: I have an Excel form and a Word form that need to go together. The Excel form has to be in excel format because of complicated calculations. After the Excel form was filled out, currently the users have to take some of the results from the Excel form and duplicate them into a letter that is in Word format. Both the Excel form and letter were later sent out together. Is there a way for have this programmed to eliminated having to cut and paste data from Excel to Word? Maybe a macro in Excel that allows the generation of the letter.doc after done with the Excel form? Or the other way around? I don't know. I want to make this process short and simple as possibles because the users of the forms are not computer savy. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
word to excel data transfer help | Excel Discussion (Misc queries) | |||
how can you transfer data from excel to MS word? | Excel Discussion (Misc queries) | |||
Transfer Excel data to Word | Excel Programming | |||
Transfer data from EXCEL to WORD | Excel Programming | |||
Transfer data from EXCEL to WORD | Excel Programming |