In my SQL book I have many tables, result of queries, usually 5-10 lines.

I kept loading them as text and tabbing them out myself.



Now I am thinking, it would be wise to export to Excel and export Excel to Word. The only downside, sometimes Excel reformats the data such as date&time, and that requires additional work to undo it.

Another issue, the document is B&W, and Excel introduces blue borders. I had to do properties to change it to black. Is there a solution?

Here is some sample data which I got tired tabbing, and went for Excel:
name schema_id principal_id PrincipalName
dbo 1 1 dbo
HumanResources 5 1 dbo
Person 6 1 dbo
Production 7 1 dbo
Purchasing 8 1 dbo
Sales 9 1 dbo
guest 2 2 guest
INFORMATION_SCHEMA 3 3 INFORMATION_SCHEMA
sys 4 4 sys
db_owner 16384 16384 db_owner
db_accessadmin 16385 16385 db_accessadmin
db_securityadmin 16386 16386 db_securityadmin
db_ddladmin 16387 16387 db_ddladmin

It really isn't clear from your post what presentation in Word you're trying to achieve. It would be more useful if you were to attach a document to a post, showing what some representative raw data look like and what the desired end result is.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I am attaching a document with query and table results.

The attached document shows a table copy & pasted from Excel.

Question: should I do it that way? Copy & Paste tabular data first to Excel, then to Word?

How do is change the blue broken line default to solid black line?

Attached Files

TableSample.docx (12.4 KB, 9 views)

You still haven't shown what the raw, unformatted data look like (ie before you've added the various tab, etc to the code and before you've done the Excel processing) , so I can't really advise on how you might go from that to the desired output. It's not even clear that you need to involve Excel at all.

Changing the table to have black cell borders is quite easy - simply use the table border tools. You might also consider applying a Table Style to the tables so that, say, the heading row has its own format.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

Can I change the default to black border so I don't have to do it every time I copy & paste Excel table?

Here is the raw data:

Cat Subcat Model Prod Price
Accessories Bike Racks Hitch Rack - 4-Bike Hitch Rack - 4-Bike $120.00
Accessories Bike Stands All-Purpose Bike Stand All-Purpose Bike Stand $159.00
Accessories Bottles and Cages Mountain Bottle Cage Mountain Bottle Cage $9.99
Accessories Bottles and Cages Road Bottle Cage Road Bottle Cage $8.99
Accessories Bottles and Cages Water Bottle Water Bottle - 30 oz. $4.99
Accessories Cleaners Bike Wash Bike Wash - Dissolver $7.95
Accessories Fenders Fender Set - Mountain Fender Set - Mountain $21.98
Accessories Helmets Sport-100 Sport-100 Helmet, Black $34.99
Accessories Helmets Sport-100 Sport-100 Helmet, Blue $34.99
Accessories Helmets Sport-100 Sport-100 Helmet, Red $34.99
Accessories Hydration Packs Hydration Pack Hydration Pack - 70 oz. $54.99
Accessories Lights Headlights - Dual-Beam Headlights - Dual-Beam $34.99
Accessories Lights Headlights - Weatherproof Headlights - Weatherproof $44.99

It is useless simply pasting data into your reply. There is no way I can tell from that whether the fields are separated by one or more spaces or tabs. I need it in a document that shows exactly what you're working with. If the fields are separated by single spaces only, then please also explain the process you're using to convert it to columns in Excel, including any code you're using.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I can do two things:
1. Copy table from Management Studio (the sources), paste to Notepad. Copy from Notepad, Paste to Word, tab it manually

2. Copy table from Management Studio, paste to Excel. Format in Excel. Copy Excel, Paste Word. Make grid Black using right click menu.

That's all very well, but I have never used Management Studio. What you're saying tells me nothing about the data format.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Here is a screenshot attached. The first one how it copies in color. The second one I change to B&W in Word by formatting --> recolor.

When I copy & paste to Excel, mostly faithful, usually it requires some manual adjustment such as left align a column.

Attached Files

ScreenShot.docx (87.7 KB, 9 views)

OK, so you have a report table. Have you tried copying & pasting that directly to Word? If so, how are the data formatted as a result of the paste?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

When I paste directly to Word, manual tabbing required. Also I don't get the nice Grid I get with Excel.

Another point, when I paste directly to Word, Word does not know that it is a table, it does not give me option to put up a grid for example.

If there is at least a single tab between the fields when pasted into Word, you can use Word's own functions to convert the data to a table - with gridlines. You don't need Excel.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

When I press hidden code, I see the tabs mostly, but in some cases, they are not there. Like before Japanese, looks like a miniature tab. Before Spanish, a little vertical line.Before Italian a little T sign turned 90 degrees. Before Swedish, NOTHING, no space. Before Hungarian a little thick vertical line.

0 mdy 7 0 us_english English
1 dmy 1 0 Deutsch German
2 dmy 1 0 Français French
3 ymd 7 0 日本語 Japanese
4 dmy 1 0 Dansk Danish
5 dmy 1 0 Español Spanish
6 dmy 1 0 Italiano Italian
7 dmy 1 0 Nederlands Dutch
8 dmy 1 0 Norsk Norwegian
9 dmy 7 0 Português Portuguese
10 dmy 1 0 Suomi Finnish
11 ymd 1 0 Svenska Swedish
12 dmy 1 0 čeština Czech
13 ymd 1 0 magyar Hungarian

I asked you before to post a Word document containing the actual raw data - pasted into Word. So far you seem to want to do anything but that. You are wasting my time. If you won't do the little you've been asked, to help yourself, I'm not going to bother spending more time on it. I have better things to do.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Attached is the document with the raw data as copy & pasted from Management Studio directly, and nothing done to it.

Attached Files

RawData.docx (10.3 KB, 9 views)