What is the correct VBA PasteSpecial Format - copy from Access to Excel?
Posted on 2011-02-14
I have an database that was developed in Access 2003.
I export query results to a temp Excel file and copy the contents of the temp file to a pre-formatted Excel workbooks using VBA.
This code worked perfectly for several years using Access 2003.
I then upgraded to Office 2010 - however the PasteSpecial stopped working properly and did a straight paste and overwrote my formatting.
I then edited the PasteSpecial lines to:
xlObj.activesheet.PasteSpecial Paste:=xlPasteValues (which i took from a recorded Excel macro)
However, the code now fails with a Run time 1004 error