' MacroName: MonthlyScatToExcel
' MacroDescription: Get the Monthly Statistics by Item Scat 
'                   report, and push the data into an Excel 
'                   spreadsheet
' Macro written by: Joel Hahn, Niles Public Library District

'Note: Requires MS Internet Explorer and Excel.  Altering for
'Netscape & some other spreadsheet program is technically 
'feasible, but I'm not sure of what all of the needed object
'methods would be, nor how they work.

'Note: Currently optimized for members of the CCS consortium,
'whose statistical reports can be retrieved from a web site.
'Other users may have to strip off the IESide program block--
'un-commenting the Goto ExcelSide should do the trick nicely--
'and manually use PfW's file capture to turn their Libs+ Monthly 
'Statistics by Item Scat report into an electronic file 
'readable by the ExcelSide program block.

'Note: Currently set up for a library with two Libs+ agencies,
'such as one for the main library and a second for a branch or
'bookmobile.  The macro should be easy to alter to deal with
'only one or many agencies, as needed.

'Note: You have to save the final Excel spreadsheet yourself;
'you'll probably want to examine it anyway and choose your own
'filename for the finished product.

sub main

Agy$="##1"     'For CCS libraries: Agency to start with
Auth$="######" 'And authorization code for that agency

  Dim IE As Object
  Dim XL As Object

  site1$ = "http://ccs1.ccs.nsls.lib.il.us:8080/cgi-bin/private/arcrptfile?rpt=s12&agy="
  site2$ = "&day="
  RptDay$ = "01"
  site3$ = "&auth=" + Auth$

'Goto ExcelSide
IESide:
  'Start the Internet Explorer and make it visible
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Visible = True
  AppActivate "Microsoft Internet Explorer"

i=1
Do
  Filename$="C:\My Documents\"+Agy$+"mscat"
  If Dir(Filename$+".txt")<>"" Then
    Kill Filename$+".txt"
  End If
  URL$=site1$+Agy$+site2$+RptDay$+site3$

  IE.Navigate URL$
  finished = IE.ReadyState
  Do While finished<>4
    finished = IE.ReadyState
  Loop

  SendKeys "%fa"
  SendKeys Filename$+".txt"
  SendKeys "{TAB}t{ENTER}", -1

  Agy$="##2" 'Second agency
  i=i+1
Loop While i<=2

  IE.Quit
  Set IE = Nothing

'Goto Done
ExcelSide:
  Set XL = CreateObject("Excel.Application")
  XL.Visible = True
  'XL.Workbooks.Open(filename$+".xls")
  XL.Workbooks.Add

Agy$="##1" 'Starting agency again
i=1
Do
  filename$="C:\My Documents\"+Agy$+"mscat"

  XL.Range("A1").Value = "Scat"
  XL.Range("B1").Value = "Checkouts"
  XL.Range("C1").Value = "Renewals"
  XL.Range("D1").Value = "Total %"
  XL.Range("E1").Value = "Total %"
  XL.Range("H2").Value = Agy$

  filenum%=FreeFile
  Open filename$+".txt" for Input as filenum%
  x=1 : cntr=2
  Do Until x=Lof(filenum%)
    Line Input #filenum%, data$
    If x=1 Then 
      RptDate$=Left(data$,10)
      XL.Range("H1").Value = RptDate$
    End if
    ScatTest$=Mid(data$,13,1)
    If ScatTest$ Like "[0-9]" Then
      Goto GoOn
   Else
      Goto DoNext
    End If
GoOn:
    Line Input #filenum%, checkout$
    Line Input #filenum%, renewal$
    data$=Trim(data$) : checkout$=Trim(checkout$) : renewal$=Trim(Renewal$)
    chks$=Trim(Mid(checkout$,15,11))
    rnls$=Trim(Mid(renewal$,15,11))
        
    whatcell$ = "A" + CStr(cntr)
    XL.Range(whatcell$).Value = "'"+data$
    whatcell$ = "B" + CStr(cntr)
    XL.Range(whatcell$).Value = chks$
    whatcell$ = "C" + CStr(cntr)
    XL.Range(whatcell$).Value = rnls$
    cntr=cntr+1

DoNext:
    x=x+1
    y=Seek(filenum%)
    If y>Lof(filenum%) then
      x=Lof(filenum%)
    Else
      Seek filenum%,y
    End If
  Loop
  Close filenum%

    whatcell$ = "A" + CStr(cntr)
    XL.Range(whatcell$).Value = "TOTALS:"
    whatcell$ = "B" + CStr(cntr)
    XL.Range(whatcell$).Value = "=SUM(B2:B"+CStr(cntr-1)+")"
    whatcell$ = "C" + CStr(cntr)
    XL.Range(whatcell$).Value = "=SUM(C2:C"+CStr(cntr-1)+")"

For j=2 to cntr-1
  XL.Range("D"+CStr(j)).Value= "=B"+CStr(j)+"/B"+CStr(cntr)+"*100"
  XL.Range("E"+CStr(j)).Value= "=C"+CStr(j)+"/C"+CStr(cntr)+"*100"
Next j

  XL.Sheets("Sheet2").Select
  Agy$="##2" 'Second agency to get--e.g. for bookmobile or branch
  i=i+1
Loop While i<=2

  XL.Sheets("Sheet1").Name = "##1" 'Starting Agency
  XL.Sheets("Sheet2").Name = "##2" 'Second Agency

  'XL.ActiveWorkbook.SaveAs Filename:=Filename$+".xls", _
  '  FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
  '  ReadOnlyRecommended:=False, CreateBackup:=False
  '
  'XL.Quit
  Set XL = Nothing

Done:
End Sub