Help with runtime error 438 - caused by using Office 2010 macro in Office 2007?

Question

Hi,

I am trying to run a visual basic macro in excel 2007 that was created in excel 2010.

When working, the macro creates a word document, populates it with data in the spreadsheet then converts to a pdf before emailing.

I'm getting a 438 error at the point where it creates the word document.  I believe this is down to the different version of word I am using (having made some changes to the references in excel (to version 12.0).

The debugger has highlighted the following code as the issue:

objword.ActiveDocument.SaveAs2 Filename:=name & "_" & BookingID & ".docx", FileFormat:= _

wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _

:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _

:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _

SaveAsAOCELetter:=False, CompatibilityMode:=14

Running Office 2007 on a Windows 8.1 device.  Both 32-bit.

The code works perfectly on Office 2010 on a Windows 7 machine, both 64-bit.

I don't know how to code at all so any advice welcomed.

Thank you.

Answer 1

ADDENDUM...

After running windows update, this solved the problem.

Thank you again.

Answer 2

If you want full compatibility on the method use SaveAs, do not use SaveAs2. Also eliminate all of the properties other than Filename and FileFormat because for what you have shown, you are using the default settings anyway. And as Greg has pointed out, eliminate the Compatibility property.

With those changes your command is compatible on Office 2007, 2010, 2013 and even Office 2011 on the Mac.

Answer 3

Niyamath Khan,  If you are going to take it upon yourself to mark other people's questions answered then you you should make the effort to mark all applicable responses likewise.  The cause of the run time error as I pointed out is because SaveAs2 is not a method in Word2007!

Answer 4

Thank you, Greg, Richard.

That worked!  I now have a word document created.

However, that has thrown up another problem, specifically taking that word document and creating a pdf.

Might I ask for your advice again?

I am getting the following error message:

Run-time error '-2147467259 (80004005)':

The export failed because the feature is not installed.

From a search, I think the issue is down to my version of office 2007 not having a built-in function to save as a pdf.

The debugger points to the following code:

objword.ActiveDocument.ExportAsFixedFormat OutputFileName:=path + "" & name & "_" & BookingID & ".pdf", _

ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _

wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _

Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _

CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _

BitmapMissingFonts:=True, UseISO19005_1:=False

Any advice would be most welcome, and thank you again for your very helpful responses earlier.

Mark

Answer 5

SaveAs2 is not a method available in Word2007.  CompatibilityMode is not an argument of SaveAs.