Pasting Excel Data into Word at Offset Location

[mstuf]

Hello --

I have an Excel Macro that Pastes Excel Data from Active Row Cell Z to the Active Word Document at the point the cursor is placed.

I am trying to expand this macro to go back an pick up a 2nd Cell ( S ) an place the data 7 Lines ahead of the cursor position.

In Excel its the Offset command --

Can anyone help with the Code to Paste Special, 7 lines after the position of the Cursor.

I can post current code and modified code for the 2nd Copy action ( lacking the paste special + 7 command ) if it will help. I can also post text of examples what I am trying to accomplish.

Thanks for Looking - I'm a novice with just enough knowledge to be dangerous.

 

[Norie]

Why not just do this with Word mail merge?

 

[mstuf]

I guess I'm just not familiar with the process.

Here is my Excel Macro so you can see what I do.

This Macro runs from an Open Inventory Excel File and Cuts a Sold Item's row based on SKU Number and pastes it into my Sold Excel File - then copies the description cell and pastes it as text into my open word Doc at the position of the cursor.

Sub OpenToSold()

   ' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim lCol As Long
Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from Sold.xls
ActiveSheet.Cells(lRow + 1, 1).Activate 'Add 1 to the last row for first blank row
ActiveSheet.Paste
Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z


Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
WDApp.Selection.PasteSpecial
WDApp.Visible = True 
' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub

I was thinking that if I replicated the Copy from Excel Portion

Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 20).Copy ' Copy S--- 20 = S

and then the word portion

WDApp.Selection.PasteSpecial   '(adding +7 code )  
WDApp.Visible = True

that I could accomplish both transfers in One Macro. As always - I just back up my working copy and wade in. As Stated - "Enough knowledge to be dangerous"

I will google and read about word mail merge

 

[Norie]

I've not seen your data but from what I know of it from your posts I think you might be able to do some of this with Word mail merge without code.

For example I think you have data across columns, that's almost ideal for use with a mail merge.

Each row is a record and each column with data in that row is a field.

It's hard to give specific details but you could just try going to Tools>Mail Merge in Word.

That should start up a wizard that will guide you through.

It will give you various options, one of which will be to select a data source.

At that stage you select your Excel file.

All I can say is give it a try.

 

[mstuf]

I went and read some about it -- Its approaching 3 AM Here. I will mess with it tomorrow.

You helped me earlier with Excel - Omiting Line from Code when cell is Empty. Thanks again.

That was part of preparing this Word Document.

I used to get a nice useable - almost ready to print sales notification for each sale on a website I sell through. I had a set of sweet macros that almost automated the whole process of removing the item from excel open to excel sold and prepared a Shipping Label - Pull Sheet - File Copy and Invoice. Then they "New and Improved" Everything. Todays earlier work got me back to where we were. I have this evening, made a couple improvements. This step would have been a nice additional touch.

I'll read to see if Mail Merge can move my data into an existing document. As the Omit Line Question earlier fixed, each sales document does not always place items on the same Line Number becuase some have 3 Line Name and Address and Some can have 5 which alters the layout of the document in two different places.

Thanks Again -- Soon as Work in done I will envoke the wizard !

 

[Norie]

One of the options on Mail Merge is to create labels.

I also think there's something that deals with addresses with missing parts.

 

[bulevardi]

One of the options on Mail Merge is to create labels.

Indeed.

If you want to make mailing labels with data from an Excel table, so you can mail merge this in Word, you can do it automatically with a simple VBA script.

Here's an example:
http://bulevardi.be/?content=scripting&example=exvb3

 

[mstuf]

Thanks Bulevardi -- Nice Example --

Unfortunately, Theres more to my routine than shown in asking for help on my specific need issue.

In order explain Everything about my Routine it would have doubled the size of my OP which i thought was already getting long and complex -- So I tried to abreviate and only show what was necessary to try to learn the thing I needed.

My Whole routine nets me way more than a Mailing Label.

From rows in Two Different Workbooks and info in an Order Notification, My Routine Creates:

A Mailing Label with Postage type.
A Pull Sheet for the shop.
A Record for my Shipping Log.
A Packing List for the Customer.
Labels for Packages requiring Customs Forms

that is all on one sheet.

PLUS, it removes inventory from my Open Excel File and Pastes it to my Sold Excel File.


All, with Just a few Keystroke Shortcuts to my Macros.

Implementing Mail Merge at this point, would be starting over or at best revising the other 95% of the Routine that works very well that I understand.

In this last week I have been Google Reading and have found some info on Loops , Text Placement and Defining Objects with Word VBA. There doesnt seem to be a "book in town" on Word VBA.

Getting Closer.

Thanks Again