Creating formatted Word Doc from Excel VBA??

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
I'll start by apologizing for my vagueness but I'm working on a proprietary project of which I can't disclose much, but seeking advice for a feature I'd like to implement.

My project is time oriented such that it pulls in time-based data from a server, adds data provided by the user, and presents an XY chart that has time across the X-axis (bottom) and a lot of horizontal lines showing events by start/stop times (staggered vertically by type of event...very similar to a Gantt chart).

One of the products I produce from this is a text file listing all of the events in chronological order. This gets imported manually by the user into a word doc that requires formatting per our company "standard" appearance. Because my product is a text file, it has no formatting, so the user has some work to do to convert the data from text to "usable". I'd like to automate this for the user.

I'd like to learn to create a word doc (this isn't hard) and push properly formatted data to it. In this case properly formatted means to assign the appropriate color (based on the line's content) and set tab stops (which should be the same for every line). In some cases the color may change mid-line.

(Note: Google was not helpful. Nothing in regards to tab stops. I also found a post where the problem was fixed by associating the appropriate Word library (to access Word VBA constants) but didn't describe how or what library).

Any point outs to good resources (or straight tips!) would be greatly appreciated!!!
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
Clarifying. At the point of creating the doc, I have a time-sorted array of items. I currently go line by line in the array, formatting and writing to the text file as I go. I'd like to do the same but to Word. Set the tab stops, write red text (or green text or black text or a mixture of red and green), go to a new line, continue. I'd also like to set the line spacing and other such items so the user can just cut and paste from my output to his report.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,846
You don't need to create and format the Word doc on the fly.... just make a Word template with "holders" (ie form field, table, etc) and then insert the data in the holders. It seems like it would be a lot easier just to use the array for this. HTH, Dave
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
Thanks, I looked at that as an option, but users of my "tool" won't necessarily have a copy of the template. I've found from experience that the more I count on other to manage parts of this the more things go wrong. Plus, It's much easier than it sounds...I simply assemble lines from an array to a document. I suspect a template is actually the harder way to go.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,846

ADVERTISEMENT

It seems that U will need to use late binding for Word being that all of your users may not have a reference set to the Word Library. Here's a thread with some code to create a Word doc and put stuff in it using late binding. You start and post again when you need some help. Be specific with what you want the outcome to be and where/what the data being transferred is. Good luck. Dave
https://www.mrexcel.com/forum/gener...questions/1046723-macro-creates-word-doc.html
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
Thanks, I'll give it a shot. As for access to the word library, everyone has the full, identical office suite installed so I doubt that will be an issue.
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
I went to that site and while I found some useful stuff, it left a lot on the table. If someone could provide examples (or links) to assist with the following I'd be grateful:

1) How do I set the tabs in the paragraph I'm writing (they will be the same for every line...can I set them once or must I do it with each paragraph?)
1a) How do I insert a tab within a line being written (for internal alignment purposes)
2) How do I set the font (needed one time)? How do I mix colors on a single line?
3) How do I end a line with an "end paragraph" mark?

All of the help I have found has been either too simple to meet my needs, too complicated to understand, or too wildly inconsistent to make sense of it.

Thanks in advance for any help you can provide!
 

Forum statistics

Threads
1,144,630
Messages
5,725,394
Members
422,623
Latest member
Dave52

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
Top