Pasting From MS Access

[SRienstra]

When pasting data including long text or dates, from Access, the default in Excel is to put word wrap on each pasted value (not the column headings) and to format dates as dd-mmm-yy. This is inconvenient to me, as I generally don't want random row heights for pasted data and I never want dd-mmm-yy date format. Is there a way to alter this default behavior?

 

[Joe4]

Not that I know of.

However, you can create a "formatting macro" that you can save to your Personal Macro Workbook in Excel. Then it is always available to you, and you can simply run it whenever you like to correct the formatting of your copied Access data. You can get most of the VBA code you need simply by using the Macro Recorder and recording yourself making these changes.

I use something like this myself to fix the wrapping problems and column widths.

 

[SRienstra]

Is there a way to tell a macro to reformat only those cells that contain dates?

Minor clarification to original post: The dd-mmm-yy formatting is only applied to query results copied from Access, not tables.

I would use the "Keep Destination Formatting" option if it weren't for the leading zeroes that I often have in my data.

 

[Joe4]

Yes, you can use the IsDate function in VBA to determine if a column has date entries. I would just program it to check the first row in each column to determine that.