Hi

I have a csv file that I try to link with Excel spreadsheet using MS Query.
I have one issue: the date format in MS Query is incorrect. MS Query imports 
a column as YMD when it should be DMY.
How can I change this?

Kind regards
IgorM 

hi
MS Query is good but sometimes you may have to do a tad bit of clean up 
after the refresh.
how are you refreshing now?
all of my MSQ's refreshed from a command button on the sheet. if i had your 
problem i might do something like this......

Sub RefreshData()
Sheets("Sheet1").range("A1").Refresh Backgroundquery:=False
Range("G:G").NumberFormat = "mm/dd/yyyy"
'or
'Range("G:G") = Format(Range("G:G"), "mmddyyyy")
Msgbox "i'm done"
end sub

the above would work only if you import was true numbers.
if the MSQ is importing the data as text you can use text to column to flip 
it around.
parse it as delimited and on step 3,in column data format(upper right) 
choose date and from the drop down, choose YMD. if you refresh frequently 
then you might want to do that on record and add the code to your refresh 
code. 

my thoughts
regards
FSt1

"IgorM" wrote:

> Hi
> 
> I have a csv file that I try to link with Excel spreadsheet using MS Query.
> I have one issue: the date format in MS Query is incorrect. MS Query imports 
> a column as YMD when it should be DMY.
> How can I change this?
> 
> Kind regards
> IgorM 
> 

hi
sorry. made a mistake.
line in the code....
> Sheets("Sheet1").range("A1").Refresh BackgroundQuery:=False
should be....
Sheets("Sheet1").range("A1").Querytable.Refresh BackgroundQuery:=False

sorry bout that
regards
FSt1

"FSt1" wrote:

> hi
> MS Query is good but sometimes you may have to do a tad bit of clean up 
> after the refresh.
> how are you refreshing now?
> all of my MSQ's refreshed from a command button on the sheet. if i had your 
> problem i might do something like this......
> 
> Sub RefreshData()
> Sheets("Sheet1").range("A1").Refresh Backgroundquery:=False
> Range("G:G").NumberFormat = "mm/dd/yyyy"
> 'or
> 'Range("G:G") = Format(Range("G:G"), "mmddyyyy")
> Msgbox "i'm done"
> end sub
> 
> the above would work only if you import was true numbers.
> if the MSQ is importing the data as text you can use text to column to flip 
> it around.
> parse it as delimited and on step 3,in column data format(upper right) 
> choose date and from the drop down, choose YMD. if you refresh frequently 
> then you might want to do that on record and add the code to your refresh 
> code. 
> 
> my thoughts
> regards
> FSt1
> 
> "IgorM" wrote:
> 
> > Hi
> > 
> > I have a csv file that I try to link with Excel spreadsheet using MS Query.
> > I have one issue: the date format in MS Query is incorrect. MS Query imports 
> > a column as YMD when it should be DMY.
> > How can I change this?
> > 
> > Kind regards
> > IgorM 
> > 

The data is returned to a PivotTable which is set to refresh on file 
opening.
So, I'd rather want to set the year format before the data is imported by 
MSQuery.

"FSt1" <FSt1@discussions.microsoft.com> wrote in message 
news:53010346-1DFD-4869-B42F-2ED1561068F4@microsoft.com...
> hi
> sorry. made a mistake.
> line in the code....
>> Sheets("Sheet1").range("A1").Refresh BackgroundQuery:=False
> should be....
> Sheets("Sheet1").range("A1").Querytable.Refresh BackgroundQuery:=False
>
> sorry bout that
> regards
> FSt1
>
> "FSt1" wrote:
>
>> hi
>> MS Query is good but sometimes you may have to do a tad bit of clean up
>> after the refresh.
>> how are you refreshing now?
>> all of my MSQ's refreshed from a command button on the sheet. if i had 
>> your
>> problem i might do something like this......
>>
>> Sub RefreshData()
>> Sheets("Sheet1").range("A1").Refresh Backgroundquery:=False
>> Range("G:G").NumberFormat = "mm/dd/yyyy"
>> 'or
>> 'Range("G:G") = Format(Range("G:G"), "mmddyyyy")
>> Msgbox "i'm done"
>> end sub
>>
>> the above would work only if you import was true numbers.
>> if the MSQ is importing the data as text you can use text to column to 
>> flip
>> it around.
>> parse it as delimited and on step 3,in column data format(upper right)
>> choose date and from the drop down, choose YMD. if you refresh frequently
>> then you might want to do that on record and add the code to your refresh
>> code.
>>
>> my thoughts
>> regards
>> FSt1
>>
>> "IgorM" wrote:
>>
>> > Hi
>> >
>> > I have a csv file that I try to link with Excel spreadsheet using MS 
>> > Query.
>> > I have one issue: the date format in MS Query is incorrect. MS Query 
>> > imports
>> > a column as YMD when it should be DMY.
>> > How can I change this?
>> >
>> > Kind regards
>> > IgorM
>> >