I have written a macro in Excel which copies data from a work sheet then
automatically opens a specific file in word.

In the word file I have an AutoOpen macro which pastes the data then brings
up the save as dialog box.

I only want the macro in the word document to run once so once it has been
saved i want to delete the macros of permanently disable them. Is this
possible or is there a better way of pasting the excel data into a new sheet
say using a template?

This is the code in my Excel file:

Sheets("quotation").select
ActiveSheet.Unprotect
Range("A16:e321").select
Selection.Sort Key1:=Range("d16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True


Cells.Find(What:="5000000", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.select
ActiveCell.Offset(-1, -2).select
Range(Selection, Cells(1)).select
Selection.Copy
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number < 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc =
wdApp.Documents.Open(Worksheets("constants").Range ("A85").Value)

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

End Sub

This is the code in my word file:

Sub autoopen()
'
' AutoOpen Macro
' Macro recorded 15/06/2007 by James Cowell
'
Selection.Paste
Dialogs(wdDialogFileSaveAs).Show
End Sub

Include the code that is currently in the Word file in the Excel macro,
allowing for the change. Although your code is maybe not the most efficient,
I assume it works.

Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc =
wdApp.Documents.Open(Worksheets("constants").Range ("A85").Value)

'Air-code, so check
wdApp.Selection.Paste
wdApp.Dialogs(wdDialogFileSaveAs).Show
'/Air-code, so check

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

End Sub

NickHK

"Jim" wrote in message
...
I have written a macro in Excel which copies data from a work sheet then
automatically opens a specific file in word.

In the word file I have an AutoOpen macro which pastes the data then
brings
up the save as dialog box.

I only want the macro in the word document to run once so once it has been
saved i want to delete the macros of permanently disable them. Is this
possible or is there a better way of pasting the excel data into a new
sheet
say using a template?

This is the code in my Excel file:

Sheets("quotation").select
ActiveSheet.Unprotect
Range("A16:e321").select
Selection.Sort Key1:=Range("d16"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Application.ScreenUpdating = True


Cells.Find(What:="5000000", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.select
ActiveCell.Offset(-1, -2).select
Range(Selection, Cells(1)).select
Selection.Copy
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number < 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc =
wdApp.Documents.Open(Worksheets("constants").Range ("A85").Value)

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

End Sub

This is the code in my word file:

Sub autoopen()
'
' AutoOpen Macro
' Macro recorded 15/06/2007 by James Cowell
'
Selection.Paste
Dialogs(wdDialogFileSaveAs).Show
End Sub

Hi Jim,

If your word document doesn't contain anything too fancy, you could disable
alerts and make it save as an RTF (from Word VBA) - that would save the
content without the VBA, and still retain formatting etc... so, using 'fname'
as save filename/path:

ActiveDocument.SaveAs FileName:=fname, FileFormat:=wdFormatRTF

....just an idea.

Many thanks for your help.

I've only just started using VBA so i've pieced together my macro from other
posts and trial and error so i'm not surprised it's not very efficient, but
it does work which surprised me even more.

"NickHK" wrote:

Include the code that is currently in the Word file in the Excel macro,
allowing for the change. Although your code is maybe not the most efficient,
I assume it works.

Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc =
wdApp.Documents.Open(Worksheets("constants").Range ("A85").Value)

'Air-code, so check
wdApp.Selection.Paste
wdApp.Dialogs(wdDialogFileSaveAs).Show
'/Air-code, so check

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

End Sub

NickHK

"Jim" wrote in message
...
I have written a macro in Excel which copies data from a work sheet then
automatically opens a specific file in word.

In the word file I have an AutoOpen macro which pastes the data then
brings
up the save as dialog box.

I only want the macro in the word document to run once so once it has been
saved i want to delete the macros of permanently disable them. Is this
possible or is there a better way of pasting the excel data into a new
sheet
say using a template?

This is the code in my Excel file:

Sheets("quotation").select
ActiveSheet.Unprotect
Range("A16:e321").select
Selection.Sort Key1:=Range("d16"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Application.ScreenUpdating = True


Cells.Find(What:="5000000", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.select
ActiveCell.Offset(-1, -2).select
Range(Selection, Cells(1)).select
Selection.Copy
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number < 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc =
wdApp.Documents.Open(Worksheets("constants").Range ("A85").Value)

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

End Sub

This is the code in my word file:

Sub autoopen()
'
' AutoOpen Macro
' Macro recorded 15/06/2007 by James Cowell
'
Selection.Paste
Dialogs(wdDialogFileSaveAs).Show
End Sub