Export data from select query to word

[CASLAN]

I'm trying to run a code for extracting info from a few data fields from a select query and put it into bookmarked places in a word fil. Why doesn't this code work? It runs forever.

Please help!




Public Sub Export()

Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim rs As DAO.Recordset
Dim dbs As DAO.Database


Set wApp = New Word.Application
Set wDoc = wApp.Documents.Open("""*word file*""")
Set dbs = CurrentDb()


Set rs = dbs.OpenRecordset("PersonalData")


rs.MoveFirst


wDoc.Bookmarks("Name").Range.Text = Nz(rs!Name, "")
wDoc.Bookmarks("Date").Range.Text = Nz(rs!Date, "")
wDoc.Bookmarks("Nationality").Range.Text = Nz(rs!Nationality, "")
wDoc.Bookmarks("Email").Range.Text = Nz(rs!Email, "")

wDoc.SaveAs2 "Desktop" & rs!Name & "_PD.docx"

rs.Close



wDoc.Close False
wApp.Quit


Set wApp = Nothing
Set wDoc = Nothing
Set rs = Nothing

End Sub

 

[MajP]

I would step through the code line by line to see where it is hanging or put some debug.prints along the way to see what gets executed. There is no loop so no way to run forever, but it may be hanging on something. I do not see anything suspect.

 

[arnelgp]

what is this:

Set wDoc = wApp.Documents.Open("""*word file*""")

is it but a placeholder?
you need to supply the path+filename of the docx.

 

[CASLAN]

what is this:

Set wDoc = wApp.Documents.Open("""*word file*""")

is it but a placeholder?
you need to supply the path+filename of the docx.

Yes it's just a placeholder. The real path is in my code

 

[CASLAN]

I would step through the code line by line to see where it is hanging or put some debug.prints along the way to see what gets executed. There is no loop so no way to run forever, but it may be hanging on something. I do not see anything suspect.

Tried to step through it now. It hangs on the opening of the word file as it says it's already in use by another user (me), although it is not. Any solutions for this?

 

[arnelgp]

maybe add Range:

Public Sub Export()

Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim rng As Word.Range
Dim rs As DAO.Recordset
Dim dbs As DAO.Database


Set wApp = New Word.Application
Set wDoc = wApp.Documents.Open(Environ("userprofile") & "\documents\myword.docx")
Set dbs = CurrentDb()


Set rs = dbs.OpenRecordset("table3")


rs.MoveFirst


Set rng = wDoc.Bookmarks("Name").Range
rng.Text = Nz(rs!Name, "")
Set rng = wDoc.Bookmarks("Date").Range
rng.Text = Nz(rs!Date, "")
Set rng = wDoc.Bookmarks("Nationality").Range
rng.Text = Nz(rs!Nationality, "")
Set rng = wDoc.Bookmarks("Email").Range
rng.Text = Nz(rs!Email, "")

wDoc.SaveAs2 "Desktop" & rs!Name & "_PD.docx"

rs.Close



wDoc.Close False
wApp.Quit


Set wApp = Nothing
Set wDoc = Nothing
Set rs = Nothing

End Sub

 

[arnelgp]

Kill Word on Task Manager (Ctrl-Shirt-ESC) first.

 

[CASLAN]

Kill Word on Task Manager (Ctrl-Shirt-ESC) first.

Thanks! It was running in the background.
It still won't save though.
Any idea why?

Code is:

wDoc.SaveAs2 "C:\Users\cas\Desktop\" & rs!Navn & "_PD.docx"

 

[arnelgp]

Does the content of navn have space in it.
Try to save it with navn only + .docx.
Does it get saved to Documents folder?

 

[Isaac]

Maybe rs!Name is a problem. You shouldn't have a column named Name, it's a reserved word.

But for now, try rs.fields("Name").value and see if it makes any difference

Do you get any actual error message from VBA?