M
mstuf
Good Evening -- Newbie Here ( Sophomore / Freshman ) -- Thanks fo
Looking. I realize this is a WORD forum. My Question is a Word VB
Question as it applies to an Excel Macro that moves data to a wor
document.
I am using Office XP - 2002.
I have a Working Excel Macro that has evolved through time from
Recorded Operation. Features have been added with the gracious help o
others , Google, experimentation and LUCK. Some of whats there exceed
my understanding. I've used it 1000's of times in the last couple years
It saves time and provides considerable error reduction. I would like t
add another routine to it and would really appreciate help.
This Macro is run from a chosen active cell in an Excel Workbook Name
"AMZ-GM-Combine.xls". It Highlights the entire row that the active cel
is in and Cuts the row from the Workbook. It pastes that row into th
first open row in Excel Workbook "AMZ-GM Sold.xls" . From that Paste
Row, it Copies Cell Z. It activates an already open Word Document an
Pastes "Special", that copied text, to the position of the Cursor. I
leaves the Word Document Visible.
Here's my working Excel / Word code.
Code
-------------------
}
Sub OpenToSold()
'Macro recorded 2/1/2008 by Me
' 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 'This leaves Word Open
' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
[/Code
I would like to add one more job to this Macro.
I would like to Also Copy the Contents of Cell S from "AMZ-GM Sold.xls" to the same open Word Document and Place it in the First Blank Line after the first row that contains more than 10 dash characters in a row. (--------------------)
I'm puzzled about how to initiate a Second operation.
I'm especially puzzled about how to designate that position in the word document. I assume that the postion ( First Blank Line after the first row that contains more than 10 dash characters in a row ) must be defined as an Object.
Mail Merge is not an option at this point because of other related macros for other segments of the routine.
Thanks for Looking. Any Help Appreciated
Looking. I realize this is a WORD forum. My Question is a Word VB
Question as it applies to an Excel Macro that moves data to a wor
document.
I am using Office XP - 2002.
I have a Working Excel Macro that has evolved through time from
Recorded Operation. Features have been added with the gracious help o
others , Google, experimentation and LUCK. Some of whats there exceed
my understanding. I've used it 1000's of times in the last couple years
It saves time and provides considerable error reduction. I would like t
add another routine to it and would really appreciate help.
This Macro is run from a chosen active cell in an Excel Workbook Name
"AMZ-GM-Combine.xls". It Highlights the entire row that the active cel
is in and Cuts the row from the Workbook. It pastes that row into th
first open row in Excel Workbook "AMZ-GM Sold.xls" . From that Paste
Row, it Copies Cell Z. It activates an already open Word Document an
Pastes "Special", that copied text, to the position of the Cursor. I
leaves the Word Document Visible.
Here's my working Excel / Word code.
Code
-------------------
}
Sub OpenToSold()
'Macro recorded 2/1/2008 by Me
' 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 'This leaves Word Open
' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
[/Code
I would like to add one more job to this Macro.
I would like to Also Copy the Contents of Cell S from "AMZ-GM Sold.xls" to the same open Word Document and Place it in the First Blank Line after the first row that contains more than 10 dash characters in a row. (--------------------)
I'm puzzled about how to initiate a Second operation.
I'm especially puzzled about how to designate that position in the word document. I assume that the postion ( First Blank Line after the first row that contains more than 10 dash characters in a row ) must be defined as an Object.
Mail Merge is not an option at this point because of other related macros for other segments of the routine.
Thanks for Looking. Any Help Appreciated