|
Copy from Excel to bookmarks in Word
Hello
I'm using the macro below, but I can't get it working the way I like it. (A macro I found on the net) The first problem happens while paste in the name bookmark, there it paste the Excel cell with text and not only the text. Why? And why do I sometimes have to start the macro 2 times before the word file will open? Sub TilMerke() Dim appWrd As Object Dim objDoc As Object Dim FilePath As String Dim FileName As String Dim LastRow As Long Dim Prompt As String Dim Title As String 'Turn some stuff off while the macro is running Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False 'Assign the Word file path and name to variables FilePath = ThisWorkbook.Path FileName = "WorkWithExcel.doc" 'Determine the last row of data for our loop LastRow = Sheets("Sykemeldinger").Range("A65536").End(xlUp). Row 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("aa17") .Copy 'Create an instance of Word for us to use Set appWrd = CreateObject("Word.Application") 'Open our specified Word file, On Error is used in case the file is not there On Error Resume Next Set objDoc = appWrd.Documents.Open("C:\test\soknad2.doc") On Error GoTo 0 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="kommune" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("t17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("w17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="Fnummer" 'Paste into Word appWrd.Selection.Paste 'Make our Word session visible appWrd.Visible = True 'Clean up Set appWrd = Nothing Set objDoc = Nothing End Sub |
Copy from Excel to bookmarks in Word
Hi,
Firstly I would early bind to the Word object model. You need to set a reference through tools-references. You can then do things like; Dim oWord as Word.Application Set oWord=New Word.Application A benefit of this over CreateObject is you can then use intellisense to navigate the objects. Regards, Chris. -- Chris Marlow MCSD.NET, Microsoft Office XP Master " wrote: Hello I'm using the macro below, but I can't get it working the way I like it. (A macro I found on the net) The first problem happens while paste in the name bookmark, there it paste the Excel cell with text and not only the text. Why? And why do I sometimes have to start the macro 2 times before the word file will open? Sub TilMerke() Dim appWrd As Object Dim objDoc As Object Dim FilePath As String Dim FileName As String Dim LastRow As Long Dim Prompt As String Dim Title As String 'Turn some stuff off while the macro is running Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False 'Assign the Word file path and name to variables FilePath = ThisWorkbook.Path FileName = "WorkWithExcel.doc" 'Determine the last row of data for our loop LastRow = Sheets("Sykemeldinger").Range("A65536").End(xlUp). Row 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("aa17") .Copy 'Create an instance of Word for us to use Set appWrd = CreateObject("Word.Application") 'Open our specified Word file, On Error is used in case the file is not there On Error Resume Next Set objDoc = appWrd.Documents.Open("C:\test\soknad2.doc") On Error GoTo 0 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="kommune" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("t17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("w17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="Fnummer" 'Paste into Word appWrd.Selection.Paste 'Make our Word session visible appWrd.Visible = True 'Clean up Set appWrd = Nothing Set objDoc = Nothing End Sub |
Copy from Excel to bookmarks in Word
Hi
thanks for you reply, My problem is that I have 3 booksmarks, so I've done something rigth. But in one of the bookmarks it copys differently than the 2 that works fine. (It copys 2 cells with only the text and one with text and the cell from excel.) Regards Ove Malde Chris Marlow wrote: Hi, Firstly I would early bind to the Word object model. You need to set a reference through tools-references. You can then do things like; Dim oWord as Word.Application Set oWord=New Word.Application A benefit of this over CreateObject is you can then use intellisense to navigate the objects. Regards, Chris. -- Chris Marlow MCSD.NET, Microsoft Office XP Master " wrote: Hello I'm using the macro below, but I can't get it working the way I like it. (A macro I found on the net) The first problem happens while paste in the name bookmark, there it paste the Excel cell with text and not only the text. Why? And why do I sometimes have to start the macro 2 times before the word file will open? Sub TilMerke() Dim appWrd As Object Dim objDoc As Object Dim FilePath As String Dim FileName As String Dim LastRow As Long Dim Prompt As String Dim Title As String 'Turn some stuff off while the macro is running Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False 'Assign the Word file path and name to variables FilePath = ThisWorkbook.Path FileName = "WorkWithExcel.doc" 'Determine the last row of data for our loop LastRow = Sheets("Sykemeldinger").Range("A65536").End(xlUp). Row 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("aa17") .Copy 'Create an instance of Word for us to use Set appWrd = CreateObject("Word.Application") 'Open our specified Word file, On Error is used in case the file is not there On Error Resume Next Set objDoc = appWrd.Documents.Open("C:\test\soknad2.doc") On Error GoTo 0 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="kommune" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("t17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("w17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="Fnummer" 'Paste into Word appWrd.Selection.Paste 'Make our Word session visible appWrd.Visible = True 'Clean up Set appWrd = Nothing Set objDoc = Nothing End Sub |
Copy from Excel to bookmarks in Word
Ove,
If you just want the text at the bookmark I would replace with something like this; objDoc.Range(objDoc.Bookmarks("kommune").Start).In sertAfter_ ThisWorkbook.Sheets("Sykemeldinger").Range("aa17") objDoc.Range(objDoc.Bookmarks("navn").Start).Inser tAfter _ ThisWorkbook.Sheets("Sykemeldinger").Range("t17") objDoc.Range(objDoc.Bookmarks("Fnummer").Start).In sertAfter _ ThisWorkbook.Sheets("Sykemeldinger").Range("w17") I've not had time to test ... let me know how it goes. Regards, Chris. -- Chris Marlow MCSD.NET, Microsoft Office XP Master " wrote: Hi thanks for you reply, My problem is that I have 3 booksmarks, so I've done something rigth. But in one of the bookmarks it copys differently than the 2 that works fine. (It copys 2 cells with only the text and one with text and the cell from excel.) Regards Ove Malde Chris Marlow wrote: Hi, Firstly I would early bind to the Word object model. You need to set a reference through tools-references. You can then do things like; Dim oWord as Word.Application Set oWord=New Word.Application A benefit of this over CreateObject is you can then use intellisense to navigate the objects. Regards, Chris. -- Chris Marlow MCSD.NET, Microsoft Office XP Master " wrote: Hello I'm using the macro below, but I can't get it working the way I like it. (A macro I found on the net) The first problem happens while paste in the name bookmark, there it paste the Excel cell with text and not only the text. Why? And why do I sometimes have to start the macro 2 times before the word file will open? Sub TilMerke() Dim appWrd As Object Dim objDoc As Object Dim FilePath As String Dim FileName As String Dim LastRow As Long Dim Prompt As String Dim Title As String 'Turn some stuff off while the macro is running Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False 'Assign the Word file path and name to variables FilePath = ThisWorkbook.Path FileName = "WorkWithExcel.doc" 'Determine the last row of data for our loop LastRow = Sheets("Sykemeldinger").Range("A65536").End(xlUp). Row 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("aa17") .Copy 'Create an instance of Word for us to use Set appWrd = CreateObject("Word.Application") 'Open our specified Word file, On Error is used in case the file is not there On Error Resume Next Set objDoc = appWrd.Documents.Open("C:\test\soknad2.doc") On Error GoTo 0 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="kommune" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("t17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn" 'Paste into Word appWrd.Selection.Paste 'Copy the data from Thisworkbook ThisWorkbook.Sheets("Sykemeldinger").Range("w17"). Copy 'Tell Word to goto the bookmark assigned to the variable BookMarkRange appWrd.Selection.Goto What:=wdGoToBookmark, Name:="Fnummer" 'Paste into Word appWrd.Selection.Paste 'Make our Word session visible appWrd.Visible = True 'Clean up Set appWrd = Nothing Set objDoc = Nothing End Sub |
Copy from Excel to bookmarks in Word
hello
thanks again for you help. I didn't get it working so I for the one with the problems I added this ThisWorkbook.Sheets("Sykemeldinger").Range("t17"). Copy appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn" appWrd.Selection.PasteSpecial , Text = " " appWrd.Selection.TypeBackspace regards Ove Malde Chris Marlow wrote: Ove, If you just want the text at the bookmark I would replace with something like this; objDoc.Range(objDoc.Bookmarks("kommune").Start).In sertAfter_ ThisWorkbook.Sheets("Sykemeldinger").Range("aa17") objDoc.Range(objDoc.Bookmarks("navn").Start).Inser tAfter _ ThisWorkbook.Sheets("Sykemeldinger").Range("t17") objDoc.Range(objDoc.Bookmarks("Fnummer").Start).In sertAfter _ ThisWorkbook.Sheets("Sykemeldinger").Range("w17") I've not had time to test ... let me know how it goes. Regards, Chris. -- |
| All times are GMT +1. The time now is 08:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com