MS Query and date format
 

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

MS Query and date format
 

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

MS Query and date format
 

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

MS Query and date format
 

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" wrote in message
...
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