Open SAS Enterprise Guide 7.1 egp file using Excel VBA

Question

Does anyone know how I can open a SAS EG 7.1 egp file using Excel VBA? Any help would be much appreciated.

score 1 · Answer 1 · answered Jan 28 '19 at 18:52

It is much easier to deal with as C#. I have to disagree with Joe here but I don't think any part of VBA is .NET: it is old COM code.

Let me go through how to handle:

Rename the EGP file to a .zip extension. That will let you look at it to understand the structure.
Use .NET Core (2.2 is latest) and Visual Studio Code (free). Core will allow you to run it on any system and is MS' direction.
Look up how to read a zip file using C#. Here is some code to get you started:

       private XDocument ReadZipFile(string testFile, bool writeFile)
        {
            var xd = new XDocument();
            using (FileStream zipToOpen = new FileStream(testFile, FileMode.Open))
            {
                using (var archive = new ZipArchive(zipToOpen, ZipArchiveMode.Read))
                {
                    var entry = archive.Entries.First(x => x.Name.ToLower() == "project.xml");
                    using (var sr = new StreamReader(entry.Open(), Encoding.UTF8))
                    {
                        var xmlFile = sr.ReadToEnd();
                        if (writeFile)
                        {
                            using (var sw =
                                new StreamWriter(@"X:\Data\projects\Savian.SasWorkflowReader\Data\project.xml"))
                            {
                                sw.Write(xmlFile);
                            }
                        }
                        xd = XDocument.Parse(xmlFile);
                    }
                }
            }
            return xd;
        }

Parse it once it is in text format using RegEx.

VBA uses similar syntax structure as VB.Net, though you're right that behind the scenes it's not actually .Net (it's VB6). I didn't mean to imply it was actually .Net behind the scenes, i'll rewrite that. — Joe, Jan 28 '19 at 19:10
That said - I don't *think* OP wants to actually parse the file, I think OP wants to open-as-in-run the EGP (so, issue commands to Windows similar to how the .vbs that is used in scheduling.) @OP, please clarify if that's not the case. — Joe, Jan 28 '19 at 19:12
VBA has only surface similarities to VB.NET but i understand what you are saying. In the .NET world, C# is the dominant language. Using VB out of comfort is a bad idea IMO. Harder to find code samples and help. Go C# for this if you are going to dive in. If the goal is to issue commands similar to EG, that is more than doable. Lots of examples of doing that but again, most examples are in C#. An EG add-in can do that, if that is the goal. — AlanC, Jan 28 '19 at 23:51

Joe · Answer 2 · 2019-01-28T19:10:51.137

Enterprise Guide is scriptable using .Net, which is fortunate as VBA also uses similar syntax to .Net for the most part, though it's not actually .Net. You may have an easier time using VB.Net or Powershell than VBA, but the principle should be at least roughly the same if you're aware of how to call .Net from Excel/VBA (see this question/answer for starters). (I highly recommend Powershell, as it's easy to learn and very much suited for this sort of thing.)

You can get a quick start with Chris Hemedinger's guide, though that's quite old (I think that's 4.3), but the concepts and names should still be at least (mostly) the same.

You can also schedule a task inside Enterprise Guide using the windows scheduler (File-> Schedule [projname]). What it does when you tell it to do that is create a .VBS file (visual basic script), which you can then open to see how it works, and cannibalize the code for your own purposes.

Open SAS Enterprise Guide 7.1 egp file using Excel VBA

2 Answers2