Export Excel data to Word or through Excel Report Template

[atomichybrid]

Hi All,

I wanted to know if there is a way to export Excel cells to a Word Document? Though I want it to be formatted in a specific way, I had no luck in Googling this kind of method as most of them were straight out copy and paste of the table or linked.
Or if it's easier perhaps make an Excel Template of the format and then use VBA to transfer the data that way?

I have attached the docs for reference.

I also want it to be able to continue the next page with the same table header, should the list go long.

I hope this is clear on what I want to achieve.

Thanks.

atomichybrid

[macropod]

Is the process being driven from Word, or from Excel? Are you using a Word template with your boilerplate content in place, including at least the first two rows of the table? Repeating the table heading on subsequent pages is as simple as using Word's 'Heading Rows Repeat' option, which you'd apply in a Word template when you create it, thus obviating the need for code.

[atomichybrid]

Hi macropod,

Thanks for your feedback, I did lack some details, apologies for that.
I was hoping the process will be driven from Excel as most of the data stems from there.

The Word template will look like attached (Word Quote Template), prior to VBA code executing.
I am cool with Word being left open for the macro to work as I can save as another name leaving the template intact, all other customer information will be inputted manually.

Yep the repeat title can be done through Word not issue thought I could save that effort but the most important feature is the exporting Excel data to the format on 'Word Template'.

I mainly wanted to know if it was possible to program and it if can, I am open to your input,

Thanks.

atomichybrid

[macropod]

As previously indicated, your template needs to have the basic table already present, with just the heading row and an empty data row; if you don't do that, a whole lot of otherwise-unnecessary code would be required.

Besides which, what you have posted is a Word document, not a Word template.

[atomichybrid]

As previously indicated, your template needs to have the basic table already present, with just the heading row and an empty data row; if you don't do that, a whole lot of otherwise-unnecessary code would be required.

Besides which, what you have posted is a Word document, not a Word template.

Hi macropod,

Would you need it to look like the attached below?

Yes, I use the Word template and document interchangeably, so I hope that clarifies things. The original file will be in dotx filetype, I can't upload that file due to attachment restrictions so it's in docx for now.

My main objective is; Is it possible to export data from the Excel Template to something like the Word Template?
If there are some things that need to be prepared first I have no problem having that sorted, I hope the above are satisfactory to compile some form of code.
I don't want to be beating around the bush if this is not possible.

atomichybrid

[macropod]

Try the attached workbook & template, both of which should be unzipped to the same folder.

As coded, the macro outputs a PDF named 'Quotation'. Presumably, you'll have a variable name in some other part of the code you'll be using for the output filename. Simply replace "Quotation" in the code with that variable's name.

[atomichybrid]

Hi macropod,

This works really well! Thanks heaps!
My only problem is since its in PDF format I am unable to write the customer's details after. Can this be exported to docx file instead so I can edit that and save as PDF afterwards.

* Still amazed on how this works *

atomichybrid

[macropod]

Try the attached. The document is left open for editing.

[atomichybrid]

Hi macropod,

This is genius, thanks a lot for putting this code altogether it does it very well.

Thanks heaps again!

atomichybrid