Data Format when exporting from Access to Excel 97-2003

[lopiteaux]

Hi all,

I'm having a few problems when exporting a dataset from Access to Excel (both 2003 - not by choice) and I was hoping someone on this forum could help, seeing as I've pretty much hit dead ends everywhere else.

Basically the central them of my question is this: how can I set output formats for the data when exporting from an Access table to Excel 1997-2003? The best example to point out in this case would be the date, which - no matter what format it's entered in in Access, or which format the field properties are set to - comes out as dd-mmm-yy. In this case, I'd like to have the data in short date format, i.e. dd/mm/yyy.


Currently I'm exporting the table through a macro - built in the noob macro builder inherent in Access, i.e. no digging in the coding behind the scenes (I'm pretty adept at VBA in Excel, but have yet to tackle this in Access... welcome to take on any ideas and/or advice on how to better this export function in pure hard black & white code!)... so yes, as I was saying, the export is done via an 'OutputTo' function in the macro builder, with the following parameters:

OutputTo
Object Type = Table
Object Name = X
Output Format = Microsoft Excel 97-2003 (*.xls)
Output File = X
Auto Start = Yes

The date format is the most important setting for the time being, but I'd love to know how I can program the Query (either in Design view or by code, although I have no experience coding in Access or SQL) to do the following:

1) Export dates as "dd/mm/yyyy";
2) Export dataset sorted by a particular column;
3) Export the dataset as a .csv and not .xls;

4) Last but definitely not least, format cells to a specified font, specified colour, without text-wrapping, and autofitted along the columns.

Any thoughts and help would be greatly appreciated!

Thanks,

Carel.​

 

[lubbs65]

I tried doing something similiar but wasn't able to find a solution. I ended up creating a macro to output all the data in .txt format and sine I know C++ i wrote a program that took all outputted files, manipulated data appropriately, then my c program outputted a text file with everything the way i want. I had a macro in excel grab that txt file.

If you aren't trying any manipulations worst case make 2 macros which kind of sucks. one that outputs the file into a determined location. then open your standard excel file and have a macro in this excel file open this file that is outputted from access.

Now if access refuses to output in the right format (for whatever reason) create a third macro using record highlight all the dates (if you will always have the same number of dates in the same location then just highlight those cells and you'll be fine. if you will have a limit, you can just highlight more than enough rows or columns if neccessary, or you could probably use formulas to define the area you want to change) now with these dates go to the home ribbon->numbers->date-> and change it to the date format you want and then excel will change the date from one format to the other.

once this third macro is correct take all the code and just add it into the first excel macro. that way when you call the first macro in excel you'll both infile and manipulate the dates.

 

[lopiteaux]

Yeah I also want to avoid the use of two macros... isn't it possible to export straight with a query, with the formatting applied behind the scenes?

 

[lubbs65]

there probably is a way, but I'm not aware of it. I ended up using the method I described for something except it was more intensive.

 

[xenou]

As far as I know, you get the formatting you get when you output with OutputTo. Ditto if you use the TransferSpreadsheet method. Best is not to worry about it ... a date is a date is a date no matter how it's formatted and your data is correct. Let the end user worry about how it looks if it bothers them (and you yourself have the luxury of being indifferent).

As stated, your only option would be to exercise more control over the output by going to further lengths (writing [vba] code to export in a scripted step by step process that will manipulate the final result as desired, or exporting to CSV to get a relatively format-free file).

Anyone with more info on this is welcome to add their thoughts!