PureBasic Forums - English

Code: Select all

IncludePath "C:\Apps\COMate"
XIncludeFile "COMate.pbi"

Global ExcelObj.COMateObject, WorkBook.COMateObject, WorkSheet.COMateObject

filename.s = ProgramParameter()
if len(filename)
  if lcase(GetExtensionPart(filename)) <> "txl": filename + ".txl": endif
  if ReadFile(1, filename)
    Repeat
      if eof(1): break: endif
      linetxl.s = trim(ReadString(1))
      if len(linetxl)
        if left(linetxl,1) = ";": continue: endif

        command.s  = StringField(linetxl,1," ")
        linerest.s = trim(right(linetxl, len(linetxl)-len(command)))
        select command
          case "open" : ExcelObj  = COMate_CreateObject(linerest)
          case "book" : WorkBook  = ExcelObj\GetObjectProperty(linerest)
          case "sheet": WorkSheet = ExcelObj\GetObjectProperty(linerest)

          case "exeob": ExcelObj\Invoke(linerest)
          case "exebk": WorkBook\Invoke(linerest)
          case "exesh": WorkSheet\Invoke(linerest)

          case "close"
                if FindString(lcase(linerest),"book",1): WorkBook\Release(): endif
                if FindString(lcase(linerest),"appl",1): ExcelObj\Release(): endif

          case "setbk": WorkBook\SetProperty(linerest)
          case "setsh": WorkSheet\SetProperty(linerest)
          case "set"  : ExcelObj\SetProperty(linerest)
          default     : ExcelObj\SetProperty(linerest)
        endselect
      endif
    Forever
  endif
endif
END

Code: Select all

;/////////////////////////////////////////////////////////////////////////////////
;***COMate***  COM automation through iDispatch.
;*===========
;*
;*Excel demo.
;/////////////////////////////////////////////////////////////////////////////////

open  Excel.Application
set   Visible = #False
book  Workbooks\Add

sheet Sheets('Sheet2')
exesh Activate()
set   Cells(2,1) = 'Hello 2B'

sheet Sheets('Sheet3')
exesh Activate()
set   Cells(2,1) = 'Hello 3B'

sheet Worksheets\Add
setsh Name = 'Another'
set   Cells(2,1) = 'Hello 4B'

sheet Sheets('Sheet1')
exesh Activate()
set   Cells(1,1) = 'Hello 1B'
set   Cells(4,1) = 66.52
set   Cells(5,1) = 71.63
set   Cells(6,1) = '=A4+A5'
set   Evaluate('A10') = '=SUM(A4:A6)'

exebk SaveAs('C:\Apps\COMate\Testing.xls')
exeob Quit()
close book appl

Code: Select all

IncludePath #PB_Compiler_Home + "comate"
XIncludeFile "comate.pbi"

ExcelFile.s = "D:\XlsWithoutExcel.xls"

If FileSize(ExcelFile) > 0
  DeleteFile(ExcelFile)
EndIf

ConnectionString.s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties=" + Chr(34) + "Excel 8.0;HDR=YES" + Chr(34)

Define CN.COMateObject

CN = COMate_CreateObject("ADODB.Connection")

If CN\Invoke("Open('" + ConnectionString + "')") = #S_OK

  CN\Invoke("Execute('Create Table myTable (F1 String, F2 String, F3 String, F4 String)')")
  
  CN\Invoke("Execute('Insert Into [myTable$] (F1, F2, F3, F4) Values ($0027Feel$0027, $0027the$0027, $0027..Pure..$0027, $0027Power$0027)')")
  
  CN\Invoke("Close")
  CN\Release()
  
Else
  
  Debug COMate_GetLastErrorDescription()
  
EndIf

Code: Select all

IncludePath "..\"
XIncludeFile "ADOmate.pbi"

ExcelFile.s = "XlsWithoutExcel.xls" 

If FileSize(ExcelFile) > 0 
  DeleteFile(ExcelFile) 
EndIf 

ConnectionString.s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties=" + Chr(34) + "Excel 8.0;HDR=YES" + Chr(34) 

myConnection = ADOmate_OpenDatabase(connectionString)

If myConnection
  ;Create a table.
    ADOmate_DatabaseUpdate(myConnection, "Create Table myTable (F1 String, F2 String, F3 String, F4 String)") 
    ADOmate_DatabaseUpdate(myConnection, "Insert Into [myTable$] (F1, F2, F3, F4) Values ('Feel', 'the', '..Pure..', 'Power')")
    ADOmate_CloseDatabase(myConnection)
Else
  Debug ADOmate_GetLastErrorDescription()
EndIf