UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Access Query To Word Document, Access 2016    
 
   
tbs
post Jun 18 2019, 01:54 PM
Post#1



Posts: 327
Joined: 12-February 13



I tried to look around for solutions but did I understood correctly that:

In order to send records from Access Query to a Word document, you have to use mail merge?

What I want to do is to send several tables from different queries to different part of a Word document. Is this even possible?

Thanks in advance.
Go to the top of the page
 
MadPiet
post Jun 18 2019, 02:00 PM
Post#2



Posts: 3,333
Joined: 27-February 09



yes, but it's a different kind of problem. <g> If you're doing a Mail Merge, you're basically sending one record to a Word file and inserting a single record in a single document. What you're asking is to send one or more sets of records (a table-shaped object) to a single document. There was an example in Access Developer's Handbook from like 15 years ago. (You basically open the recordset, convert it to a delimited text string, send that to Word and convert that string into a Word table.

Someone here may still have the code for it.

The catch in your case is that you'd have to modify the code to put the different query results in different places in the Word document, I think.

This is what I was thinking of: https://docs.microsoft.com/en-us/SQL/ado/re...SQL-server-2017

This post has been edited by MadPiet: Jun 18 2019, 02:59 PM
Go to the top of the page
 
GroverParkGeorge
post Jun 18 2019, 02:19 PM
Post#3


UA Admin
Posts: 35,894
Joined: 20-June 02
From: Newcastle, WA


Perhaps you can create sub-docs for the master Word document for each of the individual queries.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
tbs
post Jun 18 2019, 04:52 PM
Post#4



Posts: 327
Joined: 12-February 13



This might be a little, er...vastly, over my head.

If this is feasible, I'll give it a shot but let me elaborate on this project a little further:

This project involves a little under 200 documents averaging 10 pages. There are different tables in each section per document (average 3 section).

I am thinking of linking embedded excel spreadsheets. Two major cons I can think of are: 1. end users are likely to mess up the links when the spreadsheets are updated or moved and 2. tables are not consolidated into one main table (as would be if in Access db) for further analysis/pulls using advanced queries.

So....should I:

- pursue the Access to Word route,
- stick to linking embedded spreadsheets
- do something different/efficient (like maybe just put them all in Access, build them in reports using forms for updates, and export them into Word or PDF)?




Go to the top of the page
 
MadPiet
post Jun 18 2019, 04:53 PM
Post#5



Posts: 3,333
Joined: 27-February 09



is there a way to tell from the structure or content of the Word document where the tables are supposed to go? (like a bookmark or something?)

off the top of my head, when I did this before, I converted the result of the query to a delimited string,
sent that result to Word, and converted that delimited string into a table.
Go to the top of the page
 
tbs
post Jun 18 2019, 05:03 PM
Post#6



Posts: 327
Joined: 12-February 13



MadPiet -

QUOTE
is there a way to tell from the structure or content of the Word document where the tables are supposed to go? (like a bookmark or something?)


if linking an embedded spreadsheet, yes for sure.

QUOTE
I converted the result of the query to a delimited string, sent that result to Word, and converted that delimited string into a table.


is there a code in the Code Archive that does this work?
Go to the top of the page
 
MadPiet
post Jun 18 2019, 07:51 PM
Post#7



Posts: 3,333
Joined: 27-February 09



Oh, so these are non-trivial documents. It's helps a lot that it's possible record macros in Word, save them, and then run them later (or include that macro in a Word template), and then call them later. It's trivial to "point at" a given table in your document, too. Here's a trivial example:

CODE
Sub ShowTableDimensions()
'
' ShowTableCount Macro
'
'
    Dim i As Integer
    
    For i = 1 To ActiveDocument.Tables.Count
        MsgBox "Table " & CStr(i) & " has " & CStr(ActiveDocument.Tables(i).Columns.Count) & " columns and " & CStr(ActiveDocument.Tables(i).Rows.Count) & " rows."
    Next i
End Sub


So it's possible to basically create a dataset in Access (from a query), convert that to a delimited string, insert that string into a Word document/template, and then convert that string to a table, and then apply a style to the table. (I've done that before. But it was a long time ago!).

This just sounds like one of those cases where I'd be stubborn and write some code to do this, because I'm too lazy to do this manually 200 times. gets old fast.

Ha! found some code by Daniel Pinealt to do something like what you want... might have to ping him and see if he can help out.
https://www.devhut.net/2012/04/23/ms-access...rds-to-ms-word/
This post has been edited by MadPiet: Jun 18 2019, 08:02 PM
Go to the top of the page
 
DanielPineault
post Jun 18 2019, 07:59 PM
Post#8


UtterAccess VIP
Posts: 6,901
Joined: 30-June 11



You can use Word Automation to populate word documents, or even create them entirely from scratch. Refer to http://www.devhut.net/2012/04/23/ms-access...rds-to-ms-word/ for a simple demonstration upon which you can build.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tbs
post Jun 19 2019, 11:33 AM
Post#9



Posts: 327
Joined: 12-February 13



I am going to check Daniel's code out. I will come back with updates here (and probably with additional questions).

Thanks Daniel, MadPiet, and George.
Go to the top of the page
 
tbs
post Jul 1 2019, 12:13 PM
Post#10



Posts: 327
Joined: 12-February 13



Update: I am not having much luck here. It may be a way above my skillsets.

I'm going to try a different approach so I will look around first before posting the new question. I'll include the link here when I do.
Go to the top of the page
 
MadPiet
post Jul 1 2019, 03:35 PM
Post#11



Posts: 3,333
Joined: 27-February 09



Any chance you could post some sample data (not real, but representative) and a document and some notes explaining what goes where?

Converting the results of a query to a delimited string is trivial (well, once you know the trick). Then you could send that to Word and convert it to a table. But you'd need a way to programmatically determine where they go...
Go to the top of the page
 
tbs
post Jul 2 2019, 07:06 AM
Post#12



Posts: 327
Joined: 12-February 13



MadPiet,

Sure. I'll try to get that out sometime later today.
Go to the top of the page
 
MadPiet
post Jul 5 2019, 07:00 PM
Post#13



Posts: 3,333
Joined: 27-February 09



Hey Daniel,
I have a quick question... Given this line of code:

CODE
Set oWordTbl = oWordDoc.Tables(1)


What if I had a table of QueryName (string), WordTableNumber (integer), Filter (string) and then I could open a document, loop through this table ("tblPlaceQueriesInDoc") and then
1. Open doc
2. Open recordset of above (read-only, forward-only).
3. For each query in above table, Convert recordset to table, add column names, format, drop into document somewhere
4. Save document
5. close recordset

So there would be a list of documents in one table, then a child table with (DocName/ID, TableID, QueryName, filter (optional)). Then loop through all these in step 3.

Right?

Might have to resurrect my old healthcare DB and do it, just to practice.
thx,
Pieter
Go to the top of the page
 
tbs
post Jul 5 2019, 08:23 PM
Post#14



Posts: 327
Joined: 12-February 13



Can you send a sample db? This is the best way I can figure out what you were doing?

I havent been working on this one this week, but definitely will next week.

Thanks in advance.
Go to the top of the page
 
MadPiet
post Jul 5 2019, 09:02 PM
Post#15



Posts: 3,333
Joined: 27-February 09



I guess I could do something with maybe AdventureWorks because it has a bunch of tables, so I could write a bunch of queries, and dump them into a single Word document as tables.

Do you basically have a document that looks like this?

<bunch of paragraphs>
<table1>
<bunch of paragraphs>
<table2>
<bunch of paragraphs>
<table3>


I'm just trying to get my head around an easy way to do this. Does each table in your Word document get its data from a single query in your database? (so your merged document would look like this...?

<bunch of paragraphs>
<table1: from Query1>
<bunch of paragraphs>
<table2: from Query2>
<bunch of paragraphs>
<table3 from Query3>


Can you post a dummy Word document with an Excel file and your linked Excel docs or whatever? I'm just trying to understand what the document you're trying to build looks like.

Thanks!
Can you post even a dummy database and a document with even Excel tables in it?
Go to the top of the page
 
tbs
post Jul 5 2019, 09:09 PM
Post#16



Posts: 327
Joined: 12-February 13



Sure. Let me do it tomorrow morning. I am I. EST zone USA.
Go to the top of the page
 
MadPiet
post Jul 5 2019, 09:37 PM
Post#17



Posts: 3,333
Joined: 27-February 09



I have a question...

Are you adding a different number of tables in each "merge" document? If you are, how do you know which query results go in each document and where they go? If this kind of thing were in a table(s), then it would be more flexible. A bit more complicated, but more flexible. but building a form (main document) and a subform with (main doc id, table number, name of query) then you could do this really easily.

that's why I wanted to know about the destination document and whether or not they all "receive" the same queries.
Go to the top of the page
 
tbs
post Jul 11 2019, 08:44 PM
Post#18



Posts: 327
Joined: 12-February 13



Here, let me assign a really watered down DB and a fake document.

You'll notice that on the fake document (word doc), I've identified where I want the query results on the document from the Access db.

Attached File  Access2Word.zip ( 918.74K )Number of downloads: 3


Thanks.
Go to the top of the page
 
tbs
post Jul 11 2019, 09:04 PM
Post#19



Posts: 327
Joined: 12-February 13



this looks like the way to go.

from MS

I just tried the Use a table or query as the data source section but it didn't work the way I thought it would.
This post has been edited by tbs: Jul 11 2019, 09:16 PM
Go to the top of the page
 
MadPiet
post Jul 11 2019, 09:20 PM
Post#20



Posts: 3,333
Joined: 27-February 09



Okay, gotcha. Basically, open a query, drop it into the marked spot, convert it to a table, go to next.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 10:48 PM