Link to home
Start Free TrialLog in
Avatar of radiantripack
radiantripack

asked on

What is the correct VBA PasteSpecial Format - copy from Access to Excel?

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.
...
xlObj.ActiveWorkbook.sheets("On Hold").select
xlObj.activesheet.range("A1").select
xlObj.activesheet.PasteSpecial Format:=2

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





SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of radiantripack
radiantripack

ASKER

Hi Capricorn1

I add the references and now have a line as follows:
xlObj.activesheet.PasteSpecial Paste:=xlPasteValues

I'm still getting the Run Time error 1004
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial