VBA Execute Macros for Word Document in Excel
excel vba word document
excel vba open word document
excel vba edit word document
excel vba create word document from template
generate word document from excel macro
word vba run macro in another document
I have an excel calculation which contains information for a Word Document. What I want is to open the word document and save it as an pdf automatically - with a macro in Excel.
I already tried the following:
Set WordApp = CreateObject("Word.Application") With WordApp.Application .Visible = True .Documents.Open (LocationTemplate) .ExportAsFixedFormat OutputFileName:= _ OfferPath, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False ChangeFileOpenDirectory _ DestinationPath .Quit End With
What is the mistake? Looking forward to your support.
it seems that youre not selecting the open document
try something like this
Set WordApp = CreateObject("Word.Application") With WordApp.Application .Visible = True .Documents.Open (LocationTemplate) .Activate ActiveDocument.ExportAsFixedFormat .ExportAsFixedFormat OutputFileName:= _ OfferPath, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False ChangeFileOpenDirectory _ DestinationPath .Quit End With
Controlling Word from Excel using VBA, Click OK – the Macro Recorder is now running. Open a new Word Document – File -> New -> Blank Document. Stop the Macro from recording – Now copy and paste the code from the Word VBA Editor into the Excel VBA Editor. The Word VBA code started with Documents.Add, all we have to do is add our application variable to the front of that line of code. Now becomes WordApp.Documents.Add . . .
Runtime Error 438: "Object doesn't support this property or method"
Your problem stems from the fact that in the
With block you are referring to
WordApp.Application (which in itself is redundant and could be reduced to
WordApp as it already represents a
Word.Application object) and thus with the line
.ExportAsFixedFormat [...] you are essentially doing:
This method doesn't exist on the
Application object. (Now read the error description again - notice anything?)
If you instead of late-binding the
Word.Application object via
CreateObject() set a reference to the Word Object Model (Menu: Extras - References) you can do things like:
Dim wordApp As Word.Application Set wordApp = New Word.Application With wordApp.Documents.Open LocationTemplate .ExportAsFixedFormat [...] End With
Which provides you with the (much needed) intellisense, as well as compile-time errors instead of runtime errors when you try to call wrong methods.
Application.Run method (Word), hi, i googled a way to open a word doc via Excel VBA 'Open an existing Word Document from Excel Dim objWord As Object Set objWord = If you specify the document name, your code can only run macros in documents related to the current context—not just any macro in any document. Although Visual Basic code can call a macro directly (without using the Run method), this method is useful when the macro name is stored in a variable. (For more information, see the example for this
The mistake is that you execute
.ExportAsFixedFormat on the Word.Application object. This method is valid for a Word document. Your code should be more like what follows.
Note that I've added the variable declarations for
WordDoc and also code to release these objects.
Dim WordApp as Object Dim WordDoc as Object Set WordApp = CreateObject("Word.Application") With WordApp.Application .Visible = True Set WordDoc = .Documents.Open (LocationTemplate) WordDoc.ExportAsFixedFormat OutputFileName:= _ OfferPath, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False .Quit 0 'To not save changes to the document, just close it Set WordDoc = Nothing End With Set WordApp = Nothing
open word doc via vba, then execute macro for the word document , add this to your excel module and run. Don't forget to I just need to open an Excel File from word but not sure of the syntax. Rib. I know a little Public wb1 As Workbook Public dt1 As Document Sub openword() Dim wpath, epath As String 'where the word document will be opened and where the excel sheet will be saved Dim wordapp As Object 'preparing to open word Set wb1 = ThisWorkbook While wb1.Sheets.Count <> 1 wb1.Sheets(2).Delete Wend wpath = "C:\users\GPerry\Desktop\Projects and Work\document.docm" Set wordapp = CreateObject("Word.Application") 'Set wordapp = CreateObject(Shell("C:\Program Files (x86)\Microsoft Office\Office14\WINWORD
How do I call a word macro from excel?, In this article, we will create a macro to open word document. To open a word document, we will specify full path of the document in the text box on main. VBA-Excel: Change Font, Color, Weight of Table Data in the Word document VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order Send Mail With Link to a Workbook, From MS Outlook using Excel.
Launch Word file from Excel VBA, The number of people using Word & Excel are quite high, however, they are not well versed when it comes to opening word file from Excel workbook. This. Both macros work independently - BUT when I run the macro from Excel and open the word document (a letter) the Word Macro will not run automatically - when I do run the word macro manually it then doesnt find the template file (still open in the background). The EXCEL macro: Sub automateword() Dim strFolder As String strFolder = Range("B2").Value
How to Launch Word File Using VBA in Microsoft Excel, Keep in touch and stay productive with Teams and Microsoft 365, even when you're You may also want to run macros automatically when Excel starts. the VBA Project folder for your workbook, then double-click the ThisWorkbook module. Enable macros for one time when the Security Warning appears. Click the File tab. Click Options . Click Trust Center , and then click Trust Center Settings . In the Trust Center , click Macro Settings . Make the selections that you want. Click OK .
- Please tell us a little bit more about your problem: Do you get an error? Does it work in unexpected ways? (We can't check your code, as long as you don't provide us with a MCVE - as your snippet simply doesn't compile for me due to lacking declarations.)
- After the Word Document opened I got Run time error 438. The yellow markings start at .ExportAsFixedFormat.
- Can you please edit that information into your question - I think it is crucial to solving your problem. Also, have a look at my answer and see if it helps you.
- Unfortunately I did not find a final solution with the help of your answers. Nevertheless I am still trying to fix. A second way is that the Word includes an
AutoOpenmacro and can thereby save the file as an PDF. Therefore I need the Path of the excel calculation. Is there a possiblity to transfer the path (probably as a variable) from excel to word?
- You won't succeed with
AutoOpeneither. You first need to fix the problem in your code. Your
.ExportAsFixedFormatmethod refers to the wrong object. As mentioned in my answer and in Cindy Meister's answer as well.
- Did you try to compile/run your code? Your suggestion will exhibit the same behavior as OP's code snippet (I.e. Error 438).
.ExportAsFixedFormatdoes not exist on the
- yes, ive tryied and worked. in the wordapp doenst exist, but in the activedocument yes... and thats why u activate the document.
- I'm referring to the line
ActiveDocument.ExportAsFixedFormat(which should work), followed by
.ExportAsFixedFormat OutputFileName:= _[...] which I'm quite sure won't work. (As it's calling
- Thank you for your reply. I changed
Word.Applicationinstead of an object. Nevertheless, I get an compile error, because
.ExportAsFixedFormatdoes not exist. Is there a possiblity to add it? Generally my question is not only connected to
.ExportAsFixedFormat. I want access to the word document with the macros in Excel.
- @LuisCurado Now I'm getting what you're trying to achieve. The method
ExportAsFixedFormatis available on every object of the type
Word.Document. You don't need
ActiveDocumentfor this. (And thus there is no need to first call
.Activate). If you set a reference to the document you're opening like
Set doc = .Documents.Open(LocationTemplate), you can then use