Format on New Database Query

Hello Everyone,

I am connecting through Excel to my company's business software via ODBC.

The data that comes out of our business software, well... to say the least, its not very database friendly. Lots of spaces, ALL CAPS (ugh), and requires concatenation on multiple columns.

The data I import will be refreshed nightly from the software, so naturally, when i open up excel, and refresh, its right back to the raw, unformatted data.

I would like to know if at all possible, a way to "lock-in" my commands and formatting, so that when I do refresh the linked data, I automatically impose my formatting on it.

Can someone point me in the right direction?

Thanks,

Hi fncuis,

If you're using a Database Query from the Data menu, then you can
right-click the querytable and select Data Range Properties. Make sure
Preserve Cell Formatting is checked, and look for any other options that may
help you retain your formatting. Sometimes, I will put my querytable on a
hidden worksheet and have another, "presentation" worksheet that massages
the data into the format I want the users to see.

If you're not using a Database Query, then please explain how the data is
getting put into Excel, and we may be able to assist.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


fncuis wrote:
> Hello Everyone,
>
> I am connecting through Excel to my company's business software via
> ODBC.
>
> The data that comes out of our business software, well... to say the
> least, its not very database friendly. Lots of spaces, ALL CAPS
> (ugh), and requires concatenation on multiple columns.
>
> The data I import will be refreshed nightly from the software, so
> naturally, when i open up excel, and refresh, its right back to the
> raw, unformatted data.
>
> I would like to know if at all possible, a way to "lock-in" my
> commands and formatting, so that when I do refresh the linked data, I
> automatically impose my formatting on it.
>
> Can someone point me in the right direction?
>
> Thanks,

Hi,

Thanks for the response. I am trying to retain formulas. The properties window doesn't mention anything about formulas. When I think of formatting, I think of font color, etc...

But what I would like is for the data to be TRIMmed, Concatenated, and have NULL entries replaced w/ NA.

Moreover, I am hoping to impose those formulas on import/refresh. This way I don't have to apply the same silly formulas day-in day-out.

Thanks,

Hi,

I would say your best bet may be to use a totally separate range (or
worksheet) to hold your presentation version of the data. The formulas
there would refer to cells in the querytable. It is a little more difficult
if you don't know the length of your data, but you could use some dynamic
formulas or VBA to control the presentation page's length.

Another option would be to use VBA to scrub the data before putting it into
the workbook. To do that, you'd have to use ADO to call the database and
retrieve the recordset. Then, you could loop through the records and scrub
each field before writing it out.

Another option still would be to modify your SQL query (if you have access
to it) to fix the data before it is sent back to Excel.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


fncuis wrote:
> Hi,
>
> Thanks for the response. I am trying to retain formulas. The
> properties window doesn't mention anything about formulas. When I
> think of formatting, I think of font color, etc...
>
> But what I would like is for the data to be TRIMmed, Concatenated, and
> have NULL entries replaced w/ NA.
>
> Moreover, I am hoping to impose those formulas on import/refresh.
> This way I don't have to apply the same silly formulas day-in day-out.
>
> Thanks,

Woah...

Ok, option 1 I think I can handle... basically, have a second worksheet or second workbook for the presentation side. Do a bunch of linked columns/cells and impose the formatting there? As I refresh the (source) workbook/sheet it automatically carries over.

More work, but It's in my latitude.


Option 3: We use a business software called FACTS. It's not the friendliest program around but allows for ODBC, as I am using it right now. Correct me if I am wrong, but what you are saying is: during the database query, "view data or edit in ms query"? and give it my SQL commands there?

fncuis wrote:
> Woah...
>
> Ok, option 1 I think I can handle... basically, have a second
> worksheet or second workbook for the presentation side. Do a bunch
> of linked columns/cells and impose the formatting there? As I
> refresh the (source) workbook/sheet it automatically carries over.
>
> More work, but It's in my latitude.

Yes, that's what I was suggesting.

> Option 3: We use a business software called FACTS. It's not the
> friendliest program around but allows for ODBC, as I am using it right
> now. Correct me if I am wrong, but what you are saying is: during
> the database query, "view data or edit in ms query"? and give it my
> SQL commands there?

Yeah, since FACTS allows for SQL queries, you could just edit the query in
MS Query. SQL can be a difficult language to do text formatting and such,
and it's not the best place for presentation logic, but you may be able to
find a workable solution and avoid the separate worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]