Make an Excel WorkBook with two WorkSheets

 We use this:

With ActiveWorkbook, "Sheets" set Sheets
Method Sheets, "Add", , ActiveSheet, 1, xlWBATWorksheet

Where the first parameter is not passed (it is optional by design for this method, so we put a comma without value and M2000 interpreter to the job, to pass a specific variant which used for not sending something).

Another way to call this method using  named parameters, so now we pass the optional parameter:

Method Sheets, "Add", After:=ActiveSheet, Count:=1, Type:=xlWBATWorksheet

Named parameters my have different order:

Method Sheets, "Add", Count:=1, After:=ActiveSheet, Type:=xlWBATWorksheet

Info: The After and Type identifiers are shown blue, because are known identifiers, they are command, for M2000, so the editor of M2000 show it using blue color, but aren't those commands at this context, they used as names for the parameters. 

The code is a variant from the previous post. Here we see that the A3 cell at Name2 WorkSheet has a link to A1 cell at Name1 WorkSheet.

// const xlWBATChart=-4109& //Chart
// const xlWBATExcel4IntlMacroSheet=4& //Excel version 4 macro
// const xlWBATExcel4MacroSheet=3& //Excel version 4 international macro
const xlWBATWorksheet=-4167& //Worksheet

mydir$=path$(5) // Documents folder
target$="TestA.xlsx"
If exist(mydir$+target$) Then
Try ok {
	dos "del "+shortdir$(mydir$+target$);
}
If error or not ok Then break
End If

Declare withevents Excel "Excel.Application"
With Excel, "Workbooks" as WorkBooks
Method WorkBooks, "Add", xlWBATWorksheet
With Excel, "ActiveWorkbook" as ActiveWorkbook
With Excel, "ActiveSheet" as ActiveSheet
With ActiveSheet, "name", "Name1"
Try {
	With ActiveSheet, "Range" set Range0 ("A1")
	Method Range0, "Select"
	With Range0, "Value" as ThisValue, "Value" as ThisValue$
	ThisValue=1000
	With ActiveSheet, "Range" set Range0 ("A2")
	ThisValue$="Is a String"
	With ActiveSheet, "Range" set Range0 ("A3")
	ThisValue$="=A1"	// is a formula
}
With ActiveWorkbook, "Sheets" set Sheets
Method Sheets, "Add",  , ActiveSheet, 1, xlWBATWorksheet
With ActiveSheet, "name", "Name2"
Try {
	With ActiveSheet, "Range" set Range0 ("A1")
	Method Range0, "Select"
	With Range0, "Value" as ThisValue, "Value" as ThisValue$
	ThisValue=2000
	With ActiveSheet, "Range" set Range0 ("A2")
	ThisValue$="Is a String too"
	With ActiveSheet, "Range" set Range0 ("A3")
	ThisValue$="=Name1!A1"	// is a formula
}
again:
Try ok {
	Method ActiveWorkbook, "SaveAs", mydir$+target$, 51
}
If error or not ok Then If ask("File is open, close it and Try again")=1 Then Goto again
Method ActiveWorkbook, "Close", SaveChanges:=True
Method Excel, "Quit"
wait 1000
Declare Excel Nothing
Win "excel",quote$(mydir$+target$)
Print "Done"

Another example (later versions of M2000 can use properties with many indexes like the cells(), and also get string or value)
try {
	IF EXIST("MyTest.xlsx") THEN
		DOS "DEL "+QUOTE$(DIR$+"MyTest.xlsx"), 300;
	END IF
}
IF EXIST("MyTest.xlsx") THEN
	PRINT "CLOSE EXCEL FILE..."
	BREAK
END IF
DECLARE ExcelSheet "Excel.Sheet"
WITH ExcelSheet, "Application" set ExcelSheet.Application
WITH ExcelSheet.Application, "cells" as cells()
With ExcelSheet.Application, "ActiveSheet" as ActiveSheet
With ActiveSheet, "name", "MySheet1"
' row, line
cells(1,1)="Hello there"
s1=cells(1,1)
print s1, TYPE$(s1)="String"
cells(1,2)=TODAY  ' date type
cells(1,3)=NOW  ' date type
PRINT cells(1,2), TYPE$((cells(1,2)))="Date" ' need enclosed parenthesis, without these return the object under cells
cells(2,1)=100.3
cells(3, 1)=200.5
cells(4, 1)="=Sum(A2:A3)"  ' type
cells(2,2)=1234&   ' we place a long type 32 bit, but converted to Double
PRINT cells(4,1)=300.8 ' executed immediate
PRINT TYPE$((cells(4,1)))="Double"
PRINT TYPE$((cells(2,2)))="Double"  ' this is the proof of conversion to Double
REM PRINT TYPE$(ExcelSheet.Application)
METHOD ExcelSheet, "SaveAs", dir$+"MyTest.xlsx"
DECLARE ExcelSheet NOTHING
TRY {
	WIN  DIR$+"MyTest.xlsx"
}

Make an Excel WorkBook

This is an example of using COM objects from M2000. We will make an excel file, with three values in a worksheet called Name1.

Open M2000, write Edit A press Enter, then paste this code, press Esc, then write A and press enter.

As you see we have two different type of values for writing numeric and string values on selection. Because variables defining using With on objects are bound to objects via a hard link, when we set new object on Range0, variables (properties of object) reflect the new object (just using inside the hard link).

// const xlWBATChart=-4109& //Chart
// const xlWBATExcel4IntlMacroSheet=4& //Excel version 4 macro
// const xlWBATExcel4MacroSheet=3& //Excel version 4 international macro
const xlWBATWorksheet=-4167& //Worksheet

mydir$=path$(5) // Documents folder
target$="TestA.xlsx"
If exist(mydir$+target$) Then
Try ok {
	dos "del "+shortdir$(mydir$+target$);
}
If error or not ok Then break
End If

Declare withevents Excel "Excel.Application"
With Excel, "Workbooks" as WorkBooks
Method WorkBooks, "Add", xlWBATWorksheet
With Excel, "ActiveWorkbook" as ActiveWorkbook
With Excel, "ActiveSheet" as ActiveSheet
With ActiveSheet, "name", "Name1"
Try {
	With ActiveSheet, "Range" set Range0 ("A1")
	Method Range0, "Select"
	With Range0, "Value" as ThisValue, "Value" as ThisValue$
	ThisValue=1000
	With ActiveSheet, "Range" set Range0 ("A2")
	ThisValue$="Is a String"
	With ActiveSheet, "Range" set Range0 ("A3")
	ThisValue$="=A1"	// is a formula
}
again:
Try ok {
	Method ActiveWorkbook, "SaveAs", mydir$+target$, 51
}
If error or not ok Then If ask("File is open, close it and Try again")=1 Then Goto again
Method ActiveWorkbook, "Close", SaveChanges:=True
Method Excel, "Quit"
wait 1000
Declare Excel Nothing
Win "excel",quote$(mydir$+target$)
Print "Done"

Slide Show Wallpapers (at Windows Desktop - New)

Let say there is a galactica-wallpaper.jpg at My Documents folder. We can change desktop background image using SystemParametersInfoW function of User32. (tested on Windows 11 Pro 22H2).

Module TestMe {
	module changeWallPaper (strImagePath$){
		declare SystemParametersInfo lib "User32.SystemParametersInfoW" {Long action, Long Param, &pvParam$, Long fWinIni}
		const SPI_SETDESKWALLPAPER = 20&
		const SPIF_SENDWININICHANGE = &H2
		const SPIF_UPDATEINIFILE = &H1
		Call void SystemParametersInfo(SPI_SETDESKWALLPAPER, 0&, &strImagePath$, SPIF_UPDATEINIFILE Or SPIF_SENDWININICHANGE)
	}
	// 0x5 for Documents Folder - see: help path$()
	changeWallPaper path$(0x5)+"galactica-wallpaper.jpg"
}
TestMe

Now what if we have some jpg files at My Documents and we wish to cbange desktop background every half minute ?

This is the slide show. We get file names and put them to menu, without showing menu. We just read the menu items one by one.

Module TestMe {
	module changeWallPaper (strImagePath$){
		declare SystemParametersInfo lib "User32.SystemParametersInfoW" {Long action, Long Param, &pvParam$, Long fWinIni}
		const SPI_SETDESKWALLPAPER = 20&
		const SPIF_SENDWININICHANGE = &H2
		const SPIF_UPDATEINIFILE = &H1
		Call void SystemParametersInfo(SPI_SETDESKWALLPAPER, 0&, &strImagePath$, SPIF_UPDATEINIFILE Or SPIF_SENDWININICHANGE)
	}
	// 0x5 for My Documents Folder - see: help path$()
	Title "SlideShow", 0
	Try {
		Repeat
			dir path$(0x5)
			menu
			files + "jpg"
			dir user
			if menuitems>0 then
				for i=1 to menuitems
					try {
						changeWallPaper path$(0x5)+menu$(i)+".jpg"
					}
					wait 30000
				next
			end if
		Until menuitems=0
	}
	Title "M2000", 1
}
TestMe

And the best of all (using 0% processor for most time):

Module TestMe {
	module changeWallPaper (strImagePath$){
		declare SystemParametersInfo lib "User32.SystemParametersInfoW" {Long action, Long Param, &pvParam$, Long fWinIni}
		const SPI_SETDESKWALLPAPER = 20&
		const SPIF_SENDWININICHANGE = &H2
		const SPIF_UPDATEINIFILE = &H1
		Call void SystemParametersInfo(SPI_SETDESKWALLPAPER, 0&, &strImagePath$, SPIF_UPDATEINIFILE Or SPIF_SENDWININICHANGE)
	}
	// 0x5 for My Documents Folder - see: help path$()
	flush
	Title "SlideShow", 0
	Try {
		every 30000 {
			if empty  then
				dir path$(0x5)
				menu
				files + "jpg"
				dir user			
				if menuitems>0 then
					for i=1 to menuitems
						try {
							data path$(0x5)+menu$(i)+".jpg"
						}
					next
				end if
			end if
			if not empty then changeWallPaper
		}
	}
	Title "M2000", 1
}
TestMe

And final, using a mutex to run only once and without using a window at taskbar (running like a service).  When the first (which create the mutex) exit delete the mutex. (We have to clode by task manager, or automatic close at shutdown of windows.

Module TestMe {
	Declare m Mutex
	boolean pass
	try {
		method m, "CREATE", "Global\SLIDE10101001"
		pass=true
	}
	if pass else exit
	module changeWallPaper (strImagePath$){
		declare SystemParametersInfo lib "User32.SystemParametersInfoW" {Long action, Long Param, &pvParam$, Long fWinIni}
		const SPI_SETDESKWALLPAPER = 20&
		const SPIF_SENDWININICHANGE = &H2
		const SPIF_UPDATEINIFILE = &H1
		Call void SystemParametersInfo(SPI_SETDESKWALLPAPER, 0&, &strImagePath$, SPIF_UPDATEINIFILE Or SPIF_SENDWININICHANGE)
	}
	// 0x5 for My Documents Folder - see: help path$()
	flush
	Title "", 0
	Try {
		every 30000 {
			if empty  then
				dir path$(0x5)
				menu
				files + "jpg"
				dir user			
				if menuitems>0 then
					for i=1 to menuitems
						try {
							data path$(0x5)+menu$(i)+".jpg"
						}
					next
				end if
			end if
			if not empty then changeWallPaper
		}
	}
	Title "M2000", 1
}
TestMe: End

Some examples from kotlinlang.org as M2000 programs

Asynchronous

Its not the same type under the hood, so we have to adjust it:

Using stack of values (each trhead has one)

module threads1 {
	profiler
	for i=1 to 10
		thread {
			print "i="; number, "time=";timecount
			refresh
			thread this erase
		} as k interval 3000-i*200
		thread k execute push i
	next
	wait 4000
}
threads1

Using static variables (different set for each thread)

module threads2 {
	profiler
	for i=1 to 10
		thread {
			print "i="; j, "time=";timecount
			refresh
			thread this erase
		} as k interval 3000-i*200
		thread k execute static j=i
	next
	wait 4000
}
threads2

Object-Oriented

module ObjectOriented {
	class Person {
	private:
		name$
	public:
		module greed {
			error "abstract"
		}
	class:
		module Person (.name$){
		}
	}
	
	class FoodConsumer {
		module eat {
			error "abstract"
		}
		module pay (amount as currency) {
			print format$("Delicious! Here's {0} bucks!", amount)
		}
	}
	class RestaurantCustomer as Person as FoodConsumer {
	private:
		dish$=""
	public:
		module order {
			print format$("{0}, please!", .dish$)
		}
		module final eat  {
			print format$("*Eats {0}*", .dish$)
		}
		module final greed {
			print format$("It's me, {0}", .name$)
		}
	class:
		module RestaurantCustomer (.name$, .dish$) {
		}
	}
	sam = RestaurantCustomer("Sam", "Mixed salad")
	sam.greed
	sam.order
	sam.eat
	sam.pay 10
}
ObjectOriented

Functional

The Kotlin Example

The M2000 example using tuple.

Module Functional {
	function message(sender as string, body as string, isRead as boolean=false) {
			=((Sender, body, isRead),)  // array in an array - need for using append (an array to an array)
	}
	// define messages as a pointer to an array with zero items:
	messages=(,)
	Append messages, message("Ma", "Hey! Where are you?")
	Append messages, message("Adam", "Everything going according to plan today?")
	Append messages, message("Ma", "Please reply. I've lost you!")
	// filter items by the Sender name to be unique
	distinct =lambda ->{
		mylist=list
		=lambda myList ( k as array)->{
			=true
			if not exist(mylist, k#val$(0)) then append mylist,  k#val$(0) else =false
		}
	}
	// return the Sender with most messages on a fold function
	MostMessages =lambda ->{
		mylist=list
		=lambda myList (k as array, which$)->{
			if not exist(mylist, k#val$(0)) then
				append mylist,  k#val$(0):=1
			else
				return mylist,  k#val$(0):=mylist(k#val$(0))+1
			end if
			if len(which$)>1 then
				if  mylist(which$)<mylist(k#val$(0)) then which$=k#val$(0)
			else
				which$=k#val$(0)		
			end if
			push which$
		}
	}
	// get an array and extract only the Sender
	map1 =lambda (k as array)->{
		push k#val$(0)
	}
	print "Sender with most messages:";messages#fold$(MostMessages(), "")
	print "Senders:";"["+messages#filter(distinct(),(,))#map(map1)#Sort()#str$(", ")+"]"
}
Functional

Revision 34, Version 12

For this revision I do these:

1. Remove a bug from rev 33, so now eertree module run as expected.

2. Now print to One Note for Windows 10 works (also works fir any printer). I found the need for placing a document name to printer, which some other printers not needed - like "Microsoft Print to PDF" which works with empty document name. So now M2000 insert a dummy documen name "M2000 printing" and we get the printout.

Check CAL module from Info (just execute Printer ? to choose One Note for Windows ... and then write CAL and press enter to execute this module, which print calendars to printer)

A simple example for printing:

// choose One Note for Windows...
Printer ?
//
Printer {
    form 80, 66
    Pen 9
    Print "This is a line of text"
    // use 2 for center text
    Report 2, {Proportional spacing also
    }
}