VB Script help needed

art297 — Mon, 05 Jan 2015 16:36:17 GMT

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

Re: VB Script help needed

art297 — Fri, 09 Jan 2015 21:57:52 GMT

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

topic Re: VB Script help needed in SAS Programming

VB Script help needed

Re: VB Script help needed