👀 Views: 39 💬 Answers: 1 📅 Created: 2025-06-11

vba excel word

I'm upgrading from an older version and I'm trying to automate the creation of a Word document from an Excel VBA script, where the content of the document needs to dynamically pull data from various Excel cells. I need to insert text, tables, and some formatting based on user input. However, I'm working with issues with inserting tables and formatting them correctly. Here’s a basic structure of what I’ve tried: ```vba Sub CreateWordDoc() Dim wdApp As Object Dim wdDoc As Object Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Create a new instance of Word Set wdApp = CreateObject("Word.Application") wdApp.Visible = True ' Create a new document Set wdDoc = wdApp.Documents.Add ' Inserting Title wdDoc.Content.Text = ws.Range("A1").Value & vbCrLf wdDoc.Content.Style = "Heading 1" ' Inserting a table Dim tbl As Object Set tbl = wdDoc.Tables.Add(wdDoc.Bookmarks.Item("EndOfDoc").Range, 2, 2) tbl.Cell(1, 1).Range.Text = ws.Range("B1").Value tbl.Cell(1, 2).Range.Text = ws.Range("C1").Value ' Formatting the table tbl.Borders.Enable = True tbl.Columns(1).Width = 100 tbl.Columns(2).Width = 100 ' More content insertion... End Sub ``` When I run this code, the Word application opens, but the table only partially populates, and I get an behavior message saying "Run-time behavior '5941': The requested member of the collection does not exist." This happens on the line where I try to add the table using `wdDoc.Bookmarks.Item("EndOfDoc").Range`. I attempted to replace `EndOfDoc` with other bookmarks but encountered similar issues. I've also checked if my Excel ranges are correctly populated before the VBA script runs. I need help figuring out how to properly insert the table and apply formatting to it. What am I missing here?