export access query to word vba

claven123

Board Regular
Joined
Sep 2, 2010
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm trying to make a letter from data from access. I have about 40 or so records to print out on a work document. I've set up the document with bookmarks. I'm getting the data from a query.

So far this sorta runs.... I click the cmd button.... nothing happens. When I click on the actual file name, it says it's read only, then if I click thru it open and then the it runs.... creating saving the 40 or so files in the documents folder. Then when I open one of the word files it is formatted correctly.

I would like it to print these out, don't really need to save them. But, for testing purposes I'm just saving them, I can change that function later.

How do I get this to run from the cmd click? What am I missing?

Thanks,




Code:
[COLOR=#465584][FONT=Courier]Private Sub ExportToWord_Click()[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]    Dim wApp As Word.Application[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim wDoc As Word.Document[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim rs As DAO.Recordset[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        Set wApp = New Word.Application[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx")[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        Set rs = CurrentDb.OpenRecordset("AddressLabelCurrent", dbOpenDynaset)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    If Not rs.EOF Then rs.MoveFirst[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Do Until rs.EOF[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        wDoc.Bookmarks("FirstName").Range.Text = Nz(rs!Name, "")[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]               [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        wDoc.SaveAs2 "CurrentccProject.Path" & rs!StreetAddress & "_documentname.docx"[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        wDoc.Bookmarks("FirstName").Range.Delete wdCharacter, Len(Nz(rs!Name, ""))[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]      [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    rs.MoveNext[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Loop[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]   wDoc.Close False[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   wApp.Quit[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Set wDoc = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Set wApp = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Set rs = Nothing[/FONT][/COLOR]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I'm sorry I cross posted this question. I visited the link and the sub link in the rules. I've posted this question to that forum, this one and I think and a couple more in a full disclosure format. My intent was not to be malicious and or deceitful in anyway. I've noticed over the years that some forums will produce results more than others and sometimes I get a reply that is very far off base, then never will get a reply to the original question, so I've noticed that if I post to other boards I end up with good responses to my questions. i realize why this is discouraged.

I will refrain from doing this in the future.

D
 
Upvote 0
I've posted this question to that forum, this one and I think and a couple more in a full disclosure format. My intent was not to be malicious and or deceitful in anyway
We don't have a rule against cross-posting, only about what you're expected to do when you do so. The main concern is that people don't waste their time going over ground that's been covered elsewhere.

Please provide full cross-post links on all of the forums concerned.
 
Last edited:
Upvote 0
This is unclear:
When I click on the actual file name, it says it's read only, then if I click thru it open and then the it runs.... creating saving the 40 or so files in the documents folder.

What actual file name are you clicking on? Are you saying you are doing this in the middle of running your code? When you say nothing happens do you mean literally nothing? How can nothing be happening if at the end you get your 40 or so saved files! Clearly something is happening.
 
Upvote 0
I click on the cmd button, nothing happens, well something happens sort of.

I then navigate in the documents folder for the target word file, the one access will export the data to. I double click this and it opens with an error, that the file is open read only. I then say yes and the word doc opens. THEN it starts to run the code, ie placing a name in each instance of word and after this is done, I have 40 word files saved in the documents folder. It does nothing until until I open the word doc.

However, the issue has been solved.

Instead of:
wDoc.SaveAs2 "CurrentccProject.Path" & rs!StreetAddress & "_documentname.docx"
use:
wDoc.SaveAs2 CurrentccProject.Path & "" & rs!StreetAddress & "_documentname.docx"

Presumably you're using something more meaningful than 'documentname', too...​


Cheers
Paul Edstein
[MS MVP - Word]
 
Upvote 0
I then navigate in the documents folder for the target word file, the one access will export the data to. I double click this and it opens with an error, that the file is open read only. I then say yes and the word doc opens.
You could change:
Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx")
to either:
Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx", ReadOnly:=True)
or:
Set wDoc = wApp.Documents.Add(CurrentProject.Path & "\ExportLetter.docx")
with the second approach, you could omit:
wDoc.Close False
because your document is being treated as a template.
 
Upvote 0
It's possible that the code is waiting for a dialog box to be clicked (somewhere, perhaps invisibly). I would try the open read only approach (and other suggestions) above. On the other hand, your code might be the thing that has the file open already ... which is very strange why it wouldn't be able to continue.

As a general debugging method, I'd add something to the code so you know if the file is opened successfully or not:

Code:
Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx")
If not wDoc is nothing then
    msgbox "file opened!"
else
    msgbox "file not opened!"
end if

Or ... actually ... I'd just step through the code line by line.
 
Last edited:
Upvote 0
I double click this and it opens with an error, that the file is open read only.
Because Access still has a lock on it (not you)? Try closing the newly saved document in code before you navigate to the folder, or for testing, close then reopen in code to save yourself the trouble of navigating. You should then be able to review the fields. I don't think it's running any code when you open it - I think it's creating a copy based on the locked copy, which takes a moment to recreate the data in the bookmarked fields.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top