Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Explicit
I have a macro used to copy a range from a spreadshhet into a word
document and formats the table, etc. I have been trying to be consistent and using Option Explicit across the whole application. I think have decaled all the variables in this macro but it only works as far as the saveas statement, then the error capture kicks in and quits the macro. However if I remove the Option Explicit declaration then the macro completes fully including the saveAs element as required. If I step through the macro using f8 it does not report a variable undeclared, if I remove the error traps then still no debug but ends without the filesave section completing? Any ideas ? Option Explicit Sub autoword() Dim wdApp As Object ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If On Error GoTo 0 With wdApp ..Documents.Add ..Visible = True End With With wdApp.Selection ..Paste ..PageSetup.Orientation = wdOrientLandscape ..Tables(1).Select ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Font.Size = 10 ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Style = "Table Simple 1" End With On Error GoTo Quit ' this is where things seen to go wrong With wdApp 'change filename 'ChangeFileOpenDirectory "C:\temp\" 'remove comment tag and edit path as required ActiveDocument.SaveAs Filename:="METstatsrep.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End With Quit: Set wdApp = Nothing End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Explicit
Does the code compile???
-- HTH... Jim Thomlinson " wrote: I have a macro used to copy a range from a spreadshhet into a word document and formats the table, etc. I have been trying to be consistent and using Option Explicit across the whole application. I think have decaled all the variables in this macro but it only works as far as the saveas statement, then the error capture kicks in and quits the macro. However if I remove the Option Explicit declaration then the macro completes fully including the saveAs element as required. If I step through the macro using f8 it does not report a variable undeclared, if I remove the error traps then still no debug but ends without the filesave section completing? Any ideas ? Option Explicit Sub autoword() Dim wdApp As Object ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If On Error GoTo 0 With wdApp ..Documents.Add ..Visible = True End With With wdApp.Selection ..Paste ..PageSetup.Orientation = wdOrientLandscape ..Tables(1).Select ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Font.Size = 10 ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Tables(1).AutoFitBehavior (wdAutoFitWindow) ..Style = "Table Simple 1" End With On Error GoTo Quit ' this is where things seen to go wrong With wdApp 'change filename 'ChangeFileOpenDirectory "C:\temp\" 'remove comment tag and edit path as required ActiveDocument.SaveAs Filename:="METstatsrep.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End With Quit: Set wdApp = Nothing End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Explicit
Hi
I think you need a dot before ActiveDocument.SaveAs... Regards, Per skrev i meddelelsen ... I have a macro used to copy a range from a spreadshhet into a word document and formats the table, etc. I have been trying to be consistent and using Option Explicit across the whole application. I think have decaled all the variables in this macro but it only works as far as the saveas statement, then the error capture kicks in and quits the macro. However if I remove the Option Explicit declaration then the macro completes fully including the saveAs element as required. If I step through the macro using f8 it does not report a variable undeclared, if I remove the error traps then still no debug but ends without the filesave section completing? Any ideas ? Option Explicit Sub autoword() Dim wdApp As Object ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If On Error GoTo 0 With wdApp .Documents.Add .Visible = True End With With wdApp.Selection .Paste .PageSetup.Orientation = wdOrientLandscape .Tables(1).Select .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Font.Size = 10 .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Style = "Table Simple 1" End With On Error GoTo Quit ' this is where things seen to go wrong With wdApp 'change filename 'ChangeFileOpenDirectory "C:\temp\" 'remove comment tag and edit path as required ActiveDocument.SaveAs Filename:="METstatsrep.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End With Quit: Set wdApp = Nothing End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Explicit
On Apr 30, 8:56*pm, "Per Jessen" wrote:
Hi I think you need a dot before ActiveDocument.SaveAs... Regards, Per skrev i ... Just one little Dot for hours of trying, thank you very much. Any idea why this still compiled and worked without a Dot when Option Explicit was removed ?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Explicit
On Apr 30, 8:26*pm, wrote:
I have a macro used to copy a range from a spreadshhet into a word document and formats the table, etc. I have been trying to be consistent and using Option Explicit across the whole application. I think have decaled all the variables in this macro but it only works as far as the saveas statement, then the error capture kicks in and quits the macro. However if I remove the Option Explicit declaration then the macro completes fully including the saveAs element as required. If I step through the macro using f8 it does not report a variable undeclared, if I remove the error traps then still no debug but ends without the filesave section completing? * Any ideas ? Option Explicit Sub autoword() Dim wdApp As Object ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If On Error GoTo 0 With wdApp .Documents.Add .Visible = True End With With wdApp.Selection .Paste .PageSetup.Orientation = wdOrientLandscape .Tables(1).Select .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Font.Size = 10 .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Style = "Table Simple 1" End With On Error GoTo Quit ' this is where things seen to go wrong With wdApp 'change filename 'ChangeFileOpenDirectory "C:\temp\" 'remove comment tag and edit path as required * * ActiveDocument.SaveAs Filename:="METstatsrep.doc", FileFormat:= _ * * * * wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ * * * * True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ * * * * False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ * * * * SaveAsAOCELetter:=False * End With Quit: Set wdApp = Nothing End Sub Yes I have just checked again and this compiles |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Explicit
What is the error description that you see when it fails?
Since you aren't getting any errors for the MSWord constants: wdOrientLandscape wdAutoFitWindow wdFormatDocument That must mean that you have a reference set to "Microsoft Word xx.0 Object Library". So I was explicit in my declarations. Option Explicit Sub autoword() Dim wdApp As Word.Application Dim wdDoc As Word.Document ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") On Error GoTo 0 If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If With wdApp Set wdDoc = .Documents.Add .Visible = True End With With wdApp.Selection .Paste .PageSetup.Orientation = wdOrientLandscape .Tables(1).Select .Tables(1).AutoFitBehavior wdAutoFitWindow .Tables(1).AutoFitBehavior wdAutoFitWindow .Font.Size = 10 .Tables(1).AutoFitBehavior wdAutoFitWindow .Tables(1).AutoFitBehavior wdAutoFitWindow .Style = "Table Simple 1" End With wdDoc.SaveAs Filename:="c:\temp\METstatsrep.doc", _ FileFormat:=wdFormatDocument, _ LockComments:=False, _ Password:="", _ AddToRecentFiles:=True, _ WritePassword:="", _ ReadOnlyRecommended:=False, _ EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, _ SaveFormsData:=False, _ SaveAsAOCELetter:=False Quit: Set wdDoc = Nothing Set wdApp = Nothing End Sub This is called early binding -- because of the way both wdApp and wdDoc were declared (and the reference that was set). The alternative is to use late binding--without the reference. Option Explicit Sub autoword() Dim wdApp As Object Dim wdDoc As Object ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") On Error GoTo 0 If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If With wdApp Set wdDoc = .Documents.Add .Visible = True End With With wdApp.Selection .Paste .PageSetup.Orientation = 1 'wdOrientLandscape .Tables(1).Select .Tables(1).AutoFitBehavior 2 'wdAutoFitWindow .Tables(1).AutoFitBehavior 2 'wdAutoFitWindow .Font.Size = 10 .Tables(1).AutoFitBehavior 2 'wdAutoFitWindow .Tables(1).AutoFitBehavior 2 'wdAutoFitWindow .Style = "Table Simple 1" End With 'wdFormatDocument = 0 wdDoc.SaveAs Filename:="c:\temp\METstatsrep.doc", _ FileFormat:=0, _ LockComments:=False, _ Password:="", _ AddToRecentFiles:=True, _ WritePassword:="", _ ReadOnlyRecommended:=False, _ EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, _ SaveFormsData:=False, _ SaveAsAOCELetter:=False Quit: Set wdDoc = Nothing Set wdApp = Nothing End Sub Notice that since excel doesn't know about those MSWord constants, I had to provide the "translation". I went into MSWord's VBE, showed the immediate window (using view|immediate window) and then I typed things like: ?wdAutoFitWindow 2 ?wdFormatDocument 0 ========= But none of this really helps you debug your problem. I couldn't get it to fail and I didn't see any error messages. wrote: I have a macro used to copy a range from a spreadshhet into a word document and formats the table, etc. I have been trying to be consistent and using Option Explicit across the whole application. I think have decaled all the variables in this macro but it only works as far as the saveas statement, then the error capture kicks in and quits the macro. However if I remove the Option Explicit declaration then the macro completes fully including the saveAs element as required. If I step through the macro using f8 it does not report a variable undeclared, if I remove the error traps then still no debug but ends without the filesave section completing? Any ideas ? Option Explicit Sub autoword() Dim wdApp As Object ThisWorkbook.Sheets("METstats").Range("b6:h123").C opy 'Change me On Error Resume Next Set wdApp = GetObject(, "Word.Application") If wdApp Is Nothing Then Set wdApp = GetObject("", "Word.Application") End If On Error GoTo 0 With wdApp .Documents.Add .Visible = True End With With wdApp.Selection .Paste .PageSetup.Orientation = wdOrientLandscape .Tables(1).Select .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Font.Size = 10 .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Tables(1).AutoFitBehavior (wdAutoFitWindow) .Style = "Table Simple 1" End With On Error GoTo Quit ' this is where things seen to go wrong With wdApp 'change filename 'ChangeFileOpenDirectory "C:\temp\" 'remove comment tag and edit path as required ActiveDocument.SaveAs Filename:="METstatsrep.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End With Quit: Set wdApp = Nothing End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
preserve formatting option in pivot table option dialog box | Excel Discussion (Misc queries) | |||
how to create a chart from the source which does not have explicit numbers | Charts and Charting in Excel | |||
option explicit | Excel Discussion (Misc queries) | |||
keep source formatting is not an option in paste option button | Excel Discussion (Misc queries) | |||
Reformat IF(ISERROR(....) : if 1st option returns empty, look at 2nd option. | Excel Worksheet Functions |