Macro Keeps changing date to American format

[anne.gomes]

Hi,

I have a macro which has the correct date format on the pivot charts but when I run the macro and turn it into a PDF the charts now have the american date format which gets changed automatically.

How can I prevent this from happening?

Thank you in advance

[werafa]

This might be due to your regional settings (set in the operating system

[westconn1]

but when I run the macro and turn it into a PDF

are you using a macro to create the pdf?
if so post the code

[anne.gomes]

yes I am westconn1, this is it

Sub saveToPdf(filename)
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Users\anneg\Documents\temp\" & filename & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
End Sub

I have the date form for region set but when pdf is created it always comes out in US date form. On the pivot table and source data the date is correct and is set to regional date form of NZ but the chart dates always gets changed.

I have tried going into data and text to columns and setting date to DMY and when I refresh the chart date is changed to correct format but when the macro is run then it goes back to the US format.

And when I right click the x-axis where the date is and try changing the format it doesn't change.

Any ideas? I'm completely stumped!

[werafa]

Confirm OS region settings
Confirm settings in Excel - look for region and print settings
confirm settings in PDF print driver - check everything pdf related if not sure.

alternatively, convert the date to a string pre-print and then convert back post print.
I have excel do some helpful formatting that inserts weird non-printing characters into the formatting, and my only sollution is to strip values back to raw strings and convert back to standard excel formatting. This is from a cube connection where the input data is completly raw and unformatted, and the formattng supplied by the ever helpful excel cannot be changed without macros.

[anne.gomes]

thanks for that, how do I convert date to a string pre-print?

[werafa]

Set the correct format in your source data first. Copy and paste text might work. macro based read/write value2 is more certain.
then specify the correct format in your source data - or convert to a string here as your first attempt. you can force this issue by swapping / and \ as your date seperators.
remove any formatting you have tried to set in the pivot table and pivot chart - then update the pivot table/chart
this might work.

Jon Peltier has a very important blog at http://peltiertech.com/WordPress/ref...ranges-in-vba/

use this to set the format properties in the underlying pivot table - the macro recorder is only parially useful here
I'm not much help with actual code as I haven't done this for a while now - but I do feel your pain

[anne.gomes]

Nope still no luck. The Dates on the Source Data and Pivot table change but not the Pivot Chart connected to the Pivot Table

[werafa]

Try a brand new workbook (I know - painful, but sometimes it works)
Copy the raw data
build new pivot table
Build new chart

try new workbook formats of both xls and xlsx, just to be silly

also, are you trying to save as pdf or print to pdf? can you try an after-market pdf printer driver?
what happens if you export the chart as a jpeg file?
what happens if you save the document as an XML, XPS or html file? (this might exhonerate excel as well as giving you a way to convert to pdf)

[anne.gomes]

okay so this code

ChDir "C:\Users\anneg\Documents\temp"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Users\anneg\Documents\temp\Basware Daily Report Data.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

saves with correct format but what I have currently

Sub saveToPdf(filename)
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Users\anneg\Documents\temp\" & filename & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
End Sub

gives me US Date Format.

I don't understand?

[werafa]

diference is activesheet n activeworkbook.

try setting

dim mySheet as worksheet
dim myWB as workbook

set mySheet = activesheet
set myWB = activeworkbook

and rewrite the code to use the objects
you can also pause and check the object properties, as well as set watches.

also, very seriously try a print to pdf using a pdf printer driver

[anne.gomes]

I think the problem occurs when I copy out selected worksheets onto another workbook and then save that into a PDF

 graphTabList = Array( _
                                    "BU Queue Analysis", _
                                    "BU Aged Analysis WIP", _
                                    "BU Respondants Analysis", _
                                    "BU In Workflow Analysis", _
                                    "BU Supplier Analysis" _
                                     )
                                    
Sub saveToPdf(filename)
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Users\anneg\Documents\temp\" & filename & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
End Sub

Sub copyOut(filename)
'
' copyout Macro
'
'
 
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:="C:\Users\anneg\Documents\temp\" & filename & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
       
        Windows("Basware Daily Report Data.xlsm").Activate
        Sheets("TitlePage").Copy After:=Workbooks(filename & ".xlsx").Sheets(1)
    
    'Copy out graphs
    For Each graphTab In graphTabList
        Windows("Basware Daily Report Data.xlsm").Activate
        Sheets(graphTab).Copy After:=Workbooks(filename & ".xlsx").Sheets(1)
    Next graphTab


    Sheets(graphTabList).Select
Call saveToPdf(filename)

When it copies the worksheets the dates turn from NZ form to US form. How can I prevent this?

[westconn1]

still sounds like regional settings

try in a new worksheet, enter an unambiguous date, is it in the correct format?

How can I prevent this?

specify the numberformat of the specific column(s)

[anne.gomes]

hey, I looked at the copied worksheet and the region is set to NZ Date format but is actually showing up in US format. The original .xlsm doc is in NZ date format but something in the code converts it to default when copied into new worksheet. But when I manually right click worksheet and copy it to a new worksheet it keeps the NZ date format. Only when the macro runs does it change it to the US format.

[werafa]

It seems that you have already answered this - apologies if I am the one on the late bus.
but to confirm beyond doubt, can you:

go to the language settings in excel
- confirm that you do have English (NZ) on your list of available languages, and that this is set as default (sorry for 'shouting' - but make absolutely sure - it is not enough just to have it available)
- delete US english as a language option.

In your format cells/number format dialogue, do you have a 'locale/location' option? what is this set to? This is set by the windows locality settings - you can't change it in excel as far as I know.

The next bright idea is to set a custom number format - set this by copying the desired formatting, and then do it slightly differently. so dd/mm/yyyy could become dd/mm/yyyy " ". This shouldn't be recognised as a standard date and should therefore get through unscathed. It will stop you from using a date based axis on your charts - but this will only be a problem for asyncronous date ranges

[westconn1]

interesting!!
on doing a bit of testing in my version of excel
english australian
regional settings correct

i enter a date in cell B2 (02/03/2014), which can be considered ambiguous, either value could be month or day
no formatting done to cell
i copy sheet to same workbook or new workbook, date remains the same
i test the date and numberformat

?month(range("b2"))
3
?range("b2").NumberFormat
m/d/yyyy

so while it recognises the correct (by me) month
the number format still shows as US
even using an unambiguous (15/02/2014) numberformat is still US

based on those results, i feel specific cell formatting essential

try like

For Each graphTab In graphTabList 
        Windows("Basware Daily Report Data.xlsm").Activate 
        Sheets(graphTab).Copy After:=Workbooks(filename & ".xlsx").Sheets(1) 
        Workbooks(filename & ".xlsx").Sheets(2).range("g:g").numberformat = "dd/mm/yyyy"
    Next graphTab

change column(s) (ranges) to suit

why, with all the formats in the dateformat list excel does not include dd/mm/yyyy is a mystery to me, of course some compatible date formats are available, such as dd-mmm-yyyy

or if the original sheet has the numberformat specifically set, the format should be copied with the sheet

[Rob342]

Anne
Had this same problem with converting to US date format, even with the cells formatted as English it was still converting to US
Fixed it using this code whenever it was writing dates back to a sheet
[CODE]
.cells(IRow, "P").Value = CLng(CDate(Me.TxtJCDate.Value))
.Range("P" & IRow).NumberFormat = "DD/MM/YYYY"
[/CODE]
Change your code to suit
Rob