Page 8 of 8 FirstFirst ... 678
Results 71 to 78 of 78

Thread: Excel COM question...

  1. #71
    This doesn't need to be a mystery. Dave's SB COM Windows C source is free to use without restriction. No use recreating the wheel.
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  2. #72
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    OK, last update before leaving for 2 weeks holiday.

    I've added Excel_Range class and re-factored some part of the source code. I will implement more Excel_Range with cells formatting by September.

    Full sources can be found at GitHub at https://github.com/ErosOlmi/ThinBASIC_On_GitHub
    If I will have time I will start writing help in next days, but not sure (family! ).

    Attached the ThinBASIC_Excel.DLL library. Just put the library and the example script into a directory to test it.
    Here few lines of a script in order to show how to use.

    Have fun.
    Eros

    #MINVERSION 1.9.13.0
    
    Uses "Excel"
    Uses "File"
    Uses "Console"
    
    
    Randomize Timer
    
    
    '--------------------------------------------------------------------
    Function Out(ByVal sMessage As String, Optional lLevel As Long, ByVal Wait As Long)
    '--------------------------------------------------------------------
    ' Output some message
    '--------------------------------------------------------------------
      PrintL Time$, String$(lLevel * 2, " ") & sMessage
    
    
      If Wait = %TRUE Then
        PrintL "---Press a key to continue---"
        WaitKey
      End If
    
    
    End Function
    
    
    '--------------------------------------------------------------------
    Function TBMain() As Long
    '--------------------------------------------------------------------
      Dim sXlsFileName  As String
      Dim xApp          As  Excel_Application
      Dim xWorkBook     As  Excel_Workbook
      Dim xSheet        As  Excel_Worksheet
      Dim x, y          As  Long
      
      sXlsFileName = APP_ScriptFullName & ".xlsx"
    
    
      '---Create a new Excel Application COM Server instance
      xApp = New Excel_Application
      
      '---Set Visible to TRUE so we can see what's going on
      xApp.Visible = %TRUE       
    
    
      '---Create Excel workbook
      If FILE_Exists(sXlsFileName) Then
        Out("Opening file: " & sXlsFileName)
        xWorkBook = xApp.WorkBooks.Open(sXlsFileName)
      Else
        Out("Creating a new empty workbook")
        xWorkBook = xApp.WorkBooks.Add
      End If
    
    
      Out("Workbook      name is: " & xWorkBook.Name)
      Out("Workbook full name is: " & xWorkBook.FullName)
    
    
      '---Get the activesheet
      xSheet = xApp.Activesheet
      Out("Active sheet name is actually: " & xSheet.Name)
       
      '---Change Sheet name (here Set and Get of a Sheet Name)
      xSheet.Name = "thinBasic_Test_" & Replace$(Timer, ".", "_")
      Out("Changed sheet name to: " & xSheet.Name)
      
      '---Write some text into Excel sheet
      Out("Start filling some cells. Timer start: " & Timer)
      For y = 1 To 100
        For x = 1 To 5
          xSheet.Cells(y, x).Value = "Cell " & Chr$(x + Asc("A") - 1) & Format$(y)
        Next
        x = 6
        xSheet.Cells(Y, X) = y + y / Rnd(1, 10)
        x = 7
        xSheet.Cells(Y, X) = Format$(Rnd(-1000, 1000), "$#,.00")
      Next
      Out("End filling some cells. Timer End: " & Timer)
    
    
      '---Working with sheet.range
      Dim sRange As String
      Dim xRange As Excel_Range
      sRange = "A1:B3;D1:F10"
      
      out("Working with range: " & sRange)
      xRange = xSheet.Range(sRange)
      xRange.Select
      xRange.Value = "X"
      Sleep 1000
      out("Range address " & xRange.Address)
      xRange.Clear
    
    
      out("Clearing A:Z")
      xRange = xSheet.Range("A:Z")
      xRange.Clear
      xSheet.Range("A1").Select
    
    
      out("Making some easy calculation")
      xSheet.Cells(1, 1) = "Few random number"
      For y = 1 To 20
        xSheet.Cells(Y, 2) = Rnd(10, 2000)
      Next
      xSheet.Cells(22, 1) = "Total"
      xRange = xSheet.Range("B22")
      xRange.Formula = "=sum(B1:B20)"
    
    
      xRange = xSheet.Range("D1:D20")
      xRange.Formula = "=Sin(B1)"
      xRange = xSheet.Range("D22")
      xRange.Formula = "=sum(D1:D20)"
      Sleep 3000
      
      '---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
      out("Checking if file must be saved")
      If xWorkBook.Saved = %FALSE Then
        out("Tells Excel not to bother with alerts")
        xApp.DisplayAlerts = %FALSE
        If FILE_Exists(sXlsFileName) Then
          out("File already exists so just save")
          xWorkBook.Save
        Else
          out("File does not exists so saving file to: " & sXlsFileName)
          xWorkBook.SaveAs(sXlsFileName)
        End If
        out("Restoring standard alerts")
        xApp.DisplayAlerts = %TRUE
      End If
    
    
      out("Closing active window")
      xApp.ActiveWindow.Close
      
      out("Quitting Excel")
      xApp.Quit                     
    
    
    
    
      out("OK, test done. thinBasic will now close.", 0, %TRUE)
    
    
    
    
    End Function
    
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  3. #73
    Eros,

    Your new Excel COM interface looks great!

    Is the plan to use this as a way to forge a path towards generic COM in the language?
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  4. #74
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Hi John,

    no, for the moment this is just the starting of a new thinBasic module dedicated to Excel automation.
    I will try to add as many features as possible.

    Here attached a new update.
    I've additionally implemented Excel_Range, now you can change background color in different ways.
    I will add more options and full help when I will be back from holidays.

    Full sources can be found at GitHub at https://github.com/ErosOlmi/ThinBASIC_On_GitHub

    Attached the ThinBASIC_Excel.DLL library. Just put the library and the example script into a directory to test it.
    Here few lines of a script in order to show how to use.

    Ciao
    Eros

    #MINVERSION 1.9.13.0
    
    Uses "Excel"
    Uses "File"
    Uses "Console"
    
    
    Randomize Timer
    
    
    '--------------------------------------------------------------------
    Function Out(ByVal sMessage As String, Optional lLevel As Long, ByVal Wait As Long)
    '--------------------------------------------------------------------
    ' Output some message
    '--------------------------------------------------------------------
      PrintL Time$, String$(lLevel * 2, " ") & sMessage
    
    
      If Wait = %TRUE Then
        PrintL "---Press a key to continue---"
        WaitKey
      End If
    
    
    End Function
    
    
    '--------------------------------------------------------------------
    Function TBMain() As Long
    '--------------------------------------------------------------------
      Dim sXlsFileName  As String
      Dim xApp          As Excel_Application
      Dim xWorkBook     As Excel_Workbook
      Dim xSheet        As Excel_Worksheet
      Dim xRange        As Excel_Range
      Dim x, y          As Long
      
      sXlsFileName = APP_ScriptFullName & ".xlsx"
    
    
      '---Create a new Excel Application COM Server instance
      xApp = New Excel_Application
      
      '---Set Visible to TRUE so we can see what's going on
      xApp.Visible = %TRUE       
    
    
      '---Create Excel workbook
      If FILE_Exists(sXlsFileName) Then
        Out("Opening file: " & sXlsFileName)
        xWorkBook = xApp.WorkBooks.Open(sXlsFileName)
      Else
        Out("Creating a new empty workbook")
        xWorkBook = xApp.WorkBooks.Add
      End If
    
    
      Out("Workbook      name is: " & xWorkBook.Name)
      Out("Workbook full name is: " & xWorkBook.FullName)
    
    
      '---Get the activesheet
      xSheet = xApp.Activesheet
      Out("Active sheet name is actually: " & xSheet.Name)
       
      '---Change Sheet name (here Set and Get of a Sheet Name)
      xSheet.Name = "thinBasic_Test_" & Replace$(Timer, ".", "_")
      Out("Changed sheet name to: " & xSheet.Name)
      
      '---Write some text into Excel sheet
      Out("Start filling some cells. Timer start: " & Timer)
      For y = 1 To 100
        For x = 1 To 5
          xSheet.Cells(y, x).Value = "Cell " & Chr$(x + Asc("A") - 1) & Format$(y)
        Next
        x = 6
        xSheet.Cells(Y, X).Value = y + y / Rnd(1, 10)
        x = 7
        xSheet.Cells(Y, X).Value = Format$(Rnd(-1000, 1000), "$#,.00")
      Next
      Out("End filling some cells. Timer End: " & Timer)
    
    
      '---Working with sheet.range
      Dim sRange As String
      sRange = "A1:B3;D1:F10"
      
      out("Working with range: " & sRange)
      xRange = xSheet.Range(sRange)
      xRange.Select
      xRange.Value = "X"
      Sleep 1000
      out("Range address " & xRange.Address)
      xRange.Clear
    
    
      out("Clearing A:Z")
      xRange = xSheet.Range("A:Z")
      xRange.Clear
      xSheet.Range("A1").Select
    
    
      out("Making some easy calculation")
      xSheet.Cells(1, 1) = "Few random numbers"
      For y = 1 To 20
        xSheet.Cells(Y, 2) = Rnd(10, 2000)
      Next
      xSheet.Cells(22, 1) = "Total"
      xRange = xSheet.Range("B22")
      xRange.Formula = "=Sum(B1:B20)"
    
    
      xRange = xSheet.Range("D1:D20")
      xRange.Formula = "=Sin(B1)"
      xRange.HorizontalAlignment = %XlHAlign_xlHAlignCenter
      xRange.ColumnWidth = 25
      
      xRange = xSheet.Range("D22")
      xRange.Formula = "=Sum(D1:D20)"
    
    
      out("Change some cells color background")
      xSheet.Cells(1, 8).Value = "Changing some randomized color"
      Randomize Timer
    
    
      Long ColorIndex
      For y = 2 To 100
        For x = 1 To 10
          sRange = Chr$(Asc("G") + x) & y & ":" & Chr$(Asc("G") + x) & y
          xRange = xSheet.Range(sRange)
    
    
          '---Using ColorIndex property
          'xRange.Interior.ColorIndex = ColorIndex
          'xRange.Value = ColorIndex
          'Incr ColorIndex
          
          '---Using color property
          xRange.Interior.Color = Rgb(Rnd(0, 255), Rnd(0, 255), Rnd(0, 255))
          'xRange.Interior.Color = Rgb(x * Y, x * y, Rnd(0, 255))
        Next
      Next
    
    
      out("Sleeping some time in order to let see what's on video")
      Sleep 5000
    
    
      out("Clear color example area ...")
      xRange = xSheet.Range("H:Z")
      xRange.Clear
          
      
      '---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
      out("Checking if file must be saved")
      If xWorkBook.Saved = %FALSE Then
        out("Tells Excel not to bother with alerts")
        xApp.DisplayAlerts = %FALSE
        If FILE_Exists(sXlsFileName) Then
          out("File already exists so just save")
          xWorkBook.Save
        Else
          out("File does not exists so saving file to: " & sXlsFileName)
          xWorkBook.SaveAs(sXlsFileName)
        End If
        out("Restoring standard alerts")
        xApp.DisplayAlerts = %TRUE
      End If
    
    
      out("Closing active window")
      xApp.ActiveWindow.Close
      
      out("Quitting Excel")
      xApp.Quit                     
    
    
    
    
      out("OK, test done. thinBasic will now close.", 0, %TRUE)
    
    
    
    
    End Function
    
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  5. #75
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Here attached a new update.
    Excel_Range has now Font property that can be used to get/set font properties of cells in Range.
    See example below.

    Full sources can be found at GitHub at https://github.com/ErosOlmi/ThinBASIC_On_GitHub

    Attached the ThinBASIC_Excel.DLL library. Just put the library and the example script into a directory to test it.
    Here few lines of a script in order to show how to use.

    Ciao
    Eros

    #MINVERSION 1.9.13.0
    
    Uses "Excel"
    Uses "File"
    Uses "Console"
    
    
    Randomize Timer
    
    
    '--------------------------------------------------------------------
    Function Out(ByVal sMessage As String, Optional lLevel As Long, ByVal Wait As Long)
    '--------------------------------------------------------------------
    ' Output some message
    '--------------------------------------------------------------------
      PrintL Time$, String$(lLevel * 2, " ") & sMessage
    
    
      If Wait = %TRUE Then
        PrintL "---Press a key to continue---"
        WaitKey
      End If
    
    
    End Function
    
    
    '--------------------------------------------------------------------
    Function TBMain() As Long
    '--------------------------------------------------------------------
      Dim sXlsFileName  As String
      Dim xApp          As Excel_Application
      Dim xWorkBook     As Excel_Workbook
      Dim xSheet        As Excel_Worksheet
      Dim xRange        As Excel_Range
      Dim x, y          As Long
      
      sXlsFileName = APP_ScriptFullName & ".xlsx"
    
    
      '---Create a new Excel Application COM Server instance
      xApp = New Excel_Application
      
      '---Set Visible to TRUE so we can see what's going on
      xApp.Visible = %TRUE       
    
    
      '---Create Excel workbook
      If FILE_Exists(sXlsFileName) Then
        Out("Opening file: " & sXlsFileName)
        xWorkBook = xApp.WorkBooks.Open(sXlsFileName)
      Else
        Out("Creating a new empty workbook")
        xWorkBook = xApp.WorkBooks.Add
      End If
    
    
      Out("Workbook      name is: " & xWorkBook.Name)
      Out("Workbook full name is: " & xWorkBook.FullName)
    
    
      '---Get the activesheet
      xSheet = xApp.Activesheet
      Out("Active sheet name is actually: " & xSheet.Name)
       
      '---Change Sheet name (here Set and Get of a Sheet Name)
      xSheet.Name = "thinBasic_Test_" & Replace$(Timer, ".", "_")
      Out("Changed sheet name to: " & xSheet.Name)
      
      '---Write some text into Excel sheet
      Out("Start filling some cells. Timer start: " & Timer)
      For y = 1 To 100
        For x = 1 To 5
          xSheet.Cells(y, x).Value = "Cell " & Chr$(x + Asc("A") - 1) & Format$(y)
        Next
        x = 6
        xSheet.Cells(Y, X).Value = y + y / Rnd(1, 10)
        x = 7
        xSheet.Cells(Y, X).Value = Format$(Rnd(-1000, 1000), "$#,.00")
      Next
      Out("End filling some cells. Timer End: " & Timer)
    
    
      '---Working with sheet.range
      Dim sRange As String
      sRange = "A1:B3;D1:F10"
      
      out("Working with range: " & sRange)
      xRange = xSheet.Range(sRange)
      xRange.Select
      xRange.Value = "X"
      Sleep 1000
      out("Range address " & xRange.Address)
      xRange.Clear
    
    
      out("Clearing A:Z")
      xRange = xSheet.Range("A:Z")
      xRange.Clear
      xSheet.Range("A1").Select
    
    
      out("Making some easy calculation")
      xSheet.Cells(1, 1) = "Few random numbers"
      For y = 1 To 20
        xSheet.Cells(Y, 2) = Rnd(10, 2000)
      Next
      xSheet.Cells(22, 1) = "Total"
      xRange = xSheet.Range("B22")
      xRange.Formula = "=Sum(B1:B20)"  
    
    
      xRange = xSheet.Range("D1:D20")
      xRange.Formula = "=Sin(B1)"
      xRange.HorizontalAlignment = %XlHAlign_xlHAlignCenter
      xRange.ColumnWidth = 25
      
      xRange = xSheet.Range("D22")
      xRange.Formula = "=Sum(D1:D20)"
      
      '---Change some font properties
      xRange.Font.Name = "Arial"
      xRange.Font.Size = 14
      xRange.Font.Bold = %TRUE
      xRange.Font.Italic = %TRUE
      xRange.Font.Underline = %XlUnderlineStyle_xlUnderlineStyleSingle
      xRange.Font.Color = Rgb(255, 0, 0)
      xRange.Interior.Color = Rgb(255, 255, 0)
      Out("Current font style is: " & xRange.Font.FontStyle)
    
    
      out("Change some cells color background")
      xSheet.Cells(1, 8).Value = "Changing some randomized color"
      Randomize Timer
    
    
      Long ColorIndex
      For y = 2 To 100
        For x = 1 To 10
          sRange = Chr$(Asc("G") + x) & y & ":" & Chr$(Asc("G") + x) & y
          xRange = xSheet.Range(sRange)
    
    
          '---Using ColorIndex property
          'xRange.Interior.ColorIndex = ColorIndex
          'xRange.Value = ColorIndex
          'Incr ColorIndex
          
          '---Using color property
          xRange.Interior.Color = Rgb(Rnd(0, 255), Rnd(0, 255), Rnd(0, 255))
          'xRange.Interior.Color = Rgb(x * Y, x * y, Rnd(0, 255))
        Next
      Next
    
    
      out("Sleeping some time in order to let see what's on video")
      Sleep 5000
    
    
      out("Clear color example area ...")
      xRange = xSheet.Range("H:Z")
      xRange.Clear
          
      
      '---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
      out("Checking if file must be saved")
      If xWorkBook.Saved = %FALSE Then
        out("Tells Excel not to bother with alerts")
        xApp.DisplayAlerts = %FALSE
        If FILE_Exists(sXlsFileName) Then
          out("File already exists so just save")
          xWorkBook.Save
        Else
          out("File does not exists so saving file to: " & sXlsFileName)
          xWorkBook.SaveAs(sXlsFileName)
        End If
        out("Restoring standard alerts")
        xApp.DisplayAlerts = %TRUE
      End If
    
    
      out("Closing active window")
      xApp.ActiveWindow.Close
      
      out("Quitting Excel")
      xApp.Quit                     
    
    
    
    
      out("OK, test done. thinBasic will now close.", 0, %TRUE)
    
    
    
    
    End Function
    
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  6. #76
    I think the TB Excel syntax and flow look great!

    WOW Over 11,000 lines for the excel.inc file. No wonder we never hear from you anymore.
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  7. #77
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Thanks John.
    More will come in next weeks.

    excel.inc is not written by me but automatically generated by Power Basic COM Browser.
    You specify which COM library you want and Power BASIC COM Browser will produce rilevante .inc file for that library with all the classes, interfaces, constants, methods, properties, ... exposed by the library. In this case is the Excel COM library that is huge and implements a lot of interfaces, so the risulting .inc file is huge too.

    thinBASIC Excel module just wrap some interfaces/methods/properties comunicating with thinBASIC Core engine for parsing syntax and storing data.
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  8. #78
    excel.inc is not written by me but automatically generated by Power Basic COM Browser.
    You shouldn't have said anything and accepted the awe and astonishment.

    That shows you how long it has been since I used PB. I always used Jose Roca's typelib browser and code generator. The PB one seemed half baked. (may have improved after I stopped using PB)
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

Page 8 of 8 FirstFirst ... 678

Similar Threads

  1. Question of the day ;)
    By Petr Schreiber in forum thinBasic General
    Replies: 8
    Last Post: 23-08-2010, 19:58
  2. gdi question
    By Lionheart008 in forum UI (User Interface)
    Replies: 6
    Last Post: 07-12-2009, 19:31
  3. Replies: 3
    Last Post: 31-03-2009, 23:16
  4. MS Excel as 3D engine :D
    By ErosOlmi in forum Development
    Replies: 3
    Last Post: 13-03-2008, 01:09
  5. Equivalent VBA Excel code
    By marcel in forum COM
    Replies: 12
    Last Post: 08-11-2007, 16:18

Members who have read this thread: 0

There are no members to list at the moment.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •