Prev: Send Unlimited Free sms World Wide
Next: SAS on Linux (found somewhere else )
From: "Data _null_;" on 7 Dec 2009 08:25
This retruns the number of the last row of the USEDRANGE in a sheet.
It returns1 even if the sheet is empty, but I expect that could be
fix.

%let workbook = book2.xls;
%let sheet = Sheet4;


filename FT15F001 temp;
data rowsInExcel(keep=workbook sheet rows);
infile FT15F001 end=eof;
length script filevar workbook $128 sheet $16;
script = catx('\',pathname('work'),'rownum.vbs');
filevar = script;
workbook = symget('WORKBOOK');
sheet = symget('SHEET');
file dummy1 filevar=filevar;
put 'Const ' workbook=$quote130.;
put 'Const ' sheet=$quote18.;
do while(not eof);
input;
put _infile_;
end;
eof = 0;
filevar = catx('\',pathname('work'),'dummy.vbs');
file dummy1 filevar=filevar;
filevar = script;
infile dummy2 filevar=filevar end=eof;

do _n_ = 1 by 1 while(not eof);
input;
putlog _n_ z4. + 1 _infile_;
end;

filevar = catx(' ','cscript //nologo',quote(strip(script)));
infile dummy3 pipe filevar=filevar end=eof truncover;
do while(not eof);
input rows;
putlog _infile_;
output;
end;
stop;
parmcards4;
Const xlCellTypeLastCell = 11
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open(workbook)
Set objWorksheet = objWorkbook.Worksheets(sheet)
objWorksheet.Activate
Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate
wscript.echo objExcel.ActiveCell.Row
objExcel.Quit
;;;;
run;
proc print;
run;



On 12/6/09, Proc Me <procme(a)concept-delivery.com> wrote:
> For an alternative approach, have a look at Chang Chung's comment
> (http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&D=0&X=425EED7CEBA9567EEB&P=30198)
> on exporting data to Excel.
>
> You could extend this approach to use VBA to generate a file with metadata
> about a spreadsheet. In this case you are after the number of observations,
> which, assuming data starts in cell A1 of sheet mySheet is returned with
>
> ActiveWorkbook.Sheets("mySheet").Cells(1, 1).CurrentRegion.Rows.Count
>
> You could get VBA to write this data to a cell in a new workbook or a new
> sheet, or to a text file, which could then be imported into SAS.
>
> I hope this helps,
>
> Proc Me
>
From: "Data _null_;" on 7 Dec 2009 08:52
Small modification to get info on all sheets.

%let workbook = pathToWorkbook;

filename FT15F001 temp;
data rowsInExcel(keep=workbook sheet rows);
infile FT15F001 end=eof;
length script filevar workbook $128 sheet $16;
script = catx('\',pathname('work'),'rownum.vbs');
filevar = script;
workbook = symget('WORKBOOK');
file dummy1 filevar=filevar;
put 'Const ' workbook=$quote130.;
do while(not eof);
input;
put _infile_;
end;
eof = 0;
filevar = catx('\',pathname('work'),'dummy.vbs');
file dummy1 filevar=filevar;
filevar = script;
infile dummy2 filevar=filevar end=eof;

do _n_ = 1 by 1 while(not eof);
input;
putlog _n_ z4. + 1 _infile_;
end;

filevar = catx(' ','cscript //nologo',quote(strip(script)));
infile dummy3 pipe filevar=filevar end=eof truncover;
do while(not eof);
input rows sheet &$32.;
putlog _infile_;
output;
end;
stop;
parmcards4;
Const xlCellTypeLastCell = 11
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open(workbook)
For Each objWorksheet in objWorkbook.Worksheets
objWorksheet.Activate
Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate
wscript.echo objExcel.ActiveCell.Row & " " & objWorksheet.Name
Next
objExcel.Quit
;;;;
run;
proc print;
run;

On 12/7/09, Data _null_; <iebupdte(a)gmail.com> wrote:
> This retruns the number of the last row of the USEDRANGE in a sheet.
> It returns1 even if the sheet is empty, but I expect that could be
> fix.
>
> %let workbook = book2.xls;
> %let sheet = Sheet4;
>
>
> filename FT15F001 temp;
> data rowsInExcel(keep=workbook sheet rows);
> infile FT15F001 end=eof;
> length script filevar workbook $128 sheet $16;
> script = catx('\',pathname('work'),'rownum.vbs');
> filevar = script;
> workbook = symget('WORKBOOK');
> sheet = symget('SHEET');
> file dummy1 filevar=filevar;
> put 'Const ' workbook=$quote130.;
> put 'Const ' sheet=$quote18.;
> do while(not eof);
> input;
> put _infile_;
> end;
> eof = 0;
> filevar = catx('\',pathname('work'),'dummy.vbs');
> file dummy1 filevar=filevar;
> filevar = script;
> infile dummy2 filevar=filevar end=eof;
>
> do _n_ = 1 by 1 while(not eof);
> input;
> putlog _n_ z4. + 1 _infile_;
> end;
>
> filevar = catx(' ','cscript //nologo',quote(strip(script)));
> infile dummy3 pipe filevar=filevar end=eof truncover;
> do while(not eof);
> input rows;
> putlog _infile_;
> output;
> end;
> stop;
> parmcards4;
> Const xlCellTypeLastCell = 11
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = False
> objExcel.DisplayAlerts = False
> Set objWorkbook = objExcel.Workbooks.Open(workbook)
> Set objWorksheet = objWorkbook.Worksheets(sheet)
> objWorksheet.Activate
> Set objRange = objWorksheet.UsedRange
> objRange.SpecialCells(xlCellTypeLastCell).Activate
> wscript.echo objExcel.ActiveCell.Row
> objExcel.Quit
> ;;;;
> run;
> proc print;
> run;
>
>
>
> On 12/6/09, Proc Me <procme(a)concept-delivery.com> wrote:
> > For an alternative approach, have a look at Chang Chung's comment
> > (http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&D=0&X=425EED7CEBA9567EEB&P=30198)
> > on exporting data to Excel.
> >
> > You could extend this approach to use VBA to generate a file with metadata
> > about a spreadsheet. In this case you are after the number of observations,
> > which, assuming data starts in cell A1 of sheet mySheet is returned with
> >
> > ActiveWorkbook.Sheets("mySheet").Cells(1, 1).CurrentRegion.Rows.Count
> >
> > You could get VBA to write this data to a cell in a new workbook or a new
> > sheet, or to a text file, which could then be imported into SAS.
> >
> > I hope this helps,
> >
> > Proc Me
> >
>
First  |  Prev  | 
Pages: 1 2
Prev: Send Unlimited Free sms World Wide
Next: SAS on Linux (found somewhere else )