Excel Get External Data 3

[MJamison07]

I have a spreadsheet that gets data from an ODBC source. If a date field is blank in the source, it appears as 1/0/1900 in the excel sheet. Is there any way for me to make it appear as a blank cell in the excel sheet instead of 1/0/1900?

Also, when I refresh the data it changes the column width. Is there a way I can keep the column width fixed when I refresh?

Thank you,

Martha

 

[arachnoid]

You could eliminate the date issue with an if(iserror( formula.

As for your column width issue, you could write a small macro to adjust the column width on a refresh. If you dont know VBA, then you can simply record the macro.

 

[MJamison07]

Where would I put the iserror formula? excel or source?

 

[SkipVought]

In Excel. Check out this FAQ...

Why are Dates and Times so much trouble? faq68-5827


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[HughLerwill]

If you are setting up connection to your data via Data menu - Import External data - Import data you should check out the Properties of the connection, under Data formatting and layout there is the 'Adjust column widths' option. My ref Excel 2002.

regards Hugh,

 

[SkipVought]

Hugh,

This is NOT Import data, it a QUERY.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[HughLerwill]

Skip,

Sorry I thought it may have been of the

Workbooks("MyBook").QueryTables.Add(Connection:blah...

variety. You can then do

.AdjustColumnWidth = False

to the QueryTable

regards Hugh

 

[SkipVought]

It's not column width, it's an empty value in a date field. ZERO date is 1/0/1900.

Why are Dates and Times so much trouble? faq68-5827

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[MJamison07]

Thanks for your help. Since I can't make it go away, I just used the excel date info you gave me and developed a fix. Using conditional formatting I made the font in the cells that contain the 0 date info white. It's still there but it doesn't show when I print (on white paper anyway).

Thanks.
Martha

 

[SkipVought]

The trick is understanding the data and what your options are.

GOOD SOLUTION! ;-)

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}