Checkjr
09-22-2010, 01:04 PM
I'm currently trying to modify an excel spreadsheet to output data to a word .dot file. For the most part, I've been able to figure out the individual code, but when I put it together, bits and pieces aren't working. In a nutshell, I'm copying and pasting quotes generated by our sales group. I'll always know the number of columns, but the number of rows can vary from 1-393. My original plan was to create a table in my word dot file and paste in the excel data, but that was too complicated.
I then realized I can do a simple copy and paste in to word, and all it requires is reformatting the table. Based on where the cursor is, I thought my macro would have been:
With oWD
.Bookmarks("Table").Range.Paste
.Selection.moveUp unit:=wdline, Count:=a, Extend:=wdExtend
.Selection.moveRight unit:=wdCharacter, Count:=3, Extend:=wdExtend
end with
In my trials, this would select my table which would then allow me to easily format. However, when I put the code in my excel file and attempt to do this, I can get the table to paste where I want it, but I can't figure out how to select and then format the table. I've tried to record the macro in Word, but you can't highlight the table while using the record function. Should I set up a before and after bookmark and then somehow select everything between them?
Thanks!
Dim oWA As Word.Application
Dim oWD As Word.document
Dim n As Integer
Dim z As Integer
Dim a As Integer
'determine the cells to copy and paste: Note, the numbers
'are large due to the hidden rows
'only what is visible will be copied
n = 393
a = Cells(1, 1).Value
z = n + 11
Range("b11", Cells(z, 25)).Select
Selection.Copy
'Make desired word template active and writeable
On Error Resume Next
Set oWA = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set oWA = CreateObject("Word.Application")
End If
On Error GoTo 0
Set oWD = oWA.Documents.Open("C:\Documents and Settings\gindsx\Desktop\PDS\Service Contracts\TESTING\Pricing Guide.dot")
oWA.Visible = True
'paste in the excel table
With oWD
.Bookmarks("Table").Range.Paste
'This line should move the cursor up the number of rows that were pasted
.Selection.moveUp unit:=wdline, Count:=a, Extend:=wdExtend
'This line should move the cursor right the number of pasted columns
.Selection.moveRight unit:=wdCharacter, Count:=3, Extend:=wdExtend
'Format the table
With Selection
.Font.Name = "Arial"
.Font.Size = 8
.Columns(1).AutoFit
.Columns(2).AutoFit
.Columns(3).AutoFit
.Columns(4).AutoFit
End With
End With
Oh, here's the full code.
I then realized I can do a simple copy and paste in to word, and all it requires is reformatting the table. Based on where the cursor is, I thought my macro would have been:
With oWD
.Bookmarks("Table").Range.Paste
.Selection.moveUp unit:=wdline, Count:=a, Extend:=wdExtend
.Selection.moveRight unit:=wdCharacter, Count:=3, Extend:=wdExtend
end with
In my trials, this would select my table which would then allow me to easily format. However, when I put the code in my excel file and attempt to do this, I can get the table to paste where I want it, but I can't figure out how to select and then format the table. I've tried to record the macro in Word, but you can't highlight the table while using the record function. Should I set up a before and after bookmark and then somehow select everything between them?
Thanks!
Dim oWA As Word.Application
Dim oWD As Word.document
Dim n As Integer
Dim z As Integer
Dim a As Integer
'determine the cells to copy and paste: Note, the numbers
'are large due to the hidden rows
'only what is visible will be copied
n = 393
a = Cells(1, 1).Value
z = n + 11
Range("b11", Cells(z, 25)).Select
Selection.Copy
'Make desired word template active and writeable
On Error Resume Next
Set oWA = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set oWA = CreateObject("Word.Application")
End If
On Error GoTo 0
Set oWD = oWA.Documents.Open("C:\Documents and Settings\gindsx\Desktop\PDS\Service Contracts\TESTING\Pricing Guide.dot")
oWA.Visible = True
'paste in the excel table
With oWD
.Bookmarks("Table").Range.Paste
'This line should move the cursor up the number of rows that were pasted
.Selection.moveUp unit:=wdline, Count:=a, Extend:=wdExtend
'This line should move the cursor right the number of pasted columns
.Selection.moveRight unit:=wdCharacter, Count:=3, Extend:=wdExtend
'Format the table
With Selection
.Font.Name = "Arial"
.Font.Size = 8
.Columns(1).AutoFit
.Columns(2).AutoFit
.Columns(3).AutoFit
.Columns(4).AutoFit
End With
End With
Oh, here's the full code.