How can I populate an Excel Field with data from a Word document textbox?

[Joe Patrick]

Hi! This is the code I'm trying. I need, from Word, to open an Excel file and fill in various fields of the workbook with data from the Word document.

Can anyone help me with this?

Sub CreateTop25Grid()
Dim objXLApplication As Excel.Application
Dim objXLWorkbook As Excel.Workbook
Set objXLApplication = CreateObject("Excel.Application")
Set objXLWorkbook = objXLApplication.Workbooks.Open("\\Fileserver\commonh\GROUP\GROUP PROCEDURES\Top 25 Contact Template.xls")
objXLApplication.Visible = True 'I'll remove this later
With ActiveWorkbook
.Range("B2").Value = ActiveDocument.TextBox1.Value & " " & Format(Date, "yyyy")
.Range("B3").Value = ActiveDocument.TextBox2.Value
.Range("B4").Value = ActiveDocument.TextBox5.Value & "-" & ActiveDocument.TextBox6.Value
End With
objXLWorkbook.SaveAs Filename:= _
        "\\Fileserver\commonh\GROUP\Top 25 Grids\" & ActiveDocument.TextBox1.Value & ".xls",  FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
 
objXLWorkbook.Close
Set objXLWorkbook = Nothing
objXLApplication.Quit
Set objXLApplication = Nothing
End Sub

I get runtime error '438' - object does not support this property or method.

I'm sure there's a problem with having 'activeworkbook' and 'activedocument' at the same time but I don't know how to get around that.

Any help would be greatly appreciated!

 

[AlphaFrog]

I think the .Range references in your code expect a sheet reference in the With statment.

Change this...

With ActiveWorkbook

To something like this...

With objXLWorkbook.Sheets(1)

Where Sheets(1) is the first sheet in the workbook. You could reference a different sheet number or reference a sheet by name e.g. Sheets("Sheet1")

 

[Joe Patrick]

That works perfectly! Thank you so much!

 

[Joe Patrick]

OK, now I need to get the data from a word doc textbox into an excel file that is already open and I just can't seem to make it work.

Also, I need to be able to import the data back from the open excel workbook to the open word doc.

Can anyone help, please?