Format on New Database Query
 

Hello Everyone,

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

The data that comes out of our business software, well... to say th
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, s
naturally, when i open up excel, and refresh, its right back to th
raw, unformatted data.

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

Can someone point me in the right direction?

Thanks

--
fncui
-----------------------------------------------------------------------
fncuis's Profile: http://www.excelforum.com/member.php...fo&userid=2646
View this thread: http://www.excelforum.com/showthread.php?threadid=39732

Format on New Database Query
 

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,

Format on New Database Query
 

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,


--
fncuis
------------------------------------------------------------------------
fncuis's Profile: http://www.excelforum.com/member.php...o&userid=26466
View this thread: http://www.excelforum.com/showthread...hreadid=397329

Format on New Database Query
 

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,

Format on New Database Query
 

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
------------------------------------------------------------------------
fncuis's Profile: http://www.excelforum.com/member.php...o&userid=26466
View this thread: http://www.excelforum.com/showthread...hreadid=397329

Format on New Database Query
 

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]