I am using SAS (via the %sysfun function and x statement) to open a file using excel. The following code works perfectly so far:
data _null_;
file "%sysfunc(pathname(work))\reg.vbs";
put 'Dim gobjExcel';
put 'Set gobjExcel=CreateObject("Excel.Application")';
put 'gobjExcel.visible=true';
put 'gobjExcel.Workbooks.Open "http://www.sec.gov/Archives/edgar/data/1000015/000104746903010821/a2105195z10-k.htm"';
put 'gobjExcel.Cells.select';
put 'gobjExcel.Selection.MergeCells=False';
put 'gobjExcel.Columns("G:G").Select';
The code up until the above works; however, the TextToColumns method fails...
here is what VB code generated by excel looks like:
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)
So I tried to use this inside a put statement in SAS and it failed:
put 'gobjExcel.Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)';
run;
I thought perhaps the spaces, ":=" and _ created problems so I modified the code as follows:
put 'gobjExcel.Selection.TextToColumns(Range("G1"),xlDelimited,xlDoubleQuote,False,True,False,False,False,False,False,Array(1, 2))';
and it still failed...any ideas? If you do not know SAS you may have problems with this...
Joey
data _null_;
file "%sysfunc(pathname(work))\reg.vbs";
put 'Dim gobjExcel';
put 'Set gobjExcel=CreateObject("Excel.Application")';
put 'gobjExcel.visible=true';
put 'gobjExcel.Workbooks.Open "http://www.sec.gov/Archives/edgar/data/1000015/000104746903010821/a2105195z10-k.htm"';
put 'gobjExcel.Cells.select';
put 'gobjExcel.Selection.MergeCells=False';
put 'gobjExcel.Columns("G:G").Select';
The code up until the above works; however, the TextToColumns method fails...
here is what VB code generated by excel looks like:
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)
So I tried to use this inside a put statement in SAS and it failed:
put 'gobjExcel.Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)';
run;
I thought perhaps the spaces, ":=" and _ created problems so I modified the code as follows:
put 'gobjExcel.Selection.TextToColumns(Range("G1"),xlDelimited,xlDoubleQuote,False,True,False,False,False,False,False,Array(1, 2))';
and it still failed...any ideas? If you do not know SAS you may have problems with this...
Joey