Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as #####.This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:
> > Where can I change the default Column With Dave?|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/excel/HA010548151033.aspx
http://support.microsoft.com/kb/214123
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?