Simple creation of excel files using COMate

[graves]

Hi,
I've developped this program to create excelfiles from a txt file, using COMate facilities (thanks, srod).

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

And this is a "txl" (text) file for testing purposes:

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

Best Regards

[srod]

Of course for this to work the user has to have Excel installed!

[Kiffi]

Of course for this to work the user has to have Excel installed!

with this little snippet it is possible to create xls-files without Excel and
use it like a database:

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

@srod: first i want to use ADOmate but ADOmate_CreateDatabase(ConnectionString) fails [broad hint]

Greetings ... Kiffi

[srod]

Ah but that requires the Jet provider to be installed - which I guess is pretty much installed on all Windows machines these days!

I'll take a look at the problem you've reported.

[srod]

@Kiffi : You cannot use ADOmate_CreateDatabase() for that particular connection string - you need ADOmate_OpenDatabase().

ADOmate also does not require the $0027 escape sequences.

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

[Kiffi]

@Kiffi : You cannot use ADOmate_CreateDatabase() for that particular connection string - you need ADOmate_OpenDatabase().

yes, my fault. Sorry for the misinterpretation.



Greetings ... Kiffi

[srod]

No problem.

I've added that as an extra ADOmate demo program!

[Little John]

The current COMate version 1.1.8 contains a file "XlsWithoutExcel.xls", but as far as I can see none of the demo programs uses it.

Regards, Little John

[srod]

The current COMate version 1.1.8 contains a file "XlsWithoutExcel.xls", but as far as I can see none of the demo programs uses it.

Regards, Little John

Aye, a remenant of some previous tinkering on my part! I've removed it from the download.