Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2006

Tip: Looking for answers? Try searching our database.

excel copy cells to word template

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
gtpighin@shaw.ca - 05 Aug 2006 09:55 GMT
I have a large workbook and I want to copy cells from one sheet called
Menu to a specific word template called PDC_MCC_IR.dot and save the
word template as a word document. I have a bit of code that can open a
word document and past information from cells in the word document but
I need help for the rest. Can anyone help me. Here is the code that I
already have.

Sub Excel_to_Word()
  Dim appWord As Word.Application

   Set appWord = New Word.Application

   appWord.Visible = True

   Range("b4:c10").Copy

   appWord.Documents.Add.Content.Paste

   Application.CutCopyMode = False
   

   
End Sub

Thanks Garry gtpighin@shaw.ca
Jezebel - 05 Aug 2006 11:09 GMT
> Sub Excel_to_Word()
>   Dim appWord As Word.Application
   Dim pDoc as Word.Document

>    Set appWord = New Word.Application
   set pDoc = appWord.Documents.Add(Template:="PDC_MCC_IR.dot")

>    appWord.Visible = True
>
>    Range("b4:c10").Copy

    pDoc.Range.Paste
You'll need to be smarter than this if you want to specify where the pasted
stuff ends up
Other options include set Document Properties and using DocProperty fields
(covered in Word Help).

   pDoc.SaveAs FileName:="....."
   pDoc.Close
   appWord.Quit

>    Application.CutCopyMode = False
>
[quoted text clipped - 23 lines]
>
> Thanks Garry gtpighin@shaw.ca
Garr - 06 Aug 2006 03:15 GMT
Hi Jezebel
Thanks for the help, but when I run the macro it opened the
PDC_MCC_IR.dot template and then seemed to close the template and then
pasted the information from the cells into Document1. I left out the
part of the code that does the save close and quit
pDoc.SaveAs FileName:="....."
pDoc.Close
appWord.Quit
My template has two pic's an IR scan and a graph on the bottom of it.
Thanks Garry

> > Sub Excel_to_Word()
> >   Dim appWord As Word.Application
[quoted text clipped - 44 lines]
> >
> > Thanks Garry gtpighin@shaw.ca
Jezebel - 06 Aug 2006 03:40 GMT
Not sure what you mean by "seems to" here.

The Documents.Add instruction creates a new document based on the template
you specify. Beyond that I can't help unless you can explain exactly what
happens. Have you tried stepping through the code using F8?

> Hi Jezebel
> Thanks for the help, but when I run the macro it opened the
[quoted text clipped - 57 lines]
>> >
>> > Thanks Garry gtpighin@shaw.ca
Garr - 06 Aug 2006 04:42 GMT
Stepping through the code I get to   Range("b4:c10").Copy   and my
template is open at this time but when the code goes to
pDoc.Range.Paste  it pastes the cells in an empty document at the top.

> Not sure what you mean by "seems to" here.
>
[quoted text clipped - 63 lines]
> >> >
> >> > Thanks Garry gtpighin@shaw.ca
Garr - 06 Aug 2006 04:48 GMT
Jezebel I ment to add this in the last post
The program closes the template just before it opens and pastes the
cells information in the new document

> Stepping through the code I get to   Range("b4:c10").Copy   and my
> template is open at this time but when the code goes to
[quoted text clipped - 67 lines]
> > >> >
> > >> > Thanks Garry gtpighin@shaw.ca
Jezebel - 06 Aug 2006 06:15 GMT
There's nothing in the macro code to close the newly-opened document; so you
must have some other code running. Does your template have an AutoNew macro?

> Jezebel I ment to add this in the last post
> The program closes the template just before it opens and pastes the
[quoted text clipped - 79 lines]
>> > >> >
>> > >> > Thanks Garry gtpighin@shaw.ca
Garr - 06 Aug 2006 07:29 GMT
Yes it does. Here are the macro that are attached to the template.
Sub AutoNew()

'
' AutoNew Macro
' Macro created 18/02/2006 by G
'

  ' If the active document does not contain
  ' a form field, exit this routine.
  If ActiveDocument.FormFields.Count = 0 Then
     End
  End If

  ' Create variables.
  Dim sAppName As String
  Dim sSection As String
  Dim sKey As String
  Dim sFieldName As String
  Dim lRegValue As Long
  Dim lFormValue As Long
  Dim iDefault As Integer
  sAppName = "Word 2003"
  sSection = "Reports"
  sKey = "Current Report"

  ' The default starting number.
  iDefault = 101

  ' If the specified form field doesn't exist,
  ' an error will occur.
  On Error GoTo Errhandler

  ' Replace the following example Form Field bookmark name,
  ' "Report", with the name of your form field.
  Set fField = ActiveDocument.FormFields("Report")

  ' Get stored registry value, if any.
  lRegValue = GetSetting(sAppName, sSection, sKey, iDefault)

  ' If the result is zero, set to default value.
  If lRegValue = 100 Then lRegValue = iDefault

  ' Set form field result to stored value.
  fField.Result = CStr(lRegValue)

  ' Increment and update Report_Number.
  SaveSetting sAppName, sSection, sKey, lRegValue + 1

Errhandler:

  If Err <> 0 Then
     MsgBox Err.Description
  End If

End Sub
Sub Excel_2_Word()
'
' Excel_2_Word Macro
' Macro recorded 6/28/2006 by GPIGHIN
'
   Selection.PasteExcelTable False, False, False
End Sub
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/28/2006 by GPIGHIN
'
   Selection.PasteExcelTable False, False, False
   Selection.EscapeKey
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/28/2006 by GPIGHIN
'
   Selection.PasteExcelTable False, False, False
End Sub
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 6/28/2006 by GPIGHIN
'
   Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject,
Placement _
       :=wdInLine, DisplayAsIcon:=False
End Sub

What should I do with this?

> There's nothing in the macro code to close the newly-opened document; so you
> must have some other code running. Does your template have an AutoNew macro?
[quoted text clipped - 82 lines]
> >> > >> >
> >> > >> > Thanks Garry gtpighin@shaw.ca
Jezebel - 06 Aug 2006 07:43 GMT
Well, there's your problem.

> Yes it does. Here are the macro that are attached to the template.
> Sub AutoNew()
[quoted text clipped - 179 lines]
>> >> > >> >
>> >> > >> > Thanks Garry gtpighin@shaw.ca
Garr - 06 Aug 2006 09:44 GMT
Jezebel
I have removed all the macro's in word and only have the one macro in
excel. I have looked for something that might be running in the
background and can't find anything. Have you got any other sujestions
or should I try finding another way to do what I want?
Garry

> Well, there's your problem.
>
[quoted text clipped - 181 lines]
> >> >> > >> >
> >> >> > >> > Thanks Garry gtpighin@shaw.ca
Jezebel - 06 Aug 2006 10:11 GMT
I'd track down the problem, if I were you. Otherwise any other approach will
be suspect also. You shouldn't have any trouble creating a document based on
a template from your Excel macro: if that's not working there are other
things going on (and the appalling AutoNew macro suggests that someone's
being doing some seriously bad coding on your system). Have you checked for
add-ins?

> Jezebel
> I have removed all the macro's in word and only have the one macro in
[quoted text clipped - 200 lines]
>> >> >> > >> >
>> >> >> > >> > Thanks Garry gtpighin@shaw.ca
Garr - 06 Aug 2006 11:28 GMT
Thank you I appreciate all the help.

> I'd track down the problem, if I were you. Otherwise any other approach will
> be suspect also. You shouldn't have any trouble creating a document based on
[quoted text clipped - 207 lines]
> >> >> >> > >> >
> >> >> >> > >> > Thanks Garry gtpighin@shaw.ca
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.