Export Report to .docx with image

[Snowflake68]

I am trying to find a way of exporting an Access report that contains an image to a Word document (.docx). I know that I can export to an rft file but that doesnt retain the image. My customer also doesnt want a PDF file and insists on a Word document. Is there a way of doing this via vba using a template or perhaps another way altogether.

Hope someone can help me.
Thanks in advance.

 

[sneuberg]

You can do that using Word automation. The complexity of that depends on how complex your report is. If you have RTF fields in the report you have to do some weird stuff to transfer that into the word document and even with that the line spacing isn't always the same.

I believe if you purchase Adobe viewer it can convert a PDF to a word document. I'd check with your customer to see if that's an acceptable solution. If your customer would be paying for the VBA code to create a word document it might be a lot cheaper to use Adobe for the conversion especially if you are new to writing Word automation code.

 

[Snowflake68]

You can do that using Word automation. The complexity of that depends on how complex your report is. If you have RTF fields in the report you have to do some weird stuff to transfer that into the word document and even with that the line spacing isn't always the same.

I believe if you purchase Adobe viewer it can convert a PDF to a word document. I'd check with your customer to see if that's an acceptable solution. If your customer would be paying for the VBA code to create a word document it might be a lot cheaper to use Adobe for the conversion especially if you are new to writing Word automation code.

Thanks for your reply Sneuberg.
All of my customers are internal company ones so there is no money involved only time restraints really. I would really like to learn how to do it as I'm sure it will crop up again in the future. Do you have any pointers that would help me get started please?

The Access report I am trying to export to a Word document which contains a report header (where the company logo needs to appear) and a report footer (which contains an empty box with a label) as well as a Page footer (for current date and page numbers). The detail contains columns of data that has the column headers in the page header.

Do you think it is achievable to get the report to export to Word given the above outline?

 

[Minty]

Albert Kallal wrote a merge to formatted word template routine which might fit your needs - http://www.kallal.ca/msaccess/msaccess.html

 

[Snowflake68]

Albert Kallal wrote a merge to formatted word template routine which might fit your needs - http://www.kallal.ca/msaccess/msaccess.html

Thanks Minty
This article talks about merging data but all I want is to export a report that I have already built in Access but include the image.

Why does Microsoft make things so difficult? The current export to rtf format works perfectly for my report with the exception of including the company logo. It seems like a lot of work has to be done just to achieve this :banghead:

 

[Minty]

I'd just beat your fellow employees up and make them accept the pdf.
If they need to edit it, get them to edit the source data in the database

 

[Snowflake68]

I'd just beat your fellow employees up and make them accept the pdf.
If they need to edit it, get them to edit the source data in the database

If only I could tell the CEO that lol.

I will have to keep trying in my spare time as I know it will get brought up again. I can only hide for so long.

 

[Minty]

The only flexible way I found (After a similar request) was to use Albert's process. It takes a little setting up from memory, mainly a suitable template file.

Obviously about 2 months after we put it in place (and despite us saying this isn't necessary and won't get used) , the requesting department stopped using it...

 

[sneuberg]

Thanks for your reply Sneuberg.
All of my customers are internal company ones so there is no money involved only time restraints really. I would really like to learn how to do it as I'm sure it will crop up again in the future. Do you have any pointers that would help me get started please?

The Access report I am trying to export to a Word document which contains a report header (where the company logo needs to appear) and a report footer (which contains an empty box with a label) as well as a Page footer (for current date and page numbers). The detail contains columns of data that has the column headers in the page header.

Do you think it is achievable to get the report to export to Word given the above outline?

That sounds pretty simple. In our case we had to do a lot more. I need to strip our code down a bit, take out the parts you don't need and make it more generic before I can post it. I'll try to get it posted by tomorrow afternoon.

 

[sneuberg]

I've attached a database with code in the module that produces a word document. This doesn't do very much justs adds a header image and the records from a query in a Word table. You will see in the code that the image for the header is an external file. I don't like this but I couldn't find any way of using an embedded image. I do have code that will create the external file from a BLOB if you are interested.

As you are writing your code for this and you need to figure out how to do something you can open a word document and record a macro doing what you want and then look at the macro code. This doesn't always work in which case in you just google "Word VBA" + what you are trying to do. You can usually find something.

 

[Snowflake68]

I've attached a database with code in the module that produces a word document. This doesn't do very much justs adds a header image and the records from a query in a Word table. You will see in the code that the image for the header is an external file. I don't like this but I couldn't find any way of using an embedded image. I do have code that will create the external file from a BLOB if you are interested.

As you are writing your code for this and you need to figure out how to do something you can open a word document and record a macro doing what you want and then look at the macro code. This doesn't always work in which case in you just google "Word VBA" + what you are trying to do. You can usually find something.

Thanks for this, much appreciated.
I currently have a nasty cold at the moment so am off work ill but I will definitely have a look at this tomorrow if I am feeling better by then.
Will this allow me to use a template file containing the image in the header?

 

[sneuberg]

Thanks for this, much appreciated.
I currently have a nasty cold at the moment so am off work ill but I will definitely have a look at this tomorrow if I am feeling better by then.
Will this allow me to use a template file containing the image in the header?

The code I posted doesn't use a template file but maybe you can use some of the code in it. If you can use a template file this will probably be even easier. This Web page has some code to get you started.

I suggest using early binding at least until you get this working. The Intellisense helps a lot. For early binding you will need to add the Microsoft Word reference.

 

[Snowflake68]

The code I posted doesn't use a template file but maybe you can use some of the code in it. If you can use a template file this will probably be even easier. This Web page has some code to get you started.

I suggest using early binding at least until you get this working. The Intellisense helps a lot. For early binding you will need to add the Microsoft Word reference.

Hi Sneuberg
I have been playing around with all of your suggestions and found the info on the link you provided a little confusing. So I have been trying to use your code with placing an image into the header. Which I have working but for some reason it keeps modifying the image proportions.

I believe its the line below that I need to sort out for the dimmensions and I read up somewhere that if I use -1 for both the width and the height it will retain the original size of the image but for some reason it doesnt work.

Set HeaderShape = docHeader.Shapes.AddPicture(strHeaderFile, False, True, 5000, 0, -1, -1)

I really didnt think achieving this would be so difficult and time consuming. Im not giving up on this just yet so I will come back to it next week.

Just wanted to give you an update and to also thank you for providing the code and other information.

 

[sneuberg]

The Shape.AddPicture page does say anything about using -1 but these parameters are optional so I think just leaving them off like:

Set HeaderShape = docHeader.Shapes.AddPicture(strHeaderFile, False, True, 5000, 0)

will work better for you.

Sorry that the link was confusing. I'll see if I can set up an example for you. If you can use a template file I think that would make this easier for you.

 

[Snowflake68]

The Shape.AddPicture page does say anything about using -1 but these parameters are optional so I think just leaving them off like:

Set HeaderShape = docHeader.Shapes.AddPicture(strHeaderFile, False, True, 5000, 0)

will work better for you.

Sorry that the link was confusing. I'll see if I can set up an example for you. If you can use a template file I think that would make this easier for you.

Ill try that but yes a template with the image already in the header file would be much better, so thanks if you can help with that.

 

[sneuberg]

In the attached zip file you will find a WordAutomationExample database and a Word document named MyExistingWordFile. In the database in the module Word Automation with Template you will find the code from the confusing link modified so that it does something. It uses the Word document MyExistingWordFile as a template file. I created this file with Word, added an image to a header (a sunset outside my house here in Tucson), and a bookmark named TableLocation. The code inserts the table it creates at that bookmark in the document.

So with this method you can set up your template document with the header and footer you want then add Bookmarks where you want the code to insert data from the database.

 

[Snowflake68]

In the attached zip file you will find a WordAutomationExample database and a Word document named MyExistingWordFile. In the database in the module Word Automation with Template you will find the code from the confusing link modified so that it does something. It uses the Word document MyExistingWordFile as a template file. I created this file with Word, added an image to a header (a sunset outside my house here in Tucson), and a bookmark named TableLocation. The code inserts the table it creates at that bookmark in the document.

So with this method you can set up your template document with the header and footer you want then add Bookmarks where you want the code to insert data from the database.

OMG you are a little darlin I should be able to tweak this now to what I need but I just cant thank you enough for taking the time out to do this for me. (I wont take all the credit when I tell my CEO, I will be sure to tell him how great the people are in Tucson)

Thank you, you are a star. (Nice sunset btw)