A macro included in a paper a group of us wrote last year write and runs some VB script in order to paste SAS data into Excel (see: A Poor/Rich SAS Users Proc Export - sasCommunity ).
Given a case where one is creating a new workbook (c:\temp\test\tclass1.xlsx), with one worksheet called N1, has copied the data from sashelp.class into their system's clipboard, and wants to paste the data into that worksheet, the macro creates and runs the following VB script:
Dim objExcel
Dim Newbook
Dim NewSheet
Dim inSheetCount
Set objExcel = CreateObject("Excel.Application")
Set Newbook = objExcel.Workbooks.Add()
objExcel.Visible = True
inSheetCount = Newbook.Application.Worksheets.Count
set NewSheet = Newbook.Sheets.Add( ,objExcel.WorkSheets(inSheetCount))
objExcel.DisplayAlerts = False
i = inSheetCount
Do Until i = 0
Newbook.Worksheets(i).Delete
i = i - 1
Loop
Newbook.Sheets(1).Name="N1"
Newbook.Sheets("N1").Select
Newbook.Sheets("N1").Range("A2").Activate
Newbook.Sheets("N1").Paste
NewSheet.SaveAs("c:\temp\test\tclass1.xlsx")
objExcel.Workbooks.Close
objExcel.DisplayAlerts = True
objExcel.Quit
However, the script is failing for some users, in that Excel is returning the following error: Paste method of Worksheet class failed. We tried changing Newbook.Sheets("N1").Paste to Newbook.Sheets("N1").PasteSpecial, but that didn't correct the problem.
Any suggestion(s) on what should be changed to the VB script in order to get around that error?
Art
While no one here responded to my question, I did get some help from the folks over on the SAS-L listserv.
For anyone who might face a similar problem, the problems turns out to be that some versions of Excel obliterate one’s clipboard upon opening. As such, we had to modify the code to accommodate users who confront such a problem.
The solution can be found in our latest revision to the code at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export
The solution, basically, was rather than copy to one’s clipboard, write the data to a file, and then in one vb script, open Excel, open the file in Notepad, copy the text, and then paste it into Excel. A bit clumsy, maybe, but it only took a few minutes to incorporate the code, only affects users who need the work around, and lets more people benefit from the macro’s capabilities.
If you aren’t aware of the macro, it lets one export data to Excel, include or exclude a row of variable names or labels, use or not use formatted data, copy data to a non-pre-defined range, take advantage of Excel templates, and both create new workbooks, add a new worksheet, or modify an existing worksheet.
Art
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.