MS Query and date format

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
 
F

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
 
F

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
 
I

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top