stanuf
04-07-2011, 06:20 PM
Hello
I have an access database that is used for multiple excel files. The files are set up in the same format...they have a data tab and then a storyboard which has a piviot and chart based on the data tab.
Each month we update the data then users have to open the excel and pull in the new data and refresh the piviot table and charts. I am writing a mod in access that opens each excel file refreshes the data, then the piviot table and chart. It is working all except one small problem. It is changing fomating on the data in the data tab. Specifically it changes the date field to a number field. I cannot figure this out....below is the code. I have tried setting the preserveformating and preservercolumn info to different variations of true and false and nothing works. Also want to note that when I open excel and refresh manually it doesn't change the field format to number it leaves the date as a date.
Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open(Path)
Set ObjXLSheet = ObjXLBook.Worksheets(Spreadsheet)
ObjXLSheet.Select
ObjXLSheet.Activate
ObjXL.ActiveCell.QueryTable.PreserveFormatting = True
ObjXL.ActiveCell.QueryTable.PreserveColumnInfo = True
ObjXL.ActiveCell.QueryTable.Refresh
ObjXL.DisplayAlerts = False
I have an access database that is used for multiple excel files. The files are set up in the same format...they have a data tab and then a storyboard which has a piviot and chart based on the data tab.
Each month we update the data then users have to open the excel and pull in the new data and refresh the piviot table and charts. I am writing a mod in access that opens each excel file refreshes the data, then the piviot table and chart. It is working all except one small problem. It is changing fomating on the data in the data tab. Specifically it changes the date field to a number field. I cannot figure this out....below is the code. I have tried setting the preserveformating and preservercolumn info to different variations of true and false and nothing works. Also want to note that when I open excel and refresh manually it doesn't change the field format to number it leaves the date as a date.
Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open(Path)
Set ObjXLSheet = ObjXLBook.Worksheets(Spreadsheet)
ObjXLSheet.Select
ObjXLSheet.Activate
ObjXL.ActiveCell.QueryTable.PreserveFormatting = True
ObjXL.ActiveCell.QueryTable.PreserveColumnInfo = True
ObjXL.ActiveCell.QueryTable.Refresh
ObjXL.DisplayAlerts = False