Hello Excel gurus!
I have a piece of code I've been compiling which ultimately I wish to perform the following actions:
User enters numbers under quantity column *works fine
Code copies items ordered to a temp worksheet *works fine
Code copies entire range of temp worksheet *works fine
Call word document template *works fine
Set cursor to end of document *FAIL (object pastes over existing data)
Paste selection *works fine
Print Document *turned off until paste works correctly
Save document to current date *works fine
Close word *works fine
Delete temp worksheet *works fine
Reset quantity values *works fine
utilizing this link: http://www.microsoft.com/technet/script…5/tips0519.mspx
I incorporated the wdStory / wdMove scripts to move the cursor to the end of the document. still this had no effect and pastes the selection over the existing template.
please advise of what action I am failing to turn on, or placing in the wrong location.
THANK YOU!!!
Private Sub cmdSubmit_Click()
Dim i, j, mypos, LastRow As Integer
Dim Cell, Row
'Dim rng, MyRange As Range
Application.ScreenUpdating = False
'set active sheet and create new worksheet for order
Set A = ActiveSheet
Sheets.Add().Name = "Temp"
Set b = ActiveSheet
LastRow = 92
j = 1
A.Select
'copy ordered items into temp worksheet
For i = 3 To LastRow
If IsEmpty(Cells(i, 3)) Then
'do nothing
Else
Range(Cells(i, 1), Cells(i, 3)).Select
Selection.Copy
b.Select
ActiveSheet.Rows(j).Select
ActiveSheet.Paste
j = j + 1
End If
Application.CutCopyMode = False
A.Select
'Beep
Next i
'set "temp" active for copying to word
i = 0
j = 0
b.Select
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
'Copy to Word
Dim wrdApp As Object
'Dim wrdApp As Word.Application 'action never worked - turned off
Dim wrdDoc As Object
'Set cursor location requirements
Const wdStory = 6
Const wdMove = 0
'Set wrdApp = GetObject(, "Word.Application") 'action never worked - turned off
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("S:\Aaron\ProductOrder.dot")
With wrdDoc
Set objSelection = wrdApp.Selection
objSelection.Endkey wdStory, wdMove
objSelection.TypeText "Thank you"
'.Content.InsertTable
'.Content.InsertParagraphAfter
wrdDoc.Range.PasteSpecial Link:=False, Placement:=wdEndKey, DisplayAsIcon:=False 'InLine
'wrdDoc.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
'.Print
Application.DisplayAlerts = False
.SaveAs ("S:\Aaron\" & Format$(Date, "mm-dd-yyyy") & ".ProductOrder.doc")
Application.DisplayAlerts = True
.Close
End With
wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
'Print / Save Word
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
A.Select
Application.ScreenUpdating = True
''Clear Contents----->
'Set MyRange = ActiveSheet.Range("C4:C93")
'MyRange.ClearContents
'
End Sub
Display More