PDA

View Full Version : Solved: appending to excel from word



island17
11-12-2004, 01:06 PM
Hello all

I have a word macro that displays a form to gather certain information from the user. This data is then formatted and reproduced in a word doc. Now we would also like to keep track of all that data, but in excel as well. Currently I write this information out to a CSV file. My questions is; from a word macro can I append to a specfic excel workbook;worksheet.

Thanks

Jacob Hilderbrand
11-12-2004, 03:57 PM
Try this:

'Requires a referece to the Microsoft Excel Object Library
'Tools | References

Option Explicit

Sub ExcelMacro()

Dim ObjExcel As Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim Path As String
Dim FName As String

Path = ThisDocument.Path
FName = "Book1.xls"
Set ObjExcel = New Excel.Application
'ObjExcel.Visible = True 'Add this line to make the Excel app visible
Set Wkb = ObjExcel.Workbooks.Open(FileName:=Path & "\" & FName)
Set WS = Wkb.Sheets("Sheet1")
WS.Range("A1").Value = "This"
WS.Range("A2").Value = "Is"
WS.Range("A3").Value = "A"
WS.Range("A4").Value = "Message"
WS.Range("A5").Value = "From"
WS.Range("A6").Value = "Word"
Wkb.Close True
ObjExcel.Quit

Set ObjExcel = Nothing
Set Wkb = Nothing
Set WS = Nothing

End Sub

island17
11-17-2004, 04:07 PM
Jacob
Thanks, worked Like a dream, and got me off on the right foot. I looked up the insert command, and now I can use your code to append to the file.
Thanks again