Excel helper wrapper

[phishguy]

Added:

xlsplit

Modified:
xlsort - fixed bug when using with Excel 2000.

[phishguy]

The xlhelper wrapper is now on a Windows Live Skydrive (see link in first post). Updates to the wrapper and examples will now be posted at that link. I am currently working on creating example code listings for each command and will eventually create documentation for the wrapper (if I can find time).

[phishguy]

Added:

xlsheettabcolor (requies Excel 2003 or above)
xlsheetbackground


http://cid-53bce305c32e0874.skydrive.li ... bcolor.bas


http://cid-53bce305c32e0874.skydrive.li ... ground.bas

[phishguy]

Added
xlscrollup
xlscrolldown
xlscrollleft
xlscrollright
xlzoom


example:

http://cid-53bce305c32e0874.skydrive.li ... scroll.bas


http://cid-53bce305c32e0874.skydrive.li ... xlzoom.bas

[phishguy]

Added:

xlgetworkbook 'gets the workbook object
xladdmodule 'adds a VBcomponent module
xladdmacro(strcode) 'adds the macro defines in the string
xlrunmacro("MyMacro") 'runs the macro "MyMacro"
xladdbutton(100,100,100,40) 'add a button
xlshapetext("My Macro") 'set the button text
xlonaction("MyMacro") 'set the button action
xlshapetextcolor(rgb(0,0,255)) 'set the button color
xlshapetextfont("Lucida Handwriting") 'set the button font
xlshapetextfontsize(16) 'set the font size


Note: Macro security must be diabled for the xladdmacro function to work.

[phishguy]

Added:
xlprint

This will print the current sheet to the active printer with an optional number of copies.

Note: If you happen to have Excel, this can be a quick and convenient way to print out your graphics screen.

Here is an example:

Code: Select all

#include "xlhelper.bas"
screenres 400,480,8
color 0,15
cls
circle (200,239),100,0 
draw string (120,239),"Freebasic is Great!"
bsave "temp.bmp",0 'save screen as bitmap
xlstart(1,0) 'open excel hidden
xlputimagefromfile("temp.bmp",0,0) 'put image in spreadsheet
xlprint 'print spreadsheet
xlsaved 'supress file save dialog
xlquit 'close excel workbook
xlrelease 'release excel objects
kill ("temp.bmp") 'delete temporary bitmap file
sleep
end

You also would have the flexibility of rotating and/or resizing the image before printing.

[phishguy]

It's been awhile since I added anything to this wrapper. I started playing around with graphing and I found that I hate the chart wizard. So, I've started adding commands to add and change chart parameters without the wizard. I've just started. So, all I have so far is:

XLChartAddV2
XLChartType

This works on Excel 2007. But it doesn't work on Excel 2000. I'm not sure about other versions of Excel.

You will need the latest version of xlhelper. The link is in the first post.

Here is an example that shows pretty fast real time graph updates.

Code: Select all

#include "xlhelper.bas"

xlstart(1)

Dim sval(255) As String

For x As Integer = 0 To 255
    sval(x) = "0"
    Print sval(x)
Next x

xlpastearray(sval(),,1)
Dim xlwind As hwnd
xlwind = xlhandle
setforegroundwindow(xlwind)

xlgetrange("A1:A256" )
xlchartaddV2
xlcharttype(4,false)

For z As Integer = 1 To 5   
    
    For y As Integer = 1 To 50 
        
        For x As Integer = 0 To 255
            sval(x) = Str(Sin(x/y) * 20)
        Next x   
        
        xlpastearray(sval(),,1)
        Sleep 50
    Next y
    
    For y As Integer = 50 To 1 Step -1 
        
        For x As Integer = 0 To 255
            sval(x) = Str(Sin(x/y) * 20)
        Next x   
        
        xlpastearray(sval(),,1)
        Sleep 50
    Next y
    
Next z 

xlsaved
xlquit
xlrelease

<edit>
Here is another one with 2 data sets.

Code: Select all

#include "xlhelper.bas"

xlstart(1)

Dim sval(1 to 255,1 to 2) As String
 
For x As Integer = 1 To 255
    sval(x,1) = "0"
    sval(x,2) = "0"
Next x

xlpastearray(sval(),"A1",true)
Dim xlwind As hwnd
xlwind = xlhandle
setforegroundwindow(xlwind)

xlgetrange("A1:B255" )
xlchartaddV2
xlcharttype(4,false)

For z As Integer = 1 To 5   
    
    For y As Integer = 1 To 50 
        
        For x As Integer = 1 To 255
            sval(x,1) = Str(Sin(x/y) * 20 - 20)
            sval(x,2) = str(sin((255-x)/y) * 20 + 20)
        Next x   
        
        xlpastearray(sval(),"A1",true)
        Sleep 50
    Next y
    
    For y As Integer = 50 To 1 Step -1 
        
        For x As Integer = 1 To 255
            sval(x,1) = Str(Sin(x/y) * 20 - 20)
            sval(x,2) = str(sin((255-x)/y) * 20 + 20)
        Next x   
        
        xlpastearray(sval(),"A1",true)
        Sleep 50
    Next y
    
Next z 

xlsaved
xlquit
xlrelease

[phishguy]

I have added a few more functions recently.

xltableadd
xltablestyle
xltableshowtotals
xltableaddcalc
xltablerename
xlfiledialog
xlsetkey
xlsort2
xlcellsselect
xlrangeselect2

Here is an example for sorting on a key and creating a table.

Code: Select all

#include "xlhelper.bas"

'start Excel with 1 worksheets
xlstart(1)

dim gpa as single
dim rowvalue as integer = 3
dim offset as integer = 3
dim as string names(5) = {"Chris","Ryan","Betty","Fred","Ann"}
dim as string classname(5) = {"English","History","Physics","Geometry","Health"}
dim as string header(4) = {"Student","Class","Semester","GPA"}

'put in the header string values
for x as integer = 0 to 3
    xlputvalue(rowvalue,x + 1 + offset,header(x))
next x

'fill the worksheet with data
for semester as integer = 1 to 2
    for hclass as integer = 0 to 4
        for hname as integer = 0 to 4
            gpa = int(rnd * 30) / 10 + 1
            rowvalue += 1
            xlputvalue(rowvalue,offset + 1,names(hname))
            xlputvalue(rowvalue,offset + 2,classname(hclass))
            xlputvalue(rowvalue,offset + 3,str(semester))
            xlputvalue(rowvalue,offset + 4,str(gpa))
        next hname
    next hclass
next semester

'set the number format for the GPA column
xlcellsselect(4,offset + 4,rowvalue,offset + 4)
xlnumberformat("0.0")

'set the sort key for the name column data
xlsetkey(4,offset + 1,rowvalue,offset + 1)

'sort the entire range of data using the sort key
xlsort2(4,offset + 1,rowvalue,offset + 4)

'select a range for the table
xlrangeselect2(3,offset + 1,rowvalue,offset + 4)

'add the table
xltableadd

'set the table style
xltablestyle

'select cell A1
xlcellselect(1,1)

'release the objects and uninitialize Excel
xlrelease

[MaverickM]

This looks like a really useful wrapper! However, i think the download link is broken? It just sends me to a one-drive log-in page.

Can you provide an alternative link? Thanks!

[phishguy]

Try this link:
https://onedrive.live.com/redir?resid=5 ... ile%2c.bas

[MaverickM]

That did the trick! Thanks.

[JohnK]

This gives me an error trying to download off OneDrive (I refer to it as NoneDrive).
Since it is a .bas file could you please put it in a 'code box' in this thread?
Thanks

[phishguy]

Sure thing. See first post.

[BasicScience]

Excel wrapper is a great tool, but even after closing it down with xlquit and xlrease there still is an excel.exe process running (as shown by the Processes tab in the Task Manager. Moreover, if you make several calls to xlstart and xlopen, then there are multiple processes that are left running.

[Zippy]

Excel wrapper is a great tool, but even after closing it down with xlquit and xlrease there still is an excel.exe process running (as shown by the Processes tab in the Task Manager. Moreover, if you make several calls to xlstart and xlopen, then there are multiple processes that are left running.

I can't replicate your experience that excel.exe remains after using xlquit() and xlrelease() to close and release a single instance. Would be helpful to know what you are doing, exactly - are you opening an existing book/sheet or creating one, and what exactly are you doing with it after it is open? Can you post compilable and runnable test code that demos your fail?

You can effect/control multiple instances (multiple open books) by copying the global internal "xlapp" variable that is used when an instance of excel is created. Hopefully this code will be comprehensible:

Code: Select all

'xlhelper.bas test, multiple instances
'' Does excel.exe remain after xlquit() & xlrelease()
''  Compile as console prog.
''  Tested Win10 64bit, Excel 2013, FB 1.04.0 (10-01-2015), built for win32 (32bit) 
'
#include "xlhelper.bas"
'
'create 2 placeholder structures, 1 for each
'' book we are opening or creating
dim as IDispatch ptr book1,book2
'
'create 1st book
xlstart()
print "start book1 xlapp = ";xlapp
book1=xlapp 'store book1 pointer
'
sleep 2000
'
'create 2nd book
xlstart()
print "start book2 xlapp = ";xlapp
book2=xlapp 'store book2 pointer
'
sleep 2000
'
xlapp=book1 'set internal pointer to book1
xlquit()
xlrelease()
print "End book1 xlapp = ";xlapp
'
xlapp=book2 'set interal pointer to book2
xlquit()
xlrelease()
print "End book2 xlapp = ";xlapp
'
print
print "Exit in 5 seconds....."
sleep 5000

Note: What you see here is all of the testing I've performed of this premise. Caveat emptor.
.