Word and Excel sophisticated integration

[jjj2k]

Hello everyone,

This is my first post on this forum and I hope I don't bore you with my problem.

I have an excel workbook which reads data from our SQL Server by sql queries. I use this worksheet for preparing tables and graphs which I paste in to word documents for my reports. Every client I have has a tab located in this workbook.

Now with my client list reaching 50, I am looking for a sophisticated process so that I can integrate my reports in word document to excel.

I previously linked tables and graphs using "paste link" in Microsoft Word. It worked well but I am having to do it for about 50 clients now and it becomes tedious month end when you have to generate reports for them monthly, so looking for a powerful solution that automates this process.

My report structure is the same monthly. Paragraphs of texts typed in, tables and graphs (linked to Excel). I want a way so that I can reduce time as I have to ensure I refresh my worksheet, save the updated version, checking if the data actually is correct in excel and word. If I want to add any tables and graphs, I have to do it 50 times. This all takes time and hence, costs me money. I want to reduce the time I spend on checking and rather use that time to do more analysis.

Any help will be appreciated. I don't know if I am confusing but hopefully I have explained what I need to do.

Really hoping someone out there has a solution for me.

Regards

[Frosty]

You may ultimately need to hire a programmer to help you, since from your general description... it sounds like there may be a lot more variables involved than simply pointing you towards a way of looping through 50 excel spreadsheets, copying whatever graph object is on that sheet, and then pasting it into a document in Word.

My suggestion on how to proceed would be one or both of the following things:

1. Write down the step-by-step instructions of what you do as if the reader didn't know the programs at all. Something like:
a) Open MyClients Workbook in Excel
b) Select Sheet "myClient1"
c) Select the chart on that sheet
d) copy the chart
e) switch to Word
f) Go to page 7, immediately after the paragraph titled "MyClient1"
g) paste the chart using Paste Special > Linked Chart (or whatever the terminology is)

Or whatever the actual process is that you're repeating 50 times. Treat yourself like a macro recorder (I realize you can't effectively "record" a macro between the two applications, but this will help any analysis you could get here)

2. Try recording whatever the process is both in Excel and Word and posting those results. Make sure you get rid of any client specific data, but leave the important stuff. Most of the time, these generic requests involve a lot more steps than you think they do... so it's hard to give you a specific solution when I have a feeling you may, unknowingly, be leaving out a lot of essential info.

It *may* be as simple as you needing some help turning the two recorded macros into something which will work for all 50 of the sheets in the workbook. Or automating the process of refreshing the link information for the charts in the word document.

I haven't worked with linked excel tables in Word documents for years... mostly because it was such a breakable solution. But if that is currently working for you-- try recording your actions of "updating the links" and posting that recorded macro.

[jjj2k]

Thanks for your response Frosty. I was advised by a colleague of mine to use MailMerge in Microsoft Word 2010. But my experience with that particular tool has been rocky and I prefer not to use it.

Is there a way I can export all my tabs in the workbook as an individual .doc file with all the tables and charts, company XYZ in tab "ComXYZ" has say 1 table and 1 chart, company ABC in tab "ComABC" has say 20 charts and 5tables, so the charts and tables are variables. I say 5tables because not all tables worksheets are continuous and are separated by 3-5 empty rows in between.

[Frosty]

Ooof.

Is there a way to export all of your tabs (i.e., Worksheets) in an Excel Workbook to a single Word document? Not as far as I know.

And Mail Merge sounds sketchy at best, but more likely impossible given multiple chart objects on a single sheet (I don't even know how you'd bring in a single chart object, but I haven't yet boned up on Mail Merge capabilities in Word 2010).

Is there a reason you really really need to use Word in the first place?

Admittedly the word processing part is a lot easier in, well... a word processor. But it sounds like your main info is in excel, in tables and charts. Perhaps some dinking around with the formatting of the Workbook and judicious use of some floating textboxes coupled with custom page breaks would get you want you needed... and then you'd always be working within Excel and generating your "reporting" from excel... you could forget the whole worry about linking data etc.

Even though it's a spreadsheet program, there is a lot of formatting you can do to make it look pretty nice.

And if you then print the various sheets to a pdf printer (with a good use of the Page Setup for each sheet), you may get an electronic output which is viable for your clients.

Otherwise... it sounds pretty complicated as a programming solution. There may be someone else on here that has a "quick fix" type solution, but that sounds to me like you're either going to need to hire someone, or get your elbows pretty greasy.

There's a difference between fixing snippets of code, pointing people in the right direction, and providing an entire soup to nuts solution, time-wise. That sounds like something which could take 5-6 hours of time minimum, and most likely a good bit more.

If you can't figure out a way to get Excel to be your "word processor" why don't you try one of the two ways to move forward I suggested in the earlier post.

If you have the desire, you should be able to get there eventually... it seems that there are a lot of helpful people on this board, especially in the excel section (based on the number of people viewing at any given time).

[jjj2k]

Hi Frosty,

I quite like your idea of using excel as a word processor and will look it up. Can you point me in the right direction for generating reports or books I can look into or forums that deal exclusively with issues like this?

Also, since I am querying SQL from excel, can I have more than one table per sheet?

[Frosty]

I'm sorry, I don't know any resources off the top of my head... I'm more of a word guy than an excel guy, and when I've dealt with the results of SQL queries in coding, it's always been something I then used during my programming.

However, this seems to be a pretty heavy Excel crowd... why don't you try the main Excel forum and ask some really specific questions about what you'd like to do.

As for Excel as a word processor... I think it's going to be pretty rudimentary. Look up Text Boxes, merged cells, inserting page breaks, and the explore around the page setup stuff. There are ways to put in Headers and Footers, etc.

You may also look at having your charts for a particular worksheet on their own worksheet rather than embedded objects. It would probably benefit you to have your data tables on separate worksheets as well. This obviously has the potential to get fairly big if you have multiple worksheets for your spread sheets.

Depending on the kind of stuff you're doing, you could look at the reporting features of Access for your data tables (there are fairly straight-forward connections to SQL databases in Access, and the reporting in Access may be more user-friendly than setting up the tables in Excel), the charting for your Excel...

It really depends upon your tolerance for learning new things, and the kinds of things which are most important to you. But you're probably going to have to see if you have a trainer at your company, or keep trolling and asking questions.

[Paul_Hossler]

What I've done to integrate Excel data into Word is to

1. create a Word template (DOTX) with bookmarks where I want each piece of the Excel data to go

2. Use an Excel macro create a Word instance with a new document based on the template

3. Insert the Excel data into the work document at the bookmark(s)

4. Save the word document

If there were 50 tabs (clients) in the Excel WB, you could just loop 50 times

Paul

[jjj2k]

Hi Paul,

Is there a sample you could provide to go with your solution? that would be great. I understand if you don't want to if it gets too messy or you have to clean up/hide data, but if you have a simplified working example for me that would be great. I am just confused and awestruck with 50 reports for 50 different companies I am having to do.

[Paul_Hossler]

It'll take me a little while to pull something togeather to demo the concepts that I used

Again .. they might not be the best or most reccommeded way, just what i did




Paul

[jjj2k]

No worries Paul. take your time if you are keen on demonstrating your solution. i am working on it as well, I will have a demo ready by the end of this week, hopefully, time permitting :)