Option Explicit

[samanco]

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").Copy '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

 

[Jim Thomlinson]

Does the code compile???

 

[Per Jessen]

Hi

I think you need a dot before ActiveDocument.SaveAs...

Regards,
Per

 

[samanco345]

Hi

I think you need a dot before ActiveDocument.SaveAs...

Regards,
Per

<[email protected]> skrev i meddelelsennews:[email protected]...

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 ??

 

[samanco345]

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").Copy '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

 

[Dave Peterson]

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").Copy '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").Copy '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.