XProfan

'Codesnipset mit der OCX-Erweiterung von Uwe "Pascal" Niemeier
'***************************************************************************************************
' Header-Dateien laden                                                                             *
'***************************************************************************************************
 $H WINDOWS.PH
 $H OCX3a.PH
 $H COMMCTRL.PH
'***************************************************************************************************
'***************************************************************************************************
' Include-Dateien laden                                                                            *
'***************************************************************************************************
 $I OCX3a.INC
 $I USER.INC
'***************************************************************************************************
'***************************************************************************************************
'* Globale Variablen                                                                               *
'***************************************************************************************************
DECLARE Excel&, Workbooks&, ExcelWnd&, ExcelBook&, ExcelSheet&, ExcelSheet$, ExcelSheetLines&
DECLARE Range&
*-------------------------------------------------------------------------------------------------*
'* Exceldatei öffnen                                                                               *
'*-------------------------------------------------------------------------------------------------*

PROC OpenExcelFile

    '*-------------------------------------------------------------------------------------------------*
    PARAMETERS File$
    '*-------------------------------------------------------------------------------------------------*
    DECLARE    UsedRange&, Rows&, Cell$
    '*-------------------------------------------------------------------------------------------------*

    IF FILEEXISTS(File$)

        'Excel unsichtbar starten:
        Excel&      = ocxCREATE("Excel.Application")
        Workbooks&  = ocxGet(Excel&,"Workbooks")
        ExcelWnd&   = ocxGet(Excel&,"Hwnd")
        ocxPut(Excel&,"DisplayAlerts",1)
        ocxPut(Excel&,"Visible"      ,0)
        '*-------------------------------------------------------------------------------------------------*
        'Excel-Datei öffnen:
        ExcelBook&  = ocxMethod(Workbooks&, "Open"  , File$)
        ExcelSheet& = ocxGet(ExcelBook&,    "Sheets", ExcelSheet$)
        ocxMethod(ExcelSheet&,"Unprotect")
        Range&      = ocxGet(ExcelSheet&,   "Range", "A1")
        UsedRange&  = ocxGet(ExcelSheet&,   "UsedRange")
        Rows&       = ocxGet(UsedRange&,    "Rows")
        ExcelSheetLines& = ocxGet(Rows&,    "Count")
        Cell$       = "A" + STR$(ExcelSheetLines& - 3)
        Range&      = ocxGet(ExcelSheet&, "Range", Cell$)
        LastBOMPos& = VAL(ocxGET(Range&, "Text"))

    ELSE

        ExcelWnd& = 0

    ENDIF

    '*-------------------------------------------------------------------------------------------------*
    'Rückgabewert:
    RETURN ExcelWnd&
    '*-------------------------------------------------------------------------------------------------*

ENDPROC

'*-------------------------------------------------------------------------------------------------*
'*-------------------------------------------------------------------------------------------------*
'* MS Excel schließen                                                                              *
'*-------------------------------------------------------------------------------------------------*

PROC CloseExcelFile

    '*-------------------------------------------------------------------------------------------------*
    'Excel schließen:
    ocxPut(ExcelBook&,   "Saved", 1)
    ocxMethod(ExcelBook&,"Close")
    ocxMethod(Excel&, "Quit")
    ocxRelease(Range&, ExcelSheet&, ExcelBook&, WorkBooks&, Excel&)
    SENDMESSAGE(ExcelWnd&,16,0,0)'--WM_CLOSE an Excel
    ExcelWnd& = 0
    '*-------------------------------------------------------------------------------------------------*

ENDPROC

'*-------------------------------------------------------------------------------------------------*
[...]
'*-------------------------------------------------------------------------------------------------*
'Neue Zeile (=4 Excel-Zeilen) durch Kopieren der vorherigen (4 Excel-Zeilen) anlegen:
'*-------------------------------------------------------------------------------------------------*
Line% =  ExcelSheetLines& - 3
ocxMethod(ExcelSheet&,"Activate")
Range& = ocxGet(ExcelSheet&,"Range","A" + STR$(INT(Line%)) + ":AT" + STR$(INT(Line% + 3)))
ocxMethod(Range&, "Copy")
Line% = Line% + 4
Range& = ocxGet(ExcelSheet&,"Range","A" + STR$(INT(Line%)) + ":AT" + STR$(INT(Line% + 3)))
ocxMethod(Range&, "PasteSpecial")
[...]
'*-------------------------------------------------------------------------------------------------*
'Excelzelle einfügen:
'*-------------------------------------------------------------------------------------------------*
Zelle$  = "A1"
Inhalt$ ="Test"

IF Zelle$ <> ""

    Range& = ocxGet(ExcelSheet&, "Range", Zelle$)
    ocxPut(Range&, "Value", Inhalt$)'Anstelle von "Value" ggf auch "Text" probieren

ENDIF

[...]
'*-------------------------------------------------------------------------------------------------*
'Exceltabelle speichern und schließen:
'*-------------------------------------------------------------------------------------------------*
ocxMethod(ExcelSheet&, "Protect")
ocxMethod(ExcelBook& , "Save")
CloseExcelFile
'*-------------------------------------------------------------------------------------------------*