ExcelFunktionen COMatePlus

Hier könnt Ihr gute, von Euch geschriebene Codes posten. Sie müssen auf jeden Fall funktionieren und sollten möglichst effizient, elegant und beispielhaft oder einfach nur cool sein.
Mesa
Beiträge: 16
Registriert: 03.05.2012 18:23

Re: ExcelFunktionen COMatePlus

Beitrag von Mesa »

A cell has coordinates (eg B2) but it is possible to give a name to a cell (insertion / name).

How to retrieve the contents of a cell with its name (not its coordinates)?
Thank you.

google translate :
Hat eine Zelle Koordinaten (zB B2) es möglich ist, zielen darauf ab, einen Namen zu einer Zelle (Einfügen / Namen) geben.

Wie man den Inhalt einer Zelle Name mit Geschichte (nicht IKT-Koordinaten) zu bekommen?
Danke.

Mesa.
Benutzeravatar
Falko
Admin
Beiträge: 3531
Registriert: 29.08.2004 11:27
Computerausstattung: PC: MSI-Z590-GC; 32GB-DDR4, ICore9; 2TB M2 + 2x3TB-SATA2 HDD; Intel ICore9 @ 3600MHZ (Win11 Pro. 64-Bit),
Acer Aspire E15 (Win11 Home X64). Purebasic LTS 6.0
Kontaktdaten:

Re: ExcelFunktionen COMatePlus

Beitrag von Falko »

Hello Mesa,
sorry my english.
Do you mean this from VBA?
VBA-Macro hat geschrieben:Sub TestName()
'
' Test Makro
'
' Tastenkombination: Strg+z
' Test, if your cell B2 named with "Test"
Dim x As String
x = Range("B2").Name.Name 'This returns Name of Range
MsgBox x
x = Range("B2").Name 'This returns Adress of Range
MsgBox x
x = Range("Test").Address 'This returns Adress from named cell
MsgBox x
x = Range("Test").Value 'This returns Value from named cell
MsgBox x

End Sub
Bild
Win10 Pro 64-Bit, PB_5.4,GFA-WinDOS, Powerbasic9.05-Windows, NSBasic/CE, NSBasic/Desktop, NSBasic4APP, EmergenceBasic
Mesa
Beiträge: 16
Registriert: 03.05.2012 18:23

Re: ExcelFunktionen COMatePlus

Beitrag von Mesa »

I'm looking for this
Ich durchsuchen

x = Range("Test").Value 'This returns Value from named cell

(=>XLSFunc_ReadCellWithName(ExcelObject.COMateObject, name.s))

Mesa.
Benutzeravatar
Falko
Admin
Beiträge: 3531
Registriert: 29.08.2004 11:27
Computerausstattung: PC: MSI-Z590-GC; 32GB-DDR4, ICore9; 2TB M2 + 2x3TB-SATA2 HDD; Intel ICore9 @ 3600MHZ (Win11 Pro. 64-Bit),
Acer Aspire E15 (Win11 Home X64). Purebasic LTS 6.0
Kontaktdaten:

Re: ExcelFunktionen COMatePlus

Beitrag von Falko »

Okay, i've insert this following Procedure in my Include:

Code: Alles auswählen

Procedure.s XLSFunc_ReadCellWithName(ExcelObject.COMateObject,CellName.s)
  Protected ReturnValue.s
  ReturnValue=ExcelObject\GetStringProperty("Range('"+CellName+"')\Value")
  ProcedureReturn ReturnValue
EndProcedure
And here my test-code.

Code: Alles auswählen

;Please use my ExcelFunctions.pbi :  http://www.purebasic.fr/german/viewtopic.php?p=239466#239466
;Sorry, AddWorksheetAfter() and RechtsMarkieren() i could not convert to COMate, But all
;another Functions :)
;You must in ExcelFunctions.pbi correct the Path from Comate.pbi.
EnableExplicit
Define ExcelObject.l

XIncludeFile "ExcelFunktion.pbi"

ExcelObject=XLSFunc_OpenExcelFile("Mappe1.xls"); Change path to your table with cells of name with ,xls or .xlsx
XLSFunc_ExcelVisible(ExcelObject,#True); You can see Excel
Debug XLSFunc_ReadCellWithName(ExcelObject,"Test")
XLSFunc_CloseWorkbook(ExcelObject)
XLSFunc_CloseExcelAll(ExcelObject)
This is a cool function. :allright:

Regards,
Falko
Bild
Win10 Pro 64-Bit, PB_5.4,GFA-WinDOS, Powerbasic9.05-Windows, NSBasic/CE, NSBasic/Desktop, NSBasic4APP, EmergenceBasic
Benutzeravatar
Falko
Admin
Beiträge: 3531
Registriert: 29.08.2004 11:27
Computerausstattung: PC: MSI-Z590-GC; 32GB-DDR4, ICore9; 2TB M2 + 2x3TB-SATA2 HDD; Intel ICore9 @ 3600MHZ (Win11 Pro. 64-Bit),
Acer Aspire E15 (Win11 Home X64). Purebasic LTS 6.0
Kontaktdaten:

Re: ExcelFunktionen COMatePlus

Beitrag von Falko »

will you write to name of cells, here a new function, i've insert in my include:

Code: Alles auswählen

Procedure XLSFunc_WriteCellWithName(ExcelObject.COMateObject,CellName.s,NewValue.d)
  ExcelObject\SetProperty("Range('"+CellName+"')\Value ="+StrD(NewValue))
EndProcedure
Test-Code here:

Code: Alles auswählen

;Please use my ExcelFunctions.pbi :  http://www.purebasic.fr/german/viewtopic.php?p=239466#239466
;Sorry, AddWorksheetAfter() and RechtsMarkieren() i could not convert to COMate, But all
;another Functions :)
;You must in ExcelFunctions.pbi correct the Path from Comate.pbi.
EnableExplicit
Define ExcelObject.l

XIncludeFile "ExcelFunktion.pbi"

ExcelObject=XLSFunc_OpenExcelFile("Mappe1.xls"); Change your own table with cells of name
XLSFunc_ExcelVisible(ExcelObject,#True); You can see Excel
Debug XLSFunc_ReadCellWithName(ExcelObject,"Test")
XLSFunc_WriteCellWithName(ExcelObject,"Test",1250)
XLSFunc_CloseWorkbook(ExcelObject)
XLSFunc_CloseExcelAll(ExcelObject)
[Edit]
I've made two new Functions to rename and delete Cell names in my ExcelFunktions.pbi,
see first Thread.

Code: Alles auswählen

Procedure XLSFunc_RenameCellName(ExcelObject.COMateObject,Cellname.s,NewCellname.s)
  ExcelObject\SetProperty("ActiveWorkbook\Names('"+Cellname+"')\Name='"+NewCellname+"'")
EndProcedure

Procedure XLSFunc_DeleteCellName(ExcelObject.COMateObject,CellName.s)
  ExcelObject\invoke("ActiveWorkbook\Names('"+CellName+"')\Delete");:Debug COMate_GetLastErrorDescription() 
EndProcedure
But I still have no solution for:
VBA hat geschrieben:Names.Add Name:="test", RefersTo:="=sheet1!$a$1:$c$20"
to a new Procedure:

Code: Alles auswählen

Procedure.s XLSFunc_CreateCellName(ExcelObject.COMateObject,Range.s,Cellname.s)
....
Endprocedure
:cry:

[/Edit]

----------------------------------------------------------------------------------------

[Edit1]
I have found moreover another solution :D

Code: Alles auswählen

Procedure.s XLSFunc_CreateCellName(ExcelObject.COMateObject,Range.s,CellName.s)
    ExcelObject\SetProperty("Range('"+Range+"')\Name='"+CellName+"'")
EndProcedure

Code: Alles auswählen

;Please use my ExcelFunctions.pbi :  http://www.purebasic.fr/german/viewtopic.php?p=239466#239466
;You must in ExcelFunctions.pbi correct the Path from Comate.pbi.
EnableExplicit

XIncludeFile "ExcelFunktion.pbi"


ExcelObject=XLSFunc_OpenExcelFile("Mappe1.xlsx"); Change your own table with cells of name
XLSFunc_ExcelVisible(ExcelObject,#True); You can see Excel
; Debug XLSFunc_ReadCellWithName(ExcelObject,"Test")
; XLSFunc_WriteCellWithName(ExcelObject,"Test",1250)
; XLSFunc_RenameCellName(ExcelObject,"Test","Money")
; XLSFunc_DeleteCellName(ExcelObject,"Test")
XLSFunc_CreateCellName(ExcelObject,"A1","Pure")
XLSFunc_CreateCellName(ExcelObject,"B1","Basic")
XLSFunc_CreateCellName(ExcelObject,"C1","from")
XLSFunc_CreateCellName(ExcelObject,"D1","Falko")
XLSFunc_CloseWorkbook(ExcelObject)
XLSFunc_CloseExcelAll(ExcelObject)
[/Edit1]
Bild
Win10 Pro 64-Bit, PB_5.4,GFA-WinDOS, Powerbasic9.05-Windows, NSBasic/CE, NSBasic/Desktop, NSBasic4APP, EmergenceBasic
Mesa
Beiträge: 16
Registriert: 03.05.2012 18:23

Re: ExcelFunktionen COMatePlus

Beitrag von Mesa »

Hello,

A little bug here :
Hier ein kleiner Bug :

Code: Alles auswählen

Procedure XLSFunc_SetColumnWidht(ExcelObject.COMateObject, Range.s,Width.f); Set ColumnWidth to one or more Columns
  ExcelObject\Invoke("Columns('"+Range+"')\Select")
  ExcelObject\SetProperty("Selection\ColumnWidth="+Str(Width))
EndProcedure

Procedure XLSFunc_SetRowsHeight(ExcelObject.COMateObject, Range.s,Height.f); Set Height on rang to rows
  ExcelObject\Invoke("Rows('"+Range+"')\Select")
  ExcelObject\SetProperty("Selection\RowHeight="+Str(Height))
EndProcedure

Procedure XLSFunc_SetRowHeight(ExcelObject.COMateObject, Range.s,Height.f); Set Height to one or all Rows
  ExcelObject\Invoke("Row('"+Range+"')\Select")
  ExcelObject\SetProperty("Selection\RowHeight="+Str(Height))
EndProcedure
should be:
sollte:

(it's a float number => strF() )

Code: Alles auswählen

Procedure XLSFunc_SetColumnWidht(ExcelObject.COMateObject, Range.s,Width.f); Set ColumnWidth to one or more Columns
  ExcelObject\Invoke("Columns('"+Range+"')\Select")
  ExcelObject\SetProperty("Selection\ColumnWidth="+StrF(Width))
EndProcedure

Procedure XLSFunc_SetRowsHeight(ExcelObject.COMateObject, Range.s,Height.f); Set Height on rang to rows
  ExcelObject\Invoke("Rows('"+Range+"')\Select")
  ExcelObject\SetProperty("Selection\RowHeight="+StrF(Height))
EndProcedure

Procedure XLSFunc_SetRowHeight(ExcelObject.COMateObject, Range.s,Height.f); Set Height to one or all Rows
  ExcelObject\Invoke("Row('"+Range+"')\Select")
  ExcelObject\SetProperty("Selection\RowHeight="+StrF(Height))
EndProcedure
How do I set the automatic page number in the header?
Wie richte ich die automatische Seitenzahl in der Kopfzeile?

Code: Alles auswählen

XLSFunc_WriteLeftHeader(NewExcelObject, "&[Page]");&[Pages
doesn't work.
funktioniert nicht.

Thanks

Mesa.
Benutzeravatar
Falko
Admin
Beiträge: 3531
Registriert: 29.08.2004 11:27
Computerausstattung: PC: MSI-Z590-GC; 32GB-DDR4, ICore9; 2TB M2 + 2x3TB-SATA2 HDD; Intel ICore9 @ 3600MHZ (Win11 Pro. 64-Bit),
Acer Aspire E15 (Win11 Home X64). Purebasic LTS 6.0
Kontaktdaten:

Re: ExcelFunktionen COMatePlus

Beitrag von Falko »

Thank you for the Bugreport. I have changed to Floats :allright: .

At the next Problem, i have search with google many VBA-Scripts.
Just i found this here:

Code: Alles auswählen

XLSFunc_WriteLeftHeader(ExcelObject,"Seite &S"); for german (tested)

XLSFunc_WriteLeftHeader(ExcelObject,"Page &P"); for english (not tested)
Microsoft hat geschrieben: http://support.microsoft.com/kb/142136


Codes to format text
--------------------------------------------------------------------

&L Left-aligns the characters that follow.
&C Centers the characters that follow.
&R Right-aligns the characters that follow.
&E Turns double-underline printing on or off.
&X Turns superscript printing on or off.
&Y Turns subscript printing on or off.
&B Turns bold printing on or off.
&I Turns italic printing on or off.
&U Turns underline printing on or off.
&S Turns strikethrough printing on or off.
&O Turns outline printing on or off (Macintosh only).
&H Turns shadow printing on or off (Macintosh only).
&"fontname" Prints the characters that follow in the specified
font.
Be sure to include the quotation marks around the
font name.
&nn Prints the characters that follow in the specified
font size. Use a two-digit number to specify a size
in points.



Codes to insert specific data
--------------------------------------------------------------------

&D Prints the current date.
&T Prints the current time.
&F Prints the name of the document.
&A Prints the name of the workbook tab (the "sheet name").
&P Prints the page number.
&P+number Prints the page number plus number.
&P-number Prints the page number minus number.
&& Prints a single ampersand.
&N Prints the total number of pages in the document.
If you will see the PrintView of Excel - Page, you can insert this function:

Code: Alles auswählen

Procedure XLSFunc_PrintPeview(ExcelObject.COMateObject)     ; PrintPreview 
  ExcelObject\Invoke("ActiveSheet\PrintPreview"):Debug COMate_GetLastErrorDescription()
EndProcedure
Bild
Win10 Pro 64-Bit, PB_5.4,GFA-WinDOS, Powerbasic9.05-Windows, NSBasic/CE, NSBasic/Desktop, NSBasic4APP, EmergenceBasic
Mesa
Beiträge: 16
Registriert: 03.05.2012 18:23

Re: ExcelFunktionen COMatePlus

Beitrag von Mesa »

Hello,

VBA:
&P : es funktioniert
&N : es funktioniert

PUREBASIC
&P : es funktioniert
&N : es funktioniert nicht, Ich habe den Namen des Arbeitsblatts statt
i've got the name of the worksheet instead :o

And you ?
und Sie ?

replace &N by Hpagebreaks.count maybe ?
ersetzen & N durch Hpagebreaks.count vielleicht?

what do you think?
was meinst du?

Mesa.
Benutzeravatar
Falko
Admin
Beiträge: 3531
Registriert: 29.08.2004 11:27
Computerausstattung: PC: MSI-Z590-GC; 32GB-DDR4, ICore9; 2TB M2 + 2x3TB-SATA2 HDD; Intel ICore9 @ 3600MHZ (Win11 Pro. 64-Bit),
Acer Aspire E15 (Win11 Home X64). Purebasic LTS 6.0
Kontaktdaten:

Re: ExcelFunktionen COMatePlus

Beitrag von Falko »

Hello Mesa,
do you mean this?

Code: Alles auswählen

XLSFunc_WriteLeftHeader(ExcelObject,"&P&N.xls")
This write in Excel following into the left header:
D:\Eigene Dokumente\Mappe1.xls
Sorry, but the geman Excel is another as english excel.
I found this here:
http://www.exceltip.com/st/Insert_heade ... l/454.html


here a site of MSDN : http://msdn.microsoft.com/en-us/library/ff822794

For english Excel you must insert &Z for Pathes. For german Excel this does nothing. I must insert &P for Path. :o


Regards,
Falko
Bild
Win10 Pro 64-Bit, PB_5.4,GFA-WinDOS, Powerbasic9.05-Windows, NSBasic/CE, NSBasic/Desktop, NSBasic4APP, EmergenceBasic
Mesa
Beiträge: 16
Registriert: 03.05.2012 18:23

Re: ExcelFunktionen COMatePlus

Beitrag von Mesa »

Hello,

I have the same problem.
In fact I try to get the total number of pages for printing.
In the header, there would be : Page 1/10.
This is the 10 (total number of pages) I am interested.
The solution ? = Hpagebreaks.count gives the number of page break, so I think the total number of page?

Ich habe das gleiche Problem.
In der Tat versuche ich, die Gesamtzahl der Seiten für den Druck zu bekommen.
In der Kopfzeile, gäbe es Seite 1/10 sein.
Dies ist die 10 Gesamtzahl der Seite bin ich interessiert.
Die Lösung? = Hpagebreaks.count gibt die Anzahl der Seitenwechsel, also denke ich, die Gesamtzahl der Seite?

Mesa.
Antworten