J
Jim
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
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