MS Query and date format

  • Thread starter Thread starter IgorM
  • Start date Start date
I

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
 
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
 
hi
sorry. made a mistake.
line in the code....
Sheets("Sheet1").range("A1").Refresh BackgroundQuery:=False
Click to expand...
should be....
Sheets("Sheet1").range("A1").Querytable.Refresh BackgroundQuery:=False

sorry bout that
regards
FSt1
 
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.
 
Back
Top