MS Query and date format

[IgorM]

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

 

[FSt1]

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

 

[FSt1]

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

 

[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.