Importing a SAS File into Excel Workbook (.sas7bdat)

[Ben M]

Hi,

I use an excel add in to import multiple .sas7bdat files into a workbook every week, sometimes more than once per week. I receive the files by email and if I could just dump them on my desktop and run a macro to import them to exactly where I need it would save me so much time. I decided to record the import of one of these files. I watched the macro record as I clicked. Going to the SAS add in, finding the file etc.. produced nothing until I pressed import and then all of the below appeared. I imported exactly the same file and recorded the macro again and the 972284668 number became something completely different even though it was exactly the same file name so I assume this is some kind of temporary number used during the import process?

Does anyone have any idea if it is possible to set this up referencing the below code if, for example, I want to import the same file from my desktop that will always be called ds_1.sas7bdat.

By the way, when I re-ran the recorded macro it 1004 errored on selection.autofilter, then I blocked that line and it simply added a new blank sheet to my workbook.

I know this is a long shot... thanks for reading

Sub Macro5()

    ActiveWorkbook.Names.Add Name:="_AMO_SingleObject_972284668__A1", _
        RefersToR1C1:="='C  Users user1 Desktop ds_1.sa'!R1C1"
    ActiveWorkbook.Names("_AMO_SingleObject_972284668__A1").Delete
    ActiveWorkbook.Names.Add Name:="_AMO_SingleObject_972284668__A1", _
        RefersToR1C1:="='C  Users user1 Desktop ds_1.sa'!R1C1"
    Selection.AutoFilter
    Selection.ClearComments
    ActiveWorkbook.Names.Add Name:="_AMO_SingleObject_972284668__A1", _
        RefersToR1C1:="='C  Users user1 Desktop ds_1.sa'!R1C1:R1052C38"
    ActiveWorkbook.Names.Add Name:="_AMO_ContentLocation_972284668__A1.0", _
        RefersToR1C1:= _
        "=""'<contentlocation path="" ""a1""""="" rsid="" ""972284668""""="" tag="" """"""="" fid="" ""0""""="">" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <param n="" ""sasdata'"""
     ActiveWorkbook.Names.Add Name:="_AMO_ContentLocation_972284668__A1.1", _
        RefersToR1C1:="=""'End"""" v=""""1051"""" />" & Chr(13) & "" & Chr(10) & "</contentlocation>'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentLocation_972284668__A1", _
        RefersToR1C1:="=""'Partitions:2'"""
    ActiveWorkbook.Names.Add Name:="_AMO_XmlVersion", RefersToR1C1:="=""'1'"""
    ActiveWorkbook.Names("_AMO_ContentLocation_972284668__A1.0").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_972284668__A1.1").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_972284668__A1").Delete
    ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.0", _
        RefersToR1C1:= _
        "=""'<contentdefinition name="" ""c:\users\user1\desktop\ds_1.sas7bdat""""="" rsid="" ""972284668""""="" type="" ""dataset""""="" format="" ""reportxml""""="" imgfmt="" ""activex""""="" created="" ""04="" 14="" 2018="" 17:22:50""""="" modifed="" user="" ""matin,="" ben""""="" apply="" ""false""""="" css="" ""c:\'"""
     ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.1", _
        RefersToR1C1:= _
        "=""'Program Files\SASHome\x86\SASAddinforMicrosoftOffice\5.1\Styles\AMODefault.css"""" range=""""C__Users_user1_Desktop_ds_1_sas_5"""" auto=""""False"""" xTime=""""00:00:00.0010000"""" rTime=""""00:00:00.3990000"""" bgnew=""""False"""" nFmt=""""False"""" grphSet=""""False"""" img'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.2", _
        RefersToR1C1:= _
        "=""'Y=""""0"""" imgX=""""0"""">" & Chr(13) & "" & Chr(10) & "  <files>" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <children>" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <param n="" ""datasourc'"""
     ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.3", _
        RefersToR1C1:= _
        "=""'eType"""" v=""""SAS DATASET"""" />" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <p'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.4", _
        RefersToR1C1:= _
        "=""'aram n=""""CredKey"""" v=""""C:\Users\user1\Desktop\ds_1.sas7bdat"""" />" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <param n="" ""datasource""""="" v="" ""<sasdatasource="" version=""4.2"" type=""'"""
     ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.5", _
        RefersToR1C1:= _
        "=""'SAS.Servers.Dataset" FilterDS="<?xml version="1.0" encoding="utf-16"?><FilterTree><TreeRoot /></FilterTree>" ColSelFlg="0" Name="'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.6", _
        RefersToR1C1:= _
        "=""'C:\Users\user1\Desktop\ds_1.sas7bdat" />"""" />" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <param '"""
     ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668.7", _
        RefersToR1C1:= _
        "=""'n=""""ObsColumn"""" v=""""true"""" />" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <excelxmloptions adjcolwidths="" ""true""""="" rowopt="" ""insertcells""""="" colopt="">" & Chr(13) & "" & Chr(10) & "</excelxmloptions></p'"""
</children></parents></files></contentdefinition>'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentDefinition_972284668", _
        RefersToR1C1:="=""'Partitions:8'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentLocation_972284668__A1.0", _
        RefersToR1C1:= _
        "=""'<contentlocation path="" ""a1""""="" rsid="" ""972284668""""="" tag="" """"""="" fid="" ""0""""="">" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  
" & Chr(13) & "" & Chr(10) & "  <param n="" ""sasdata'"""
     ActiveWorkbook.Names.Add Name:="_AMO_ContentLocation_972284668__A1.1", _
        RefersToR1C1:="=""'End"""" v=""""1051"""" />" & Chr(13) & "" & Chr(10) & "</contentlocation>'"""
    ActiveWorkbook.Names.Add Name:="_AMO_ContentLocation_972284668__A1", _
        RefersToR1C1:="=""'Partitions:2'"""
End Sub