Excel Into Word

[SamSpade]

I'm writing a program that grabs a range from an Excel spreadsheet and drops it into a Word document (among other things - it changes wording, creates and drops images etc.). Actually, it does this about 1000 times with dozens of documents that are nearly the same in format. So far, it's working - ok.

The *problem* is that the act of inserting it into the Word document is changing the format. In the spreadsheet, the cells are of a certain size and color - but in the Word doc, they're all different. They don't look *BAD* - but it's not my call. My job is to make it look EXACTLY as it does in the spreadsheet - and it refuses to do that.

I'm sure there must be a setting I've overlooked. Has anyone experience with this sort of thing? Please don't give advice regarding cutting and pasting. This is being automated - there will be no human intervention nor will there be a copy of Word or Excel visible.

 

[julz20684]

Have you tried conditional formatting in the cells within the worksheet in Excel?

 

[ylexot]

Paste special and use windows metafile or one of the other paste special options.

 

[SamSpade]

Have you tried conditional formatting in the cells within the worksheet in Excel?

How do I do that? I'm fairly new to Excel.

 

[SamSpade]

Paste special and use windows metafile or one of the other paste special options.

That's what I've been trying since lunch time. I can't figure out the syntax of how to represent the range, because for some reason, Word doesn't like named ranges when you copy.

I'm trying to programatically take a range - say - A15 - and plop it at a specific bookmark in Word. So far, something like this kind of works:

objBookmark.Range.InsertFile strFileName, "A15", False, False, False

where objBookmark has already been set at the position in the Word doc I want, and strFileName is the Excel doc I'm copying FROM.

I did however notice if I just cut and paste - as an end user - Paste Special does the job, and preserves the formatting.

If I can copy to the Clipboard - with .Copy? and then use PasteSpecial at the bookmark? --- can't quite get it. That SHOULD do it. But I'm missing something.

 

[julz20684]

Have you tried conditional formatting in the cells within the worksheet in Excel?

Select Format from the menu bar and select Conditional Formatting. Select formatting from either Cell Value Is or Formula Is; type constant value or a formula and select your formatting.

 

[BabyGurl1978]

That's what I've been trying since lunch time. I can't figure out the syntax of how to represent the range, because for some reason, Word doesn't like named ranges when you copy.

I'm trying to programatically take a range - say - A15 - and plop it at a specific bookmark in Word. So far, something like this kind of works:

objBookmark.Range.InsertFile strFileName, "A15", False, False, False

where objBookmark has already been set at the position in the Word doc I want, and strFileName is the Excel doc I'm copying FROM.

I did however notice if I just cut and paste - as an end user - Paste Special does the job, and preserves the formatting.

If I can copy to the Clipboard - with .Copy? and then use PasteSpecial at the bookmark? --- can't quite get it. That SHOULD do it. But I'm missing something.

Make it a picture after you do Paste Special. Picture (Windows Metafile), that should keep all of your formatting.

 

[SamSpade]

Make it a picture after you do Paste Special. Picture (Windows Metafile), that should keep all of your formatting.

I can't get that far. Bear in mind one thing I can't do, is do this manually. With 500 to 1000 documents to do on a regular basis, I'm writing a program to do this.

However, I have noticed that "Paste Special" does do the job when done manually. I've included Excel VBA as a reference in the project, so that Ranges and WorkSheets now provide "PasteSpecial" as available methods.

Up until now, I haven't needed to open an Excel application object, because I'm just referencing a range within a file, and copying that range. VBA seems happy with that. Now, I think that will have to change, because I don't think I can programmatically reference Excel worksheets without an actual Excel app object open - although it will be invisible.

I probably should just post in case anyone has answered my other posts.

 

[SamSpade]

Ok. Sucks to be me. I finally found the way to use Paste Special - and it still changes the formatting when done via programming. The colors however, are good. It's the row spacing that is getting changed.

Still working on this.

 

[BabyGurl1978]

Ok. Sucks to be me. I finally found the way to use Paste Special - and it still changes the formatting when done via programming. The colors however, are good. It's the row spacing that is getting changed.

Still working on this.

what type of stuff do you have in your spreadsheet that you are trying to get into word?

 

[BabyGurl1978]

Ok. Sucks to be me. I finally found the way to use Paste Special - and it still changes the formatting when done via programming. The colors however, are good. It's the row spacing that is getting changed.

Still working on this.

Could you fix the row spacing once you get that file into Word? You should be able to correct that problem under Tools, Options, View, and the Formatting marks section.

 

[SamSpade]

Could you fix the row spacing once you get that file into Word? You should be able to correct that problem under Tools, Options, View, and the Formatting marks section.

You know - I appreciate the time you're taking to help me on this - honestly - but I can't seem to get the point across that there's never going to be any opening and hand editing the Word document. I'm creating in a program hundreds of reports. I am importing graphs, charts and tables from a mainframe. I'm looking up a database to get names of images and editing a Word template to change strings of content so that each report is different. Once they are all finished, they go to a web server where clients can download them.

Possibly dozens of them will get updated - daily.

And I create all of these by clicking one button. Click. 500 documents created.

What this means is, I can't rely on any procedure that depends on me opening a Word document. It has to be done within a program. If I have to hand edit 500 files, there's no point in writing the program in the first place.

I might be able to modify the three rows that are expanding after I do the Paste Special - after creating my Excel objects, I did something like this:

Set objRange = objDoc.Bookmarks.Item("Excel1").Range
Set objBookmark = objDoc.Bookmarks.Item("Excel1")
With ActiveWorkbook.Worksheets("Sheet1")
.Range("A15").Copy
End With

objBookmark.Range.PasteSpecial

where objBookMark refers to the Word document.

Simple. So simple, I was surprised, because there's like a dozen variants.

I've just got to find a way to lock the sizing before it gets copied. I don't want to have to dig through the new Excel object in Word. It's a pain in the azz.

 

[SamSpade]

what type of stuff do you have in your spreadsheet that you are trying to get into word?

Thankfully - just text and data, although the data has to be imported across from a mainframe from a proprietary data source - Excel cannot read it directly. But the final range does not contain formualas or summaries or anything fancy - just text, data and a lotta fancy dressing up. I created my template really by carving up the final report - and reverse engineering it all.

 

[BabyGurl1978]

You know - I appreciate the time you're taking to help me on this - honestly - but I can't seem to get the point across that there's never going to be any opening and hand editing the Word document. I'm creating in a program hundreds of reports. I am importing graphs, charts and tables from a mainframe. I'm looking up a database to get names of images and editing a Word template to change strings of content so that each report is different. Once they are all finished, they go to a web server where clients can download them.

Possibly dozens of them will get updated - daily.

And I create all of these by clicking one button. Click. 500 documents created.

What this means is, I can't rely on any procedure that depends on me opening a Word document. It has to be done within a program. If I have to hand edit 500 files, there's no point in writing the program in the first place.



I might be able to modify the three rows that are expanding after I do the Paste Special - after creating my Excel objects, I did something like this:

Set objRange = objDoc.Bookmarks.Item("Excel1").Range
Set objBookmark = objDoc.Bookmarks.Item("Excel1")
With ActiveWorkbook.Worksheets("Sheet1")
.Range("A15").Copy
End With

objBookmark.Range.PasteSpecial

where objBookMark refers to the Word document.

Simple. So simple, I was surprised, because there's like a dozen variants.

I've just got to find a way to lock the sizing before it gets copied. I don't want to have to dig through the new Excel object in Word. It's a pain in the azz.

Gotcha! Sorry.