Re: excel copy cells to word template by Jezebel
Jezebel
Sun Aug 06 04:11:36 CDT 2006
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?
"Garr" <gtpighin@shaw.ca> wrote in message
news:1154853879.868716.134690@b28g2000cwb.googlegroups.com...
> 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
>
>
> Jezebel wrote:
>> Well, there's your problem.
>>
>>
>>
>> "Garr" <gtpighin@shaw.ca> wrote in message
>> news:1154845777.980161.65490@m79g2000cwm.googlegroups.com...
>> > 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?
>> >
>> >
>> > Jezebel wrote:
>> >> 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?
>> >>
>> >>
>> >>
>> >> "Garr" <gtpighin@shaw.ca> wrote in message
>> >> news:1154836085.618214.153930@i42g2000cwa.googlegroups.com...
>> >> > 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
>> >> >
>> >> > Garr wrote:
>> >> >> 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.
>> >> >>
>> >> >> Jezebel wrote:
>> >> >> > 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?
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Garr" <gtpighin@shaw.ca> wrote in message
>> >> >> > news:1154830546.373307.187650@m73g2000cwd.googlegroups.com...
>> >> >> > > 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