topic Re: use if tab exist then delete in VBA SAS in SAS Programming

use if tab exist then delete in VBA SAS

sam1231 — Thu, 22 Aug 2019 03:25:01 GMT

Put "objexcel.sheets(""Sheet1"").Delete";

I WANT TO USE IF TAB EXIST THEN DELETE.

Please can some one help me?

Re: use if tab exist then delete in VBA SAS

ChrisNZ — Sun, 25 Aug 2019 23:10:48 GMT

A VBA script has nothing to do with SAS

You'l probably have more luck asking a purely VBA question on a Microsoft forum.

Re: use if tab exist then delete in VBA SAS

DWilson — Mon, 26 Aug 2019 14:06:44 GMT

@ChrisNZ wrote:
A VBA script has nothing to do with SAS
You'l probably have more luck asking a purely VBA question on a Microsoft forum.

With SAS' move away from DDE, posts asking for help with DDE lead to replies saying, "Don't use DDE, that's old technology, it's unsupported, and risky" and saying "use the SAS VBA add-in instead if you are trying to do things you used to do with DDE."

I think this forum is a fine place for people to ask for VBA help now that SAS has pushed VBA as the alternative to DDE. Would you tell posters in the past who asked about DDE, before it was put to pasture, to go to a Microsoft forum? I would hope not.

My recommendation would be to do a google search first as his question isn't SAS-specific but I wouldn't direct him away from posting on this forum.

Here's some code that may get him pointed in the right direction:

(lifted gratuitously from stackoverflow: https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists)

Function CheckIfSheetExists(SheetName As String) As Boolean

CheckIfSheetExists = False

For Each WS In Worksheets

If SheetName = WS.name Then

CheckIfSheetExists = True

Exit Function

End If

Next WS

End Function

Re: use if tab exist then delete in VBA SAS

ChrisNZ — Mon, 26 Aug 2019 22:35:56 GMT

@DWilson

> I think this forum is a fine place for people to ask for VBA help now that SAS has pushed VBA as the alternative to DDE. Would you tell posters in the past who asked about DDE, before it was put to pasture, to go to a Microsoft forum? I would hope not.

We'll have to agree to disagree.

I responded after no reply was given for 4 days. VBA skills are plentiful on MS forums (of course) and rare here.

There is no doubt that an answer would have been swiftly given on these forums.

Same thing for DDE questions involving MS scripts. Or for VBS questions.

So the best advice imho is the advice I gave.

Re: use if tab exist then delete in VBA SAS

DWilson — Tue, 27 Aug 2019 14:10:31 GMT

@ChrisNZ wrote:
@DWilson
> I think this forum is a fine place for people to ask for VBA help now that SAS has pushed VBA as the alternative to DDE. Would you tell posters in the past who asked about DDE, before it was put to pasture, to go to a Microsoft forum? I would hope not.

We'll have to agree to disagree.
I responded after no reply was given for 4 days. VBA skills are plentiful on MS forums (of course) and rare here.
There is no doubt that an answer would have been swiftly given on these forums.
Same thing for DDE questions involving MS scripts. Or for VBS questions.
So the best advice imho is the advice I gave.

Sure. I wholeheartedly agree that VBA skills are plentiful on MS forums and rarer here. But I think SAS put themselves into the position of expanding the set of SAS users who need VBA because they've pushed a change to their technologies that put the onus on VBA to fill the gap left by their removal of DDE support. It shouldn't be unexpected to see more VBA questions here.

I suspect this is more of a generational change (younger folks opting to use VBA) in SAS use.

I also suspect that the set of SAS users/business that need the level of fine-grained control over excel reports is a relatively small fraction over the set of SAS users who produce reports with Excel. This is also a reason why most posters seem to not care about the deprecation of DDE support with SAS (not to mention the loss of DDE support with base SAS for the SAS grid environment being pushed by SAS.)

p.s. Fwiw, the removal of DDE support without providing a concomitant replacement functionality is the windmill to my Don Quixote. So, pre- and post- apologies if I come across as earnest. If SAS simply added the ability to write to a single cell in an existing excel spreadsheet without touching the other cells in the spreadsheet (e.g. supporting the use of table templates in excel) then that would address my concern. Asking users to learn VBA and pull data into Excel from SAS to replicate this functionality is silly. A multi-billion dollar company should be able to do something better.