transfer data from word userform to excel

J

Jade

Hello everyone,

Does anyone know the proper method to transfer data directly from a
word userform to an excel spreadsheet.


I have the code below but am having no success...any feedback to
point
me in the right direction as to what I'm doing wrong or how this
could
be achieved would be great. Thanks.


Sub Do()


On Error Resume Next
Set Xl = GetObject("Excel.Application")
Set wb = Xl.Workbooks("Activities.xls")
If Err <> 0 Then Set wb = Xl.Workbooks.Open("c:\My Documents
\Activities.xls"): Err.Clear
wb.Worksheets("Sheet3").Range("A" &
Rows.Count).End(xlUp).Offset(1).Value = UserForm2.txtBy.Value
wb.Worksheets("Sheet3").Range("A" &
Rows.Count).End(xlUp).Offset(0, 1).Value = UserForm2.cmbVote.Value


Xl.Visible = False: AppActivate Xl.Caption
wb.ActiveSheet.Save
Set wb = Nothing: Set Xl = Nothing


End Sub
 
T

Tom Ogilvy

It looks like you are using late binding, so the constants you are using
would be unknow and interpreted to have a value of zero:

Sub Do()


On Error Resume Next
Set Xl = GetObject("Excel.Application")
Set wb = Xl.Workbooks("Activities.xls")
If Err <> 0 Then Set wb = Xl.Workbooks.Open("c:\My Documents
\Activities.xls"): Err.Clear
wb.Worksheets("Sheet3").Range("A" &
Rows.Count).End(-4162).Offset(1).Value = UserForm2.txtBy.Value
wb.Worksheets("Sheet3").Range("A" &
Rows.Count).End(-4162).Offset(0, 1).Value = UserForm2.cmbVote.Value


Xl.Visible = False: AppActivate Xl.Caption
wb.Save '<=== modified
Set wb = Nothing: Set Xl = Nothing


End Sub


If you want to close Excel, you would need to work in an xl.quit before you
clear the variable.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top