-
This is the final version of the code I used in the spreadsheet.
[vba]
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Opening Word Document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\Dominguc\My Documents\Letter.dot")
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks(ws.Name).Range.Paste
Application.CutCopyMode = False
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub
[/vba]
-
This is the final version of the code in the spreadsheet:
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Opening Word Document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\Dominguc\My Documents\Letter.dot") '.Add
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks(ws.Name).Range.Paste
Application.CutCopyMode = False
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub
-
I just thought of something with regards to the code. This code sits on the spreadsheet and I was trying to put the code on Excel so that the user could run it after opening up the new spreadsheet that will get sent on a regular basis with new data. Does Excel have a global location where the data can be put and then the user can run it on the new spreadsheet. Sort of like the Normal.dot in Word.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules