'Contributed by Hiroshi Takisita
'Manipulates MS Office Spreadsheet

output.Top=0
output.Left=0

Dim dispWidth,dispHeight
   
      strComputer = "."
      Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
      Set colItems = objWMIService.ExecQuery _
         ("Select * From Win32_DisplayConfiguration")
      
      For Each objItem In colItems
          dispWidth = objItem.PelsWidth
          dispHeight = objItem.PelsHeight
      Next

Sub main
      Form1.Text = "Spreadsheet"
  '    Form1.MaxWidth = dispWidth * 0.95
  '    Form1.MaxHeight= dispHeight * 0.88
      Form1.Width  = dispWidth * 0.95
      Form1.Height = dispHeight * 0.88
  '    Form1.MinWidth = 800
  '    Form1.MinHeight= 600
      Frame1.Width = Form1.Width - 4
      Frame1.Height= Form1.Height - 42
      
      'Microsoft Office2003
      AddObject "OWC11.Spreadsheet.11", "Spreadsheet1", 4, 10, Frame1.Width-4, Frame1.Height-12, Frame1

      'Microsoft Office2002
      'AddObject "OWC10.Spreadsheet.10", "Spreadsheet1", 4, 10, Frame1.Width-4, Frame1.Height-12, Frame1

      UpdateScreen
   
   DataDisp

End Sub

Sub sheet1setting
    
    With Spreadsheet1
           .autofit = True
           .DisplayOfficeLogo = True
           .DisplayToolbar = True
           .calculation = -4105
           .defaultQueryOnload = False
           .DisplayTitleBar = True
           .EnableEvents = True
           .ScreenUpdating = False
           .ViewOnlyMode = False
           
           .Range("H6:I25").NumberFormat = "#,##0;[Red]-#,##0"
           .Range("B6:B25").HorizontalAlignment = -4131     'Left
           .Range("H6:I25").HorizontalAlignment = -4152     'Right
           .Range("A5:J5").HorizontalAlignment = -4108      'Center
           .Range("E25").HorizontalAlignment = -4108
           
    '       .Range("A5:J5").Font.Name = "‚l‚r ‚ofSfVfbfN"
           .Range("A5:J5").Font.Bold = True
           .Range("A5:J5").Font.Size = 10
   
    '       .Range("A6:J100").Font.Name = "‚l‚r ‚ofSfVfbfN"
           .Range("A6:J100").Font.Bold = False
           .Range("A6:J100").Font.Size = 10
   
    '       .Range("H6:H100").Font.Name = "‚l‚r fSfVfbfN"
           .Range("H6:H100").Font.Bold = False
           .Range("H6:H100").Font.Size = 10
   
           .Range("A5:J5").Interior.ColorIndex = 6       'Background col
   
           .Range("A5:J25").Borders(7).LineStyle = 1      'Ruled line
           .Range("A5:J25").Borders(7).Weight = 2
           .Range("A5:J25").Borders(7).ColorIndex = 1
   
           .Range("A5:J25").Borders(8).LineStyle = 1
           .Range("A5:J25").Borders(8).Weight = 2
           .Range("A5:J25").Borders(8).ColorIndex = 1
   
           .Range("A5:J25").Borders(9).LineStyle = 1
           .Range("A5:J25").Borders(9).Weight = 2
           .Range("A5:J25").Borders(9).ColorIndex = 1
   
           .Range("A5:J25").Borders(10).LineStyle = 1
           .Range("A5:J25").Borders(10).Weight = 2
           .Range("A5:J25").Borders(10).ColorIndex = 1
   
           .Range("A5:J25").Borders(11).LineStyle = 1
           .Range("A5:J25").Borders(11).Weight = 2
           .Range("A5:J25").Borders(11).ColorIndex = 1
   
           .Range("A5:J25").Borders(12).LineStyle = 1
           .Range("A5:J25").Borders(12).Weight = 2
           .Range("A5:J25").Borders(12).ColorIndex = 1

            .Range("A5").Value = "No"
            .Range("B5").Value = "Year"
            .Range("C5").Value = "Term"
            .Range("D5").Value = "Charge"
            .Range("E5").Value = "Check number"
            .Range("F5").Value = "Detailed"
            .Range("G5").Value = "Items"
            .Range("H5").Value = "Sales total"
            .Range("I5").Value = "Quantity"
            .Range("J5").Value = "Comment"

            .Range("A5").ColumnWidth = 4
            .Range("B5").ColumnWidth = 5
            .Range("C5").ColumnWidth = 4
            .Range("D5").ColumnWidth = 6
            .Range("E5").ColumnWidth = 12
            .Range("F5").ColumnWidth = 8
            .Range("G5").ColumnWidth = 9
            .Range("H5").ColumnWidth = 13
            .Range("I5").ColumnWidth = 8
            .Range("J5").ColumnWidth = 28

            .Range("G1").Value = "Selling total"
            .Range("J2").Value = FormatDateTime(Now,vbLongDate)
            .Range("J2").HorizontalAlignment = -4152
            .Range("J3").Value = "ABCD Company"
            .Range("J3").HorizontalAlignment = -4152
            .ScreenUpdating = True  
    End With

End Sub

Sub DataDisp
   
    Spreadsheet1.Range("A:J").ClearContents         '

      sheet1setting
          
      UpdateScreen
      Spreadsheet1.ScreenUpdating = False
         
         For j=1 To 5
               Spreadsheet1.Cells(j+5, 1).Value = j
               Spreadsheet1.Cells(j+5, 2).Value = "2007"
               Spreadsheet1.Cells(j+5, 3).Value = "4Q"
               Spreadsheet1.Cells(j+5, 4).Value = "Takisita"
               Spreadsheet1.Cells(j+5, 5).Value = 3000000000 + j
               Spreadsheet1.Cells(j+5, 6).Value = j*10
         Next
               Spreadsheet1.Cells(6, 7).Value = "Cola"
               Spreadsheet1.Cells(6, 8).Value = 1000
               Spreadsheet1.Cells(6, 9).Value = 10

               Spreadsheet1.Cells(7, 7).Value = "Cola"
               Spreadsheet1.Cells(7, 8).Value = -1000
               Spreadsheet1.Cells(7, 9).Value = -10

               Spreadsheet1.Cells(8, 7).Value = "Cola"
               Spreadsheet1.Cells(8, 8).Value = 3000
               Spreadsheet1.Cells(8, 9).Value = 30

               Spreadsheet1.Cells(9, 7).Value = "Orange juice"
               Spreadsheet1.Cells(9, 8).Value = 1000
               Spreadsheet1.Cells(9, 9).Value = 10

               Spreadsheet1.Cells(10, 7).Value = "Milk"
               Spreadsheet1.Cells(10, 8).Value = 1000
               Spreadsheet1.Cells(10, 9).Value = 10

               Spreadsheet1.Cells(22, 5).Value = "Cola"
               Spreadsheet1.Cells(22, 8).Formula = "=SUMIF(G6:G21,E22,H6:H21)"
               Spreadsheet1.Cells(22, 9).Formula = "=SUMIF(G6:G21,E22,I6:I21)"
         
               Spreadsheet1.Cells(23, 5).Value = "Orange juice"
               Spreadsheet1.Cells(23, 8).Formula = "=SUMIF(G6:G21,E23,H6:H21)"
               Spreadsheet1.Cells(23, 9).Formula = "=SUMIF(G6:G21,E23,I6:I21)"
         
               Spreadsheet1.Cells(24, 5).Value = "Milk"
               Spreadsheet1.Cells(24, 8).Formula = "=SUMIF(G6:G21,E24,H6:H21)"
               Spreadsheet1.Cells(24, 9).Formula = "=SUMIF(G6:G21,E24,I6:I21)"
         
               Spreadsheet1.Cells(25, 5).Value = "Total"
               Spreadsheet1.Cells(25, 8).Formula = "=sum(H6:H21)"
               Spreadsheet1.Cells(25, 9).Formula = "=sum(I6:I21)"
      DoEvents
      UpdateScreen
      Spreadsheet1.ScreenUpdating = True
               
End Sub

Sub Form1_Resize
      
      Frame1.Width = Form1.Width -4
      Frame1.Height = Form1.Height -42
      
   UpdateScreen
End Sub

Sub Spreadsheet1_Click 
 Dim Adr_Cell
   
   Adr_Cell = Split(Spreadsheet1.ActiveCell.Address, "$")

   Print "Col : " & Adr_Cell(1)  & vbtab & "Row : " _
         & Adr_Cell(2) & vbtab & vbtab & "Item : " & Spreadsheet1.ActiveCell.FormulaLocal 
End Sub

Sub Spreadsheet1_LostFocus 
   Print "Spreadsheet1_event LostFocus"
End Sub

Sub ButtonSheetEventTrue_Click
      SpreadSheet1.EnableEvents = True
End Sub

Sub ButtonSheetEventFalse_Click
      SpreadSheet1.EnableEvents = False
End Sub

Sub ButtonExeport_Click
      SpreadSheet1.Export "C:\TestData.xls"
End Sub

Sub EndJob_Click
      Bye
End Sub

Sub ButtonAddSheet_Click
      Spreadsheet1.Sheets.Add ,3,1
End Sub

Sub ButtonDellSheet_Click
      Spreadsheet1.Sheets(4).Delete
End Sub

Sub ButtonSheet1Sel_Click
         Spreadsheet1.Sheets(1).Select
End Sub

Sub ButtonSheet2_Click
         Spreadsheet1.Sheets(2).Select
End Sub

Sub ButtonSheet3_Click
         Spreadsheet1.Sheets(3).Select
End Sub