Word Form to Excel

[nickirvine]

Hi,

I've had help from people on here in the past and you've been super. I'm trying to kind of adapt two codes now.

I have created a little form thing you type details into a form and it inserts them into bookmarks on letter on word. You press print it clears the bookmarks and the form then you can just do another one. Works super.

I know want the details you enter into the bookmarks to be saved onto an Excel spreadsheet and would really appreciate any help.

I've had a go by combining some code but i cant get it to work.

Its the command button1 sub i need changing. thats the print and do another button.

I have this, any pointers help really appreciated..


Option Explicit
Dim bname As String
Private Sub wb(bname, ByVal inhalt As String)

Dim r As Range
Set r = ActiveDocument.Bookmarks(bname).Range
r.Text = inhalt
ActiveDocument.Bookmarks.Add bname, r

End Sub

Private Sub ComboBox1_Change()
End Sub
Private Sub UserForm_Initialize()
With title
.AddItem "Mrs"
.AddItem "Mr"
.AddItem "Miss"
.AddItem "Ms"
.AddItem "Dr"
.AddItem "Rev"
End With

With chbenddate
.AddItem "1st June 2009"
.AddItem "7th September 2009"
End With

title.SetFocus


title.Text = ActiveDocument.Bookmarks("title").Range.Text
firstname.Text = ActiveDocument.Bookmarks("firstname").Range.Text
surname.Text = ActiveDocument.Bookmarks("surname").Range.Text
Address.Text = ActiveDocument.Bookmarks("Address").Range.Text
Processor.Text = ActiveDocument.Bookmarks("Processor").Range.Text
firstname.Text = ActiveDocument.Bookmarks("firstname").Range.Text
surname.Text = ActiveDocument.Bookmarks("surname").Range.Text
LastChild.Text = ActiveDocument.Bookmarks("LastChild").Range.Text
EntDate.Text = ActiveDocument.Bookmarks("EntDate").Range.Text


End Sub

Private Sub CommandButton3_Click()
Unload Me
Application.Move Left:=90, Top:=0
Application.Resize Width:=674, Height:=552
Application.Move Left:=51, Top:=0
ActiveDocument.Close SaveChanges:=False


End Sub

Private Sub CommandButton1_Click()
ActiveDocument.PrintOut Background:=False

Dim XLapp As Object, XLbook As Object, XLsheet As Object, Wbook As String, lastRow As Long, myRange As Range

On Error GoTo ErrorHandler

' Set application, workbook and worksheet objects
Wbook = "C:\my docs\worksheet.xls"

Set XLapp = GetObject(, "Excel.Application")
Set XLbook = XLapp.Workbooks.Open(Wbook)
Set XLsheet = XLbook.Worksheets("Sheet1")

With XLsheet

' Find last completed row on reporting log
.lastRow = .Cells(.Rows.Count, 1).Row + 1

' write information to reporting log
.Range("A" & lastRow + 1).Value = firstname

End With

' Close and save workbook and clear objects

Set XLsheet = Nothing
XLbook.Close SaveChanges:=True
Set XLbook = Nothing
Set XLapp = Nothing


' Error Handler
ErrorHandler:
If Err.Number = 429 Then
'Excel is not running, open Excel with CreateObject
Set XLapp = CreateObject("Excel.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
End If

title.Value = ""
Address.Value = ""
firstname.Value = ""
surname.Value = ""
EntDate.Value = ""
LastChild.Value = ""
Unload Me
chblettergen.Show
End Sub
Private Sub title_Change()
wb "title", title
wb "title2", title
End Sub
Private Sub Address_Change()
wb "Address", Address
End Sub

Private Sub Processor_Change()
wb "Processor", Processor
wb "Processor2", Processor
End Sub
Private Sub firstname_Change()
wb "firstname", firstname
wb "firstname2", firstname
wb "firstname3", firstname
wb "firstname4", firstname
End Sub
Private Sub surname_Change()
wb "surname", surname
wb "surname2", surname
wb "surname3", surname
wb "surname4", surname
wb "surname5", surname
End Sub
Private Sub LastChild_Change()
wb "LastChild", LastChild
End Sub
Private Sub EntDate_Change()
wb "EntDate", EntDate
End Sub

[nickirvine]

ADMIN: PLEASE DELETE DUPLICATE

[Dave]

Trial changing ".lastRow " to just "lastRow" HTH. Dave

[lucas]

Duplicate post deleted.

It would help if you would post your document so we don't have to re - create it to help you. I assume the excel file is just a blank, new workbook, at least to begin with.

[nickirvine]

Yeh the excel file is blank. The problem with the word document is that it has some sensitive information on that I can't really post on the internet.

I was hoping there was a little error in the code somewhere that I was missing but I can definately create a kind of "censored version" if you like?

I'll start on it now..

[nickirvine]

Ok here is the version I have at the moment.

It doesnt have the new part where I'm trying to save it to an excel spread sheet. As I thought you would like to see what ive got so far so it makes a bit more sense.

As i said before I am trying to get it to print a letter and then save the fields you enter onto a spreadsheet once you click the command button.

Any help appreciated.

[Dave]

Did you trial my suggestion from my previous post? Dave

[nickirvine]

yes dave. sorry for not replying. still no joy.

any other suggestions welcome.

[nickirvine]

Can anyone else offer me any help? I'm sure ive overcomplicated things in my original code i posted.

[lucas]

I just had a minute to look at this but this seems to work:

[nickirvine]

Hi Steve,

Thanks for giving it a go. I'll have a go and get back to you.

Thanks again

Nick

[Tinbendr]

Here's my attempt.

It saves everything from the userform.

The Excel routine is in a module by itself. (Module 1)

I also took the liberty of prefixing all your objects with their function. (e.g. txtFullname for textbox)

[nickirvine]

Hi Tinbendr,

I get this bit highlighted "Dim oXL As Excel.Application" and cant find project or library. Any ideas?

Thanks for your efforts so far.

[nickirvine]

Lucas,

Thanks for helping me out i really appreciate it. I still get the same error as Tinbendr's. Can't find the excel file ... grrr!

Any ideas?

I think it may be due to the old version of Word and Excel im using?

Thanks again for your help

[lucas]

Tinbendr's code works for me. What version of Word are you using. I tested it in 2003.

One quick question, did you actually try tinbendr's file or did you copy the code. If you just copied the code you would need to set a reference to excel........

[nickirvine]

Lucas, Yeah I used the file in the zips but I edited the code to the destination of the excel file.

I'm using Word & Excel 2002. I'm sure once upon a time I found a work around for 2002 that found the file, set up a seperate function. But can't for the life of me remember how i did it or where i found it.

Thanks for all your help guys.

[Tinbendr]

You'll have to set the reference for your version. 10.0 if memory serves me. The code still should work.

[nickirvine]

Set the reference?

The reference of my word is 10 point something. It doesnt work tho tried it on my work computer and my home (both use same version)

[Tinbendr]

In the Word VBE, Tools, Reference, look for Microsoft Excel 10.0 Object library. If it's not selected at the top, then scroll down until you find it, then check it.

[nickirvine]

Hi,

Thanks for your continual help.

That check box was already ticked, so guess that wasn't the problem

[nickirvine]

Hi,

Thanks for your continual help.

That check box was already ticked, so guess that wasn't the problem

[Tinbendr]

Sorry you're having trouble.

You could try and copy the code over to your original document. You'd have to rename all the objects.

[nickirvine]

Tinbendr: yeh I have tried that I still get the same error.

Are you convinced your code works and is error free then? I've tried it exactly as you sent it, only changed the filepath. So can't understand why it is not working.

[Tinbendr]

I just checked it again and it works fine.

Grabbing at straws here.

What is your macro security set to? Mine is at low.

I assume your Excel runs ok.

Step through the code (F8) to determine if it halts at a different location than the highlighted line.

Hope this helps.

[fumei]

It works for me as well, although in References it had MISSING: Excell 11.0.

It worked when I changed it to Excel 10.0.