Copy from excel to Word
in [Word VBA]
|
From: Kentae on 20 Apr 2010 13:31 Hi I need a simple way to copy a value /string from a cell i ecxel to a bookmark in a worddocumnet. Tanks in advance for all support
From: Fumei2 via OfficeKB.com on 20 Apr 2010 13:53 What have you tried so far? What have you looked up/researched? This is a very common thing. Also, you do not state if you are working from Excel (getting its own information and putting that into Word), or from Word (getting information FROM Excel, and putting it into its own bookmark). Working FROM Excel, with an existing Word document Dim appWord As Word.Application Dim wrdDoc As Word.Document Set appWord = CreateObject("Word.Application") Set wrdDoc = appWord.Documents.Open _ (FileName:="c:\zzz\Test\ExistingFile.doc") wrdDoc.Bookmarks("PutDataThere").Range.Text = Worksheets("Sheet1").Range("A1") .Value The above is not full code (by any means) but the method is basically the way you do it. 1. you make an instance of the application 2. you use that instance Kentae wrote: >Hi >I need a simple way to copy a value /string from a cell i ecxel to a >bookmark in a worddocumnet. >Tanks in advance for all support -- Gerry Message posted via http://www.officekb.com
From: Dave Williams on 22 Apr 2010 11:20 I too am trying to utilize data in an Excel sheet. My vba runs from the Word doc... I need to open a MS Excel file (using filedialog if possible) then retrieve the data from well over 200 cells within the worksheet, then transfer the information to the Word document. I will be adding text and/or updating check boxes using bookmarks to locate the appropriate document point.... I have a couple issues.... First, once I open the xls I can get the data ok and paste into the Word doc.... but how do I close the xls (when finished) so that I can open later without an error that the file is locked for editing by myself... Also I need direction on how to use the filedialog function to allow users to use a standard windows type open file dialog to search their pc for the appropriate xls to open... do I actually open the file or can I just reference the file (getting the name from the filedialog)? The information is on the same sheet (name is same) no matter what xls the user will open... Anything you can do to get me on the right track would be great! Later perhaps you can give me some insight on what might be the best programming approach since I need to pull so much from the xls... Thanks! Dave "Fumei2 via OfficeKB.com" wrote: > What have you tried so far? What have you looked up/researched? This is a > very common thing. > > Also, you do not state if you are working from Excel (getting its own > information and putting that into Word), or from Word (getting information > FROM Excel, and putting it into its own bookmark). > > Working FROM Excel, with an existing Word document > > Dim appWord As Word.Application > Dim wrdDoc As Word.Document > > Set appWord = CreateObject("Word.Application") > Set wrdDoc = appWord.Documents.Open _ > (FileName:="c:\zzz\Test\ExistingFile.doc") > > wrdDoc.Bookmarks("PutDataThere").Range.Text = Worksheets("Sheet1").Range("A1") > .Value > > The above is not full code (by any means) but the method is basically the way > you do it. > > 1. you make an instance of the application > 2. you use that instance > > > Kentae wrote: > >Hi > >I need a simple way to copy a value /string from a cell i ecxel to a > >bookmark in a worddocumnet. > >Tanks in advance for all support > > -- > Gerry > > Message posted via http://www.officekb.com > > . >
From: Doug Robbins - Word MVP on 22 Apr 2010 17:31 To see how to properly access and then release Excel, see the article "Control Excel from Word” at: http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm To allow the user to browse to and select the workbook, use Dim fd As FileDialog Dim strWorkbook As String Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .Title = "Select the Excel Workbook to use." .InitialFileName = "*.xls?" .AllowMultiSelect = False If .Show = -1 Then strWorkbook = .SelectedItems(1) Else MsgBox "No WorkBook Selecte." Exit Sub End If End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Dave Williams" <DaveWilliams(a)discussions.microsoft.com> wrote in message news:2B814204-AB4D-4B81-A04D-10D0EB5DD568(a)microsoft.com... > I too am trying to utilize data in an Excel sheet. My vba runs from the > Word > doc... I need to open a MS Excel file (using filedialog if possible) then > retrieve the data from well over 200 cells within the worksheet, then > transfer the information to the Word document. I will be adding text > and/or > updating check boxes using bookmarks to locate the appropriate document > point.... > > I have a couple issues.... > > First, once I open the xls I can get the data ok and paste into the Word > doc.... but how do I close the xls (when finished) so that I can open > later > without an error that the file is locked for editing by myself... > > Also I need direction on how to use the filedialog function to allow users > to use a standard windows type open file dialog to search their pc for the > appropriate xls to open... do I actually open the file or can I just > reference the file (getting the name from the filedialog)? The > information > is on the same sheet (name is same) no matter what xls the user will > open... > > Anything you can do to get me on the right track would be great! > > Later perhaps you can give me some insight on what might be the best > programming approach since I need to pull so much from the xls... > > Thanks! > > Dave > > > "Fumei2 via OfficeKB.com" wrote: > >> What have you tried so far? What have you looked up/researched? This is >> a >> very common thing. >> >> Also, you do not state if you are working from Excel (getting its own >> information and putting that into Word), or from Word (getting >> information >> FROM Excel, and putting it into its own bookmark). >> >> Working FROM Excel, with an existing Word document >> >> Dim appWord As Word.Application >> Dim wrdDoc As Word.Document >> >> Set appWord = CreateObject("Word.Application") >> Set wrdDoc = appWord.Documents.Open _ >> (FileName:="c:\zzz\Test\ExistingFile.doc") >> >> wrdDoc.Bookmarks("PutDataThere").Range.Text = >> Worksheets("Sheet1").Range("A1") >> .Value >> >> The above is not full code (by any means) but the method is basically the >> way >> you do it. >> >> 1. you make an instance of the application >> 2. you use that instance >> >> >> Kentae wrote: >> >Hi >> >I need a simple way to copy a value /string from a cell i ecxel to a >> >bookmark in a worddocumnet. >> >Tanks in advance for all support >> >> -- >> Gerry >> >> Message posted via http://www.officekb.com >> >> . >>
From: Dave Williams on 23 Apr 2010 09:18 Doug: I really appreciate your recommendations. The one for controlling Excel works great and so does the filedialog... I updated the code utilizing the above and added some code for the meat of what I need to do. Problem is my code is not working now... Specifically if I retrieve data from Excel and attempt to paste into Word it doesn't work.. An example of what I mean is the following code: 'retrieve value of cell D2 in chosen workbook and place in active word document at bookmark NovStudyCode_D2 ActiveDocument.Bookmarks("NovStudyCode_D2").Range = Sheets("Novartis IRT Decision Grid").Range("D2").Value This exact code worked before.... If I go to the immediate window (while pausing the code) and hover over ? Range("D2").value I get an error: Method Range of object _ global Failed Where am I going wrong? Thanks in advance for all your assistance I really appreciate your direction...:) My full code is: Sub CreatecURS() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String Dim fd As FileDialog Dim strWorkbook As String Set fd = Application.FileDialog(msoFileDialogFilePicker) 'set object as a file dialog With fd .Title = "Select the Excel Workbook to use." .InitialFileName = "*.xls?" .AllowMultiSelect = False If .Show = -1 Then strWorkbook = .SelectedItems(1) 'Store name of workbook selected Else MsgBox "No WorkBook Selected." Exit Sub End If End With 'specify the workbook to work on WorkbookToWorkOn = strWorkbook 'identify workbook to use for data 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") 'retrieve value of cell D2 in chosen workbook and place in active word document at bookmark NovStudyCode_D2 ActiveDocument.Bookmarks("NovStudyCode_D2").Range = Sheets("Novartis IRT Decision Grid").Range("D2").Value 'retrieve value of cell I286 in chosen workbook and place in active word document at bookmark SystemSetupType_I286 ActiveDocument.Bookmarks("SystemSetupType_I286").Range = Sheets("Novartis IRT Decision Grid").Range("I286").Value 'check value of F36 and if yes, update checkbox DistrRegHub_F36 value to true If Sheets("Novartis IRT Decision Grid").Range("F36").Value = "yes" Then ActiveDocument.FormFields("DistrRegHub_F36").CheckBox.Value = True End If 'check value of F37 and if yes, update checkbox DistrCPO_F37 value to true If Sheets("Novartis IRT Decision Grid").Range("F37").Value = "yes" Then ActiveDocument.FormFields("DistrCPO_F37").CheckBox.Value = True End If 'check value of F37 and if yes, update checkbox DistrBoth_F38 value to true If Sheets("Novartis IRT Decision Grid").Range("F38").Value = "yes" Then ActiveDocument.FormFields("DistrBoth_F38").CheckBox.Value = True End If If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub -- Thanks! Dave "Doug Robbins - Word MVP" wrote: > To see how to properly access and then release Excel, see the article > "Control Excel from Word” at: > > http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm > > To allow the user to browse to and select the workbook, use > > Dim fd As FileDialog > Dim strWorkbook As String > Set fd = Application.FileDialog(msoFileDialogFilePicker) > With fd > .Title = "Select the Excel Workbook to use." > .InitialFileName = "*.xls?" > .AllowMultiSelect = False > If .Show = -1 Then > strWorkbook = .SelectedItems(1) > Else > MsgBox "No WorkBook Selecte." > Exit Sub > End If > End With > > > -- > Hope this helps. > > Please reply to the newsgroup unless you wish to avail yourself of my > services on a paid consulting basis. > > Doug Robbins - Word MVP, originally posted via msnews.microsoft.com > > "Dave Williams" <DaveWilliams(a)discussions.microsoft.com> wrote in message > news:2B814204-AB4D-4B81-A04D-10D0EB5DD568(a)microsoft.com... > > I too am trying to utilize data in an Excel sheet. My vba runs from the > > Word > > doc... I need to open a MS Excel file (using filedialog if possible) then > > retrieve the data from well over 200 cells within the worksheet, then > > transfer the information to the Word document. I will be adding text > > and/or > > updating check boxes using bookmarks to locate the appropriate document > > point.... > > > > I have a couple issues.... > > > > First, once I open the xls I can get the data ok and paste into the Word > > doc.... but how do I close the xls (when finished) so that I can open > > later > > without an error that the file is locked for editing by myself... > > > > Also I need direction on how to use the filedialog function to allow users > > to use a standard windows type open file dialog to search their pc for the > > appropriate xls to open... do I actually open the file or can I just > > reference the file (getting the name from the filedialog)? The > > information > > is on the same sheet (name is same) no matter what xls the user will > > open... > > > > Anything you can do to get me on the right track would be great! > > > > Later perhaps you can give me some insight on what might be the best > > programming approach since I need to pull so much from the xls... > > > > Thanks! > > > > Dave > > > > > > "Fumei2 via OfficeKB.com" wrote: > > > >> What have you tried so far? What have you looked up/researched? This is > >> a > >> very common thing. > >> > >> Also, you do not state if you are working from Excel (getting its own > >> information and putting that into Word), or from Word (getting > >> information > >> FROM Excel, and putting it into its own bookmark). > >> > >> Working FROM Excel, with an existing Word document > >> > >> Dim appWord As Word.Application > >> Dim wrdDoc As Word.Document > >> > >> Set appWord = CreateObject("Word.Application") > >> Set wrdDoc = appWord.Documents.Open _ > >> (FileName:="c:\zzz\Test\ExistingFile.doc") > >> > >> wrdDoc.Bookmarks("PutDataThere").Range.Text = > >> Worksheets("Sheet1").Range("A1") > >> .Value > >> > >> The above is not full code (by any means) but the method is basically the > >> way > >> you do it. > >> > >> 1. you make an instance of the application > >> 2. you use that instance > >> > >> > >> Kentae wrote: > >> >Hi > >> >I need a simple way to copy a value /string from a cell i ecxel to a > >> >bookmark in a worddocumnet. > >> >Tanks in advance for all support > >> > >> -- > >> Gerry > >> > >> Message posted via http://www.officekb.com > >> > >> . > >>
|
Next
|
Last
Pages: 1 2 Prev: Removing tables while preserving content Next: VBA To Create Complex Field Code? |