Creating formatted Word Doc from Excel VBA??

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,062
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top