Newbie (simple?) Copy and Paste from Excel to Word Macro

[RogerPang]

Hello,

First post - have looked for simple solution, but strugging with search options on the site - please could someone point me in the direction of a good existing solution - or even solve my problem below. I am an advanced Excel (formula) user, but very new to macros and VBA.

I have an Excel 2010 spreadsheet designed for novice users. I'd like a macro I can give to the users that copies data (say from column A "A:A" in the active sheet), then opens a new Word 2010 document, adjusts the margins of the word document to 'narrow', pastes the data into the word document (straightforward paste is fine as the data has already been converted to text within the spreadsheet), then saves the Word document as a file on their desktop with the file name taken from a different sheet (say 'Data') and cell name (say $B$3) in the Excel doc.

I have found a macro that opens a new document (below), but cannot find how to then perform the copy and paste or adjust margins etc.:

Sub New_Word_Doc_RP()
' add a reference to the Word-library
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add ' create a new document

'wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

Any help appreciated.

Roger

[patel45]

Please Login or Register  to view this content.

[RogerPang]

Thanks for v quick response - so far so good, with the following I can now copy and paste my Column A into a new Word document - do you know how I can adjust the Margins of the Word document?

Sub ExportWord1()
Dim number As Integer, i As Integer, WS As Worksheet, number_exported As Integer
Dim wdApp As Object, wdDoc As Object, MyWd As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
Set WS = ActiveSheet
Range("A:A").Copy

wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
wdApp.Visible = True
Set wdApp = Nothing
End Sub

[RogerPang]

o.k - I've sussed the margins - updated macro below. Now 'just' want to SAVE the Word document as a file on the user's desktop with the file name taken from a different Excel sheet (say 'Data') and cell name (say $B$3) in the Excel doc.

Any suggestions??

Sub ExportWord1()
Dim number As Integer, i As Integer, WS As Worksheet, number_exported As Integer
Dim wdApp As Object, wdDoc As Object, MyWd As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
Set WS = ActiveSheet
Range("A:A").Copy

wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste

' Margins Macro
' SET MARGINS TO NARROW
'
wdDoc.PageSetup.LineNumbering.Active = False
wdDoc.PageSetup.Orientation = wdOrientPortrait
wdDoc.PageSetup.TopMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.BottomMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.LeftMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.RightMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.Gutter = MillimetersToPoints(0)
wdDoc.PageSetup.HeaderDistance = MillimetersToPoints(12.5)
wdDoc.PageSetup.FooterDistance = MillimetersToPoints(12.5)
wdDoc.PageSetup.PageWidth = MillimetersToPoints(210)
wdDoc.PageSetup.PageHeight = MillimetersToPoints(297)
wdDoc.PageSetup.FirstPageTray = wdPrinterDefaultBin
wdDoc.PageSetup.OtherPagesTray = wdPrinterDefaultBin
wdDoc.PageSetup.SectionStart = wdSectionNewPage
wdDoc.PageSetup.OddAndEvenPagesHeaderFooter = False
wdDoc.PageSetup.DifferentFirstPageHeaderFooter = False
wdDoc.PageSetup.VerticalAlignment = wdAlignVerticalTop
wdDoc.PageSetup.SuppressEndnotes = False
wdDoc.PageSetup.MirrorMargins = False
wdDoc.PageSetup.TwoPagesOnOne = False
wdDoc.PageSetup.BookFoldPrinting = False
wdDoc.PageSetup.BookFoldRevPrinting = False
wdDoc.PageSetup.BookFoldPrintingSheets = 1
wdDoc.PageSetup.GutterPos = wdGutterPosLeft

wdApp.Visible = True
Set wdApp = Nothing
End Sub

[RogerPang]

O.k. - with a bit more searching - now all sorted - final macro below...


Sub ExportWord1()
Dim number As Integer, i As Integer, WS As Worksheet, number_exported As Integer
Dim wdApp As Object, wdDoc As Object, MyWd As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
Set WS = ActiveSheet
Range("A:A").Copy

wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste

' Margins Macro
' SET MARGINS TO NARROW
'
wdDoc.PageSetup.LineNumbering.Active = False
wdDoc.PageSetup.Orientation = wdOrientPortrait
wdDoc.PageSetup.TopMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.BottomMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.LeftMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.RightMargin = MillimetersToPoints(12.7)
wdDoc.PageSetup.Gutter = MillimetersToPoints(0)
wdDoc.PageSetup.HeaderDistance = MillimetersToPoints(12.5)
wdDoc.PageSetup.FooterDistance = MillimetersToPoints(12.5)
wdDoc.PageSetup.PageWidth = MillimetersToPoints(210)
wdDoc.PageSetup.PageHeight = MillimetersToPoints(297)
wdDoc.PageSetup.FirstPageTray = wdPrinterDefaultBin
wdDoc.PageSetup.OtherPagesTray = wdPrinterDefaultBin
wdDoc.PageSetup.SectionStart = wdSectionNewPage
wdDoc.PageSetup.OddAndEvenPagesHeaderFooter = False
wdDoc.PageSetup.DifferentFirstPageHeaderFooter = False
wdDoc.PageSetup.VerticalAlignment = wdAlignVerticalTop
wdDoc.PageSetup.SuppressEndnotes = False
wdDoc.PageSetup.MirrorMargins = False
wdDoc.PageSetup.TwoPagesOnOne = False
wdDoc.PageSetup.BookFoldPrinting = False
wdDoc.PageSetup.BookFoldRevPrinting = False
wdDoc.PageSetup.BookFoldPrintingSheets = 1
wdDoc.PageSetup.GutterPos = wdGutterPosLeft

wdApp.Visible = True

wdApp.ActiveDocument.SaveAs Range("B3").Value


Set wdApp = Nothing
End Sub