code:

---

Global $oXl,$xlRow,$SearcStartRow,$xlHeader

$filename = "c:\werktijdreg\registraties.xls"
$dsn='DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\werktijdreg\werkinfo.mdb'
$sql="Select * FROM Objecten"
$recordset = DBCommand($dsn,$sql)

$oXL = CreateObject("EXCEL.application")

IF EXIST ($filename)
$RC = $oXl.Workbooks.Open($filename)
For $object=0 to ubound($recordset,0)
	If $oxl.sheets.name($recordset[$object,0]) = false
		$objNewSheet = $objWorkBook.Worksheets.Add
		$objNewSheet.Name = $recordset[$object,0]
		$array = "Datum", "Naam", "Plaats", "Taken", "Afstand", "Duur"
   		$oXL.Range("A1:F1").Value = $array
	Endif
next

else
$rc = $oXL.Workbooks.Add
For $object=0 to ubound($recordset,1)
	$objNewSheet = $objWorkBook.Worksheets.Add
	$objNewSheet.Name = $recordset[$object,0]
	$array = "Datum", "Naam", "Plaats", "Taken", "Afstand", "Duur"
   	$oXL.Range("A1:F1").Value = $array
next
ENDIF 

$sql="Select * FROM Registraties"
$recordset = DBCommand($dsn,$sql)

if @error = 0
$oXL.Visible = 1

   For $row=0 To ubound($recordset,1)
	for $column=0 to ubound($recordset,2)
      	$oXL.Cells(($row+2),$column).Value = $recordset[$row,$column]
	Next
   Next

   $oXL.Range("A1:G1").Font.Bold = 1
   $rc = $oXL.Range("A1:G1").EntireColumn.AutoFit

;##loop door colom A om een waarde te vinden##

$objWorksheet = $objWorkbook.ActiveSheet
$objCell = $objWorksheet.Range("a2")

While $objCell.Offset(0,1).Value <> ""
	$Value = $objCell.Value
       ? $Value
	$objCell = $objCell.Offset(1,0)
Loop

   If Exist($FileName)
   	$RC = $oXl.ActiveWorkbook.Save
   Else
   	$RC = $oXl.ActiveWorkbook.SaveAs($FileName,-4143,"","",0,0,,,0) ; xlWorkbookNormal = -4143
   EndIf


   $oXL.UserControl = 1
else
   ? @error + " / " @serror
endif
exit

;###############################################################
function DBCommand($ConnDSN,$sql)
Dim $objConn, $adStateOpen
dim $Conntimeout, $CmdTimeout
dim $cmdCommand, $rsRecordset
dim $Records, $FinalRecords
dim $adCmdText, $adLockReadOnly, $adOpenStatic
dim $row, $rows, $column, $columns

$ConnDSN=trim($ConnDSN)
if not $ConnDSN
exit 87
endif
$sql=trim($sql)
if not $sql
exit 87
endif

$adStateOpen=1
$ConnTimeout=15
$CmdTimeout=30
$adCmdText = 1
$adOpenStatic = 3
$adLockReadOnly = 1

; open the database connection
$objConn = CreateObject("ADODB.Connection")
if @ERROR
exit @ERROR
endif
$objConn.ConnectionTimeout = $ConnTimeout
if @ERROR
exit @ERROR
endif
$objConn.CommandTimeout = $CmdTimeout
if @ERROR
exit @ERROR
endif
$objConn.Open($ConnDSN)
if @ERROR
exit @ERROR
endif
if not $objConn.State=$adStateOpen
$objConn=''
$DBCommand=''
exit @ERROR
endif

; create the database command object
$cmdCommand = CreateObject('ADODB.Command')
if @ERROR
exit @ERROR
endif
$cmdCommand.ActiveConnection = $objConn
if @ERROR
exit @ERROR
endif
$cmdCommand.CommandType = $adCmdText
if @ERROR
exit @ERROR
endif
$cmdCommand.CommandText = $sql
if @ERROR
$DBCommand=@ERROR
exit @ERROR
endif

if instr($sql,'SELECT')=1
; return a recordset

; create the recordset object
$rsRecordSet = CreateObject('ADODB.Recordset')
if @ERROR
exit @ERROR
endif
$rsRecordset.CursorType = $adOpenStatic
if @ERROR
exit @ERROR
endif
$rsRecordset.LockType = $adLockReadOnly
if @ERROR
exit @ERROR
endif
$rsRecordset.Open($cmdCommand)
if @ERROR
exit @ERROR
endif

if $rsRecordset.EOF and $rsRecordSet.BOF
; recordset is empty
$FinalRecords=''
else
if @ERROR
exit @ERROR
endif

; retrieve all records at once and transpose into tabular format
$Records = $rsRecordset.GetRows()
$columns=ubound($records,1)
$rows=ubound($records,2)
redim $FinalRecords[$rows,$columns]
for $row=0 to $rows
for $column=0 to $columns
$FinalRecords[$row,$column]=$records[$column,$row]
next
next
endif

; close recordset
if $rsRecordset.state=$adStateOpen
$rsRecordset.Close()
if @ERROR
exit @ERROR
endif
endif

$rsRecordset=''
$cmdCommand=''

$DBCommand=$FinalRecords
else
$rsRecordset=$cmdCommand.Execute()
$cmdCommand=''
$rsRecordset=''
if @ERROR
exit @ERROR
endif

$DBCommand=0
endif

; close the database connection
If $objConn.State = $adStateOpen
$objConn.Close()
if @ERROR
exit @ERROR
endif
EndIf
$objConn=''

exit 0
endfunction

---