Wolf80
12-12-2012, 09:33 AM
Please Help I am trying to create a vba string that will create a word doc from Excel. I have the following code that works fine for the first book mark and then returns to excel and collects the second lot of data however after that I cann't seem to return to word and copy to the second bookmark without losing the first set of Data any ideas?
Sub B()
Dim LR As Long, LC As Long, cell As Range, rng As Range
Dim appWrd As Object
Dim objDoc As Object
Dim FilePath As String
Dim FileName As String
Dim x As Long
Dim LastRow As Long
Dim SheetChart As String
Dim SheetRange As String
Dim BookMarkChart As String
Dim BookMarkRange As String
Dim Prompt As String
Dim Title As String
Const wdGoToBookmark As Long = -1
' Application.ScreenUpdating = False
' Application.EnableEvents = False
' Application.DisplayAlerts = False
'Assign the Word file path and name to variables
FilePath = "N:\eng\gtpe\03. Products\10. General SAS & TS\MRS Master"
FileName = "Spec word template.doc"
'Workbooks.Open FileName:="N:\eng\gtpe\03. Products\10. General SAS & TS\MRS Master\MRS_Template.xls"
Set sourceSheet = Worksheets("B")
sourceSheet.Activate
LastCol = ActiveSheet.Range("q3").End(xlToRight).Column
LastRow = ActiveSheet.Range("q3").End(xlDown).Row
ActiveSheet.Range("q3:" & _
ActiveSheet.Cells(LastRow, LastCol).Address).Copy
'Assign the Word file path and name to variables
FilePath = "N:\eng\gtpe\03. Products\10. General SAS & TS\MRS Master"
FileName = "Spec word template.doc"
'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
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)
'Word to goto the bookmark assigned to the variable BookMarkRange
appWrd.Selection.Goto What:=wdGoToBookmark, Name:="B"
Application.SendKeys ("^v")
Sub B()
Dim LR As Long, LC As Long, cell As Range, rng As Range
Dim appWrd As Object
Dim objDoc As Object
Dim FilePath As String
Dim FileName As String
Dim x As Long
Dim LastRow As Long
Dim SheetChart As String
Dim SheetRange As String
Dim BookMarkChart As String
Dim BookMarkRange As String
Dim Prompt As String
Dim Title As String
Const wdGoToBookmark As Long = -1
' Application.ScreenUpdating = False
' Application.EnableEvents = False
' Application.DisplayAlerts = False
'Assign the Word file path and name to variables
FilePath = "N:\eng\gtpe\03. Products\10. General SAS & TS\MRS Master"
FileName = "Spec word template.doc"
'Workbooks.Open FileName:="N:\eng\gtpe\03. Products\10. General SAS & TS\MRS Master\MRS_Template.xls"
Set sourceSheet = Worksheets("B")
sourceSheet.Activate
LastCol = ActiveSheet.Range("q3").End(xlToRight).Column
LastRow = ActiveSheet.Range("q3").End(xlDown).Row
ActiveSheet.Range("q3:" & _
ActiveSheet.Cells(LastRow, LastCol).Address).Copy
'Assign the Word file path and name to variables
FilePath = "N:\eng\gtpe\03. Products\10. General SAS & TS\MRS Master"
FileName = "Spec word template.doc"
'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
Set objDoc = appWrd.Documents.Open(FilePath & "\" & FileName)
'Word to goto the bookmark assigned to the variable BookMarkRange
appWrd.Selection.Goto What:=wdGoToBookmark, Name:="B"
Application.SendKeys ("^v")