Extract Access Data and Load into Word

[samye]

Hello,



I am not sure if this is the correct forum. I did not see one for data extraction.

I have a query that contains building information from various tables in our Access db. I want to import that data into word and format it.

Currently, the query is used by a report that I can save as a pdf or Word. The issue is when saved as Word tabs are added and it's impossible to format. I've tried saving as a pdf and then saving as Word but there are still formatting issues.

Can someone provide suggestions on the best way to accomplish this?

More detail: We have a Word template that we use as a building report. There is a section where the Access report would be inserted so no headers/footers are needed. The number of buildings could vary depending on the location. I thought I could download the query as Excel and then use Word bookmarks to create a formatted page but the number of buildings vary and I have limited VBA knowledge.

Thank you,
Carol

[madpiet]

"I have a query that contains building information from various tables in our Access db. I want to import that data into word and format it."

Do you mean similar to copying the query results and pasting them into Word, so that they get converted into a table? If so, there's code in one of the Access Developer's Handbook Desktop Edition versions that has the code. Off the top of my head, you use ADO, so that you can convert the query into a delimited string, and then you insert that text result into your document and then in your code, you convert that string into a Word table, and format it. But since I used it last in like 2008, I don't remember all the at well. But I'm pretty sure someone here still has a copy of that kicking around and could tell you better. Not entirely sure how to do the same in DAO, if you have to do that.

[moke123]

Do you want something like a Mail merge document? If so try Albert Kallal's super easy word merge.

[samye]

Hi All,
Thank you for your responses. After googling, I think the answer is to write some VBA code that will create/open a word doc and then add the information that way. Originally, I was using a report and saving as Word but it puts tabs in everywhere and doesn't align, etc when exporting. If anyone has other ideas, please feel free to contribute.
R,,,Carol

[moke123]

Albert's super easy word merge http://www.kallal.ca/msaccess/msaccess.html

[mike60smart]

Hi

Why not create the whole report in Access?

[madpiet]

Can't speak for anyone else, but when I glued together the code I got from ADH Desktop, I needed to be able to edit the result (add comments/explanations). So it depends a lot on what will be done with the final result.

[madpiet]

I was going to say to use ADO, but I think you can use Leo's code that's here: Code Snippets (accessmvp.com)
You'd basically write a query to return the rows and columns you wanted in your table, and then use his SimpleCSV function to convert it into essentially a delimited string. Then you'd insert the result of the SimpleCSV into Word, and then with the result selected, you'd convert that to a Word table, and format it.

Probably the easiest way to get your head around it is to do it manually once or twice. Then you can record it as a macro and then you'd call that macro from your Access code which creates an instance of Word. There are some simple examples of Word automation on mvps.org/accessweb