Excel Disphelper help

phishguy · Post by **phishguy** » Jun 05, 2009 17:58

BTW - I just found what I believe to be a complete list of Excel constants. I wish that I would have found this earlier!
http://msdn.microsoft.com/en-us/library ... 11%29.aspx

Zippy · Post by **Zippy** » Jun 05, 2009 18:19

Good. That's easier than trying to find "stuff" in the VB help.

I see that MSDN has the constants branched under "Office 2003" instead of "Excel 2003". Grumble. I suppose I should be grateful that they publish anything for free, MSDN used to be darned pricey. Also TECHNET. Gotcha coming and going.

phishguy · Post by **phishguy** » Jun 07, 2009 19:58

Hey Zippy, you wanted some demo code for the xlhelper wrapper. Ckeck out my latest strange demo here:
http://www.freebasic.net/forum/viewtopi ... 121#121121

You will need to use the latest version of the wrapper.

Using this method, it should be fairly easy to make an Excel Pong or Excel Tetris game. I'm not gonna though.

Zippy · Post by **Zippy** » Jun 07, 2009 20:45

PONGGGGGGGG???!!!

I did say I'd take any examples..

Heh.

phishguy · Post by **phishguy** » Jun 07, 2009 20:56

Yes, that example isn't exactly useful, is it? What can I say? I was bored. Oh well, it does show how to set row and column size, select a particular cell, and change the interior color of the cell.

Zippy · Post by **Zippy** » Jun 07, 2009 22:23

Yes, your example has some utility. It's all good.

=====

Sometimes I want to shower after I use Google.. There's.. an entire website devoted to Excel Games:

http://www.excelgames.org

and of course Pong:

http://www.excelgames.org/pong.asp

I didn't try it/any. I didn't look..

phishguy · Post by **phishguy** » Jun 08, 2009 19:32

Here's another worthless demo.

Code: Select all

#include "xlhelper.bas"
Screenres 161,9,32
Color Rgb(255,0,0),Rgb(255,255,255)
Cls
Draw String(1,1),"FreeBasic is Great!"
xlstart(1)
xlselect("A1:FA8")
xlrowsize(2)
xlcolumnsize(.25)
Dim a As Uinteger
For c As Integer = 1 To 161
    For r As Integer = 1 To 8
        a = Point(c,r)
        If a <> Rgb(255,255,255) Then
            xlselectcell(r,c)
            xlintcolor(a)
        End If
    Next r
    Sleep 1,0
Next c

xlsaved
xlrelease

phishguy · Post by **phishguy** » Oct 07, 2009 15:20

I hope Zippy is out there. I have another function I want to perform and I can't figure out how to do it. I want to find the row and/or column of the current cell position if a user moves to a different cell. If anyone could tell me how to do that I would once again be extremely gratefull.

Zippy · Post by **Zippy** » Oct 07, 2009 16:00

phishguy wrote:I hope Zippy is out there. I have another function I want to perform and I can't figure out how to do it. I want to find the row and/or column of the current cell position if a user moves to a different cell. If anyone could tell me how to do that I would once again be extremely gratefull.

Like ActiveCell?
http://www.excelforum.com/excel-program ... dress.html

ETA: Ok, might not be that easy.. Tell me it is..

phishguy · Post by **phishguy** » Oct 07, 2009 16:17

Thanks again Zippy. How did you find that so fast? I've been searching all morning. Anyway, in case anyone else would like to know, here is the code that does what I want.

Code: Select all

 dhGetValue("%s",@tvalue,xlapp,".activecell.address")
             print  *tvalue

BasicScience · Post by **BasicScience** » Dec 18, 2009 8:23

@ Phishguy

How can xlSaveAs be modified to perform a save ? I tried this simple approach... but it didn't work

Code: Select all

'saveas  - xlsaveas("filename.xls")
Sub xlsaveas(filename As String)
    dhcallmethod(xlapp, "activesheet.SaveAs(%s, %d)", filename,17  )
    dhPutValue(xlApp, ".ActiveWorkbook.Save")
    dhPutValue(xlApp, ".ActiveWorkbook.Saved = %b", TRUE)
End Sub

'save  - xlsave("filename.xls")
Sub xlsave(filename As String)
    dhcallmethod(xlapp, "activesheet.Save(%s, %d)", filename,17  )
    dhPutValue(xlApp, ".ActiveWorkbook.Save")
    dhPutValue(xlApp, ".ActiveWorkbook.Saved = %b", TRUE)
End Sub

Edit...

I realized that this post does not really give a clear picture of the problem.

I am able to create or open an excel file, and modify the cells. What I would like to do (but haven't figured out how) is to intermittently save the entire excel workbook (to save work thus far, in case of crash) and overwrite the original... but PREVENT Excel from issuing the popup query "A file named **** already exists in this location. Do you want to replace it? "

I face the same problem when exiting the FB program. I want to save and close the excel file... without having the popup about replacing the existing version".

Come to think of it... I'm confused about which functions in xlwrapper should be invoked to safely save and close an excel file. The wrapper includes:

xlquit
xlsaveas
xlsaved
xlrelease

which of these should be done, and in which order?

Thanks,

BasicScience

phishguy · Post by **phishguy** » Dec 18, 2009 21:19

I haven't been ignoring you. I haven't had any time to look into this. I will check and get back to you sometime this weekend.

BasicScience · Post by **BasicScience** » Dec 18, 2009 22:55

Thanks, I appreciate all the help. I didn't mean to be impatient... just didn't know whether you were still regularly checking the Library Forum.

One other work-around, but klutzy, would be to Close the Workbook File, Rename the excel file to some backup name, then Save the Active.Workbook with the original file name. This could all be done with the Shell or Rename / Kill. To do this... xlwrapper would need an xlclose proc.

Cheers.

phishguy · Post by **phishguy** » Dec 19, 2009 3:03

OK, add the following sub to the wrapper:

Code: Select all

sub xlsave()
    xlcm(xlapp, "ActiveWorkbook.Save")
end sub

Example usage:

Code: Select all

#include "xlhelper.bas"
xlopen("test.xls")
xlputvalue(5,5,"Test Data")
xlsave
xlrelease

XLRelease will safely release the objects and uninitialize. However, the Excel window will still be there.

XLQuit will do the same thing, but will close Excel.

XLSaved will let the Excel window close without prompting you about the workbook being saved.

I hope that this helps. I'm surprised that someone is actually using my wrapper.

BasicScience · Post by **BasicScience** » Dec 19, 2009 4:01

Works perfectly.... thanks.

Um... one other thing. The Excel Compatibility Checker pops up when attempting to save a file that contains newer-released excel features. For example, if xlintcolor() is used to set the internal color of a cell, then the the compatibility checker comes up with xlsaveas(). I presume this is because the new *.xlsx file format must be used. Is there a parameter to xlsaveas that will tell excel to use MS Office 2007 format?