disphelper help

[wallyfblu]

Hi, I try to use disphelper to automate Excel, but I don't know how to put a value in a specific cell/range

Any help? Where could I find information about disphelper use?
For example: what %o % s ... mean; where use dhPutValue instead dhCallMethod and so on.

thank's in advance


here my poor code

Code: Select all

#define UNICODE
#include "disphelper/disphelper.bi"

sub Open_excel( )
DISPATCH_OBJ(exApp)
DISPATCH_OBJ(exSheet)
DISPATCH_OBJ(exRange)

dhInitialize( TRUE )
dhToggleExceptions( TRUE )

dhCreateObject( "Excel.Application", NULL, @exApp )
	
dhPutValue( exApp, "Visible=%b", TRUE )
	
dhCallMethod( exApp, "Workbooks.Add", "" ) '---> OK
dhGetValue("%o",@exSheet,exApp,"ActiveSheet")'---> it's right?
dhGetValue("%o",@exRange,exSheet,"cells")'---> it's right?
    
'this not works. Error: object don't support propertie or method: Range 
dhPutValue(exRange,"Range('A1')=%s","a string")
	
'this fills all sheet cells with 'a string	
dhPutValue(exRange,"cells(1,1)=%s","a string")
	
dhCallMethod( exApp, "Application.Quit", "" )
    
SAFE_RELEASE( exApp )
dhUninitialize( TRUE )
end sub

''Main
Open_excel
End

[phishguy]

If you figure this out, let me know. I currently send data into excel by putting it into the keyboard buffer. It works, but isn't very flexible.

[Burt]

Try the following:

Code: Select all

dhCallMethod( exApp, "Workbooks.Add", "" )
dhGetValue("%o",@exSheet,exApp,"ActiveSheet")
dhGetValue("%o",@exRange,exSheet,"Range(%s)","A1")

dhPutValue(exRange,"Value=%s","a string")    
dhPutValue(exSheet,"cells(%u,%u)=%s",2,3,"another string")

%o,%s,%u are the types of the parameters passed as they appear in the call
(Object, string, unsigned int)

[phishguy]

Thanks Burt! I've been wanting to know how to do that for a long time.

[phishguy]

OK, I've gotten over my giddyness after finding out how to do this. Now, is there a way to send cell formatting information? (cell size, color, font, borders, etc.)

Thanks

[wallyfblu]

Thanks a lot, this is what I need. Now I try to go on and use others properties/methods of Excel.

[wallyfblu]

Hi phishguy, I played around with the Burt code and I realized that you have to create a object for every VBA object.
The code:

Code: Select all

#define UNICODE
#include "disphelper/disphelper.bi"

sub Open_excel( )
	DISPATCH_OBJ(exApp)
	DISPATCH_OBJ(exsheet)
	DISPATCH_OBJ(exrange)
	DISPATCH_OBJ(excolumn)
	DISPATCH_OBJ(exfont)	

	dhInitialize( TRUE )
	dhToggleExceptions( TRUE )

	dhCreateObject( "Excel.Application", NULL, @exApp )
	dhPutValue( exApp, "Visible=%b", TRUE )
	
	dhCallMethod( exApp, "Workbooks.Add", "" )
	dhGetValue("%o",@exSheet,exApp,"ActiveSheet")
	dhGetValue("%o",@exRange,exSheet,"Range(%s)","A1")
	dhGetValue("%o",@excolumn,exsheet,"Columns(%u)",1)
	dhGetValue("%o",@exfont,exRange,"Font")
	
	dhPutValue(exRange,"Value=%s","a string")    
	dhPutValue(exSheet,"cells(%u,%u)=%s",2,3,"another string")
	dhPutValue(excolumn,"ColumnWidth=%u",15)
	dhPutValue(exfont,"bold=%b",True)
	
	'dhCallMethod( myApp, "Application.Quit", "" ) ---> OK

	SAFE_RELEASE( exApp )
	SAFE_RELEASE( exsheet )
	SAFE_RELEASE( exrange )
	SAFE_RELEASE( excolumn )
	SAFE_RELEASE( exfont )

	dhUninitialize( TRUE )
end sub

''Main
	Open_excel
	End

[phishguy]

@wallyfblu
Thanks for the info. That helps me alot. I have another issue I'm trying to figure out. My previous version of a wedge program I wrote used ShellExecEx to run Excel and I tested for Excel closing by using GetExitCodeProcess. Using this new (for me) object mode I can't figure out how to detect when Excel is closed. Does anyone have any guidance on how this would be accomplished?

Another thing I can't figure out. In your example I can't figure out how to change the bold of any other cells except the specified A1.


[edit]

Ok I figured out how to set the bold of a specified column. Now I can't figure our how to specify a particular cell.

[edit again]

I figured it out. Perhaps not the best way though. Also, how can I move to a different active cell for user input?

Code: Select all

 
#define UNICODE
#include "disphelper/disphelper.bi"


Sub Open_excel( )
    dim  x as integer
dim as integer columnwidth(1 to 5) = {10,15,20,5,30}
dim as string colloc(1 to 5) = {"A","B","C","D","E"}
    dim  as string header(1 to 5) = {"Count","Tag ID","Date","Time","User Data"}
        DISPATCH_OBJ(exApp)
        DISPATCH_OBJ(exsheet)
        DISPATCH_OBJ(exrange)
        DISPATCH_OBJ(excolumn)
        DISPATCH_OBJ(exfont)        

        dhInitialize( TRUE )
        dhToggleExceptions( TRUE )

        dhCreateObject( "Excel.Application", NULL, @exApp )
        dhPutValue( exApp, "Visible=%b", TRUE )
        
        dhCallMethod( exApp, "Workbooks.Add", "" )
        dhGetValue("%o",@exSheet,exApp,"ActiveSheet")
        dhGetValue("%o",@exRange,exSheet,"Range(%s)","A1")
        dhGetValue("%o",@excolumn,exsheet,"Columns(%u)",1)
        dhGetValue("%o",@exfont,exRange,"Font")
        
        for x = 1 to 5
            dhGetValue("%o",@exRange,exSheet,"Range(%s)",colloc(x)&"1")
            dhPutValue(exRange,"Value=%s",header(x))    
            dhGetValue("%o",@exfont,exRange,"Font")
            dhPutValue(exfont,"bold=%b",True)
            dhGetValue("%o",@excolumn,exsheet,"Columns(%u)",x)
            dhPutValue(excolumn,"ColumnWidth=%u",columnwidth(x))
            next x
                             
        'dhCallMethod( myApp, "Application.Quit", "" ) ---> OK

        SAFE_RELEASE( exApp )
        SAFE_RELEASE( exsheet )
        SAFE_RELEASE( exrange )
        SAFE_RELEASE( excolumn )
        SAFE_RELEASE( exfont )

        dhUninitialize( TRUE )
End Sub

''Main
        Open_excel
        End
 

[wallyfblu]

From Burt example:

Code: Select all

dhPutValue(exSheet,"cells(%u,%u)=%s",2,3,"another string")

In this way you can select any cell you want, for example using a 'for' cicle.

[Burt]

To change the active cell to "A10", once you have created the exSheet object:

Code: Select all

dhCallMethod( exSheet, "Range(%s).Select", "A10" )

[phishguy]

Burt, That does put the Active cell where I want, But I get the error "call was rejected by callee" when I try to manually enter data into the form.

Code: Select all

#define UNICODE
#include "disphelper/disphelper.bi"


Sub Open_excel( )
    dim as integer i,x,dpos,dcount,y,f
    dim as integer hpos(0 to 10)
dim as integer columnwidth(1 to 5) = {7,18,10,10,30}
dim as string colloc(1 to 5) = {"A","B","C","D","E"}
    dim  as string header(1 to 5) = {"Count","Tag ID","Date","Time","User Data"}
   dim as string reader,tempheader
   ' header(1)="=" & chr$(34) & header(1) & chr$(34)
        DISPATCH_OBJ(exApp)
        DISPATCH_OBJ(exsheet)
        DISPATCH_OBJ(exrange)
        DISPATCH_OBJ(excolumn)
        DISPATCH_OBJ(exfont)   
        
        dhInitialize( TRUE )
        dhToggleExceptions( TRUE )

        dhCreateObject( "Excel.Application", NULL, @exApp )
        dhPutValue( exApp, "Visible=%b", TRUE )
        
        dhCallMethod( exApp, "Workbooks.Add", "" )
        
        dhGetValue("%o",@exSheet,exApp,"ActiveSheet")
        
        dhGetValue("%o",@exRange,exSheet,"Range(%s)","A1")
        dhGetValue("%o",@excolumn,exsheet,"Columns(%u)",1)
        dhGetValue("%o",@exfont,exRange,"Font")
        
        for x = 1 to 5
            dhGetValue("%o",@exRange,exSheet,"Range(%s)",colloc(x)&"1")
            dhPutValue(exRange,"Value=%s",header(x))    
            dhGetValue("%o",@exfont,exRange,"Font")
            dhPutValue(exfont,"bold=%b",True)
            dhGetValue("%o",@excolumn,exsheet,"Columns(%u)",x)
            dhPutValue(excolumn,"ColumnWidth=%u",columnwidth(x))
        next x
        
          open com "com1:9600,n,8,1,cs0,ds0,cd0,rs" as 1
         do
              while loc(1) = 0
                  if inkey$<>"" then
                      exit do
                  end if
                  sleep 1,1
              wend
              
              input #1,reader
             dcount=0
             hpos(dcount)=1
             do
                dpos = instr(hpos(dcount)+1,reader," ")
               if dpos<>0 then
                  dcount=dcount+1
                 hpos(dcount)=dpos
                  end if
                  loop until dpos=0
                  dcount=dcount+1
                  hpos(dcount)=len(reader)
              i=i+1
              header(1)=str$(i)
              header(3)=""
              header(4)=""
              for y = 1 to dcount
                  tempheader=mid$(reader,hpos(y-1),hpos(y)-hpos(y-1))
              f=0
              if instr(tempheader,":")>0 then
                  header(4)=tempheader
                  f=1
              end if
              if instr(tempheader,"-")>0 then
                  header(3)=mid$(tempheader,4,3) & left$(tempheader,3) & right$(tempheader,4)
                  f=1
                  end if
              if f=0 then
                  header(2)= "=" & chr$(34) & tempheader & chr$(34)
                    end if
              next y
              if header(4)="" then
                  header(4)=time$
              end if
              if header(3)="" then
              header(3)=date$
              end if
              
              for x = 1 to 4
                   dhGetValue("%o",@exRange,exSheet,"Range(%s)",colloc(x)&str$(i+1))
            dhPutValue(exSheet,"cells(%u,%u)=%s",i+1,x,header(x))
            
              dhCallMethod( exSheet, "Range(%s).Select", "E" & str$(I+1) ) ' error after entering data on this cell
           
        next x
        loop
            close #1      
        'dhCallMethod( myApp, "Application.Quit", "" ) ---> OK

        SAFE_RELEASE( exApp )
        SAFE_RELEASE( exsheet )
        SAFE_RELEASE( exrange )
        SAFE_RELEASE( excolumn )
        SAFE_RELEASE( exfont )

        dhUninitialize( TRUE )
End Sub

''Main
        Open_excel
        End
 

[phishguy]

I am still trying to figure out how to use Disphelper with Excel. I figured that I would try the C example for Excel at the Disphelper CVS site. I figured out how to translate most of it. I don't know how to translate the sections that have "with" "with1" and the corresponding "end_with" statements. Here is what I've got so far. Any help would be mucho appreciated"

Code: Select all

#define UNICODE
#include "disphelper/disphelper.bi"
dim as integer i


'int ExcelSample1(void)
'{
	DISPATCH_OBJ(xlApp)
	DISPATCH_OBJ(xlRange)
	DISPATCH_OBJ(xlChart)
	DISPATCH_OBJ(xlCells)
    DISPATCH_OBJ(xlSheet)
	dim as string Headings(1 to 5) = { "Mammals", "Birds", "Reptiles", "Fishes", "Plants" }

	dhInitialize(TRUE)
	dhToggleExceptions(TRUE)

	 dhCreateObject("Excel.Application", NULL, @xlApp)

	dhPutValue(xlApp, ".DisplayFullScreen = %b", TRUE)
	dhPutValue(xlApp, ".Visible = %b", TRUE)

	'/* xlApp.Workbooks.Add */
	 dhCallMethod(xlApp, ".Workbooks.Add") 

	'/* Set the worksheet name */
	dhPutValue(xlApp, ".ActiveSheet.Name = %T", wstr("Critically Endangered"))

'	/* Add the column headings */
	for i=1 to 5
	
		dhPutValue(xlApp, ".ActiveSheet.Cells(%d, %d) = %S", 1, i, wstr(headings(i)))
	next i

	'/* Format the headings */
	'WITH1(xlCells, xlApp, ".ActiveSheet.Range(%S)", "A1:E1")
'	{
		dhPutValue(xlCells, ".Interior.Color = %d", RGB(&hee,&hdd,&h82))
		dhPutValue(xlCells, ".Interior.Pattern = %d", 1) ' /* xlSolid */
		dhPutValue(xlCells, ".Font.Size = %d", 13)
		dhPutValue(xlCells, ".Borders.Color = %d", RGB(0,0,0))
		dhPutValue(xlCells, ".Borders.LineStyle = %d", 1)' /* xlContinuous */
		dhPutValue(xlCells, ".Borders.Weight = %d", 2)   ' /* xlThin */

	'}
  '  END_WITH(xlCells)

	'WITH(xlSheet, xlApp, ".ActiveSheet")
	'{
	'	/* Set some values */
		dhPutValue(xlSheet, ".Range(%S).Value = %d", "A2", 184)
		dhPutValue(xlSheet, ".Range(%S).Value = %d", "B2", 182)
		dhPutValue(xlSheet, ".Range(%S).Value = %d", "C2", 57)
		dhPutValue(xlSheet, ".Range(%S).Value = %d", "D2", 162)
		dhPutValue(xlSheet, ".Range(%S).Value = %d", "E2", 1276)

		'/* Output data source */
		dhCallMethod(xlSheet, ".Range(%S).Merge", "A4:E4")
		dhPutValue(xlSheet, ".Range(%S).Value = %S", "A4", "Source: IUCN Red List 2003 (http://www.redlist.org/info/tables/table2.html)")

	'	/* Apply a border around everything. Note '%m' means missing. */
		dhCallMethod(xlSheet, ".Range(%S).BorderAround(%d, %d, %m, %d)", "A1:E2", 1, 2, RGB(0,0,0))

'		/* Set column widths */
		dhPutValue(xlSheet, ".Columns(%S).ColumnWidth = %e", "A:E", 12.5)

'	} 
  '  END_WITH(xlSheet)

	'/* Set xlRange = xlApp.ActiveSheet.Range("A1:E2") */
	 dhGetValue("%o", @xlRange, xlApp, ".ActiveSheet.Range(%S)", "A1:E2") 

	'/* Set xlChart = xlApp.ActiveWorkbook.Charts.Add */
	 dhGetValue("%o", @xlChart, xlApp, ".ActiveWorkbook.Charts.Add")

	'/* Set up the chart */
	dhCallMethod(xlChart, ".ChartWizard(%o, %d, %d, %d, %d, %d, %b, %S)",xlRange, -4100, 7, 1, 1, 0, FALSE, "Critically Endangered Plants and Animals")

	dhPutValue(xlChart, ".HasAxis(%d) = %b", 3, FALSE) ' /* xlSeries */

'	/* Put the chart on our worksheet */
	dhCallMethod(xlChart, ".Location(%d,%S)", 2, "Critically Endangered")

'cleanup:
	dhToggleExceptions(FALSE)

	dhPutValue(xlApp, ".ActiveWorkbook.Saved = %b", TRUE)

	SAFE_RELEASE(xlRange)
	SAFE_RELEASE(xlChart)
	SAFE_RELEASE(xlApp)

	dhUninitialize(TRUE)
'	return 0;
'}

[Burt]

You got it right Phishguy, you just missed the initialization of xlCells and xlSheet

Attached working code:

Code: Select all

#define UNICODE
#include "disphelper/disphelper.bi"
Dim As Integer i


'int ExcelSample1(void)
'{
        DISPATCH_OBJ(xlApp)
        DISPATCH_OBJ(xlRange)
        DISPATCH_OBJ(xlChart)
        DISPATCH_OBJ(xlCells)
    DISPATCH_OBJ(xlSheet)
        Dim As String Headings(1 To 5) = { "Mammals", "Birds", "Reptiles", "Fishes", "Plants" }

        dhInitialize(TRUE)
        dhToggleExceptions(TRUE)

        dhCreateObject("Excel.Application", NULL, @xlApp)

        dhPutValue(xlApp, ".DisplayFullScreen = %b", TRUE)
        dhPutValue(xlApp, ".Visible = %b", TRUE)

        '/* xlApp.Workbooks.Add */
        dhCallMethod(xlApp, ".Workbooks.Add") 

        '/* Set the worksheet name */
        dhPutValue(xlApp, ".ActiveSheet.Name = %T", wstr("Critically Endangered"))

'        /* Add the column headings */
        For i=1 To 5
        
                dhPutValue(xlApp, ".ActiveSheet.Cells(%d, %d) = %S", 1, i, wstr(headings(i)))
        Next i

        '/* Format the headings */
        'WITH1(xlCells, xlApp, ".ActiveSheet.Range(%S)", "A1:E1")
'        {
         dhGetValue("%o",@xlSheet,xlApp,"ActiveSheet")
         dhGetValue("%o",@xlCells,xlSheet,"Range(%s)","A1:E1")
                
                dhPutValue(xlCells, ".Interior.Color = %d", RGB(&hee,&hdd,&h82))
                dhPutValue(xlCells, ".Interior.Pattern = %d", 1) ' /* xlSolid */
                dhPutValue(xlCells, ".Font.Size = %d", 13)
                dhPutValue(xlCells, ".Borders.Color = %d", RGB(0,0,0))
                dhPutValue(xlCells, ".Borders.LineStyle = %d", 1)' /* xlContinuous */
                dhPutValue(xlCells, ".Borders.Weight = %d", 2)   ' /* xlThin */

        '}
  '  END_WITH(xlCells)

        'WITH(xlSheet, xlApp, ".ActiveSheet")
        '{
        '        /* Set some values */
                dhPutValue(xlSheet, ".Range(%s).Value = %d", "A2", 184)
                dhPutValue(xlSheet, ".Range(%s).Value = %d", "B2", 182)
                dhPutValue(xlSheet, ".Range(%s).Value = %d", "C2", 57)
                dhPutValue(xlSheet, ".Range(%s).Value = %d", "D2", 162)
                dhPutValue(xlSheet, ".Range(%s).Value = %d", "E2", 1276)

                '/* Output data source */
                dhCallMethod(xlSheet, ".Range(%s).Merge", "A4:E4")
                dhPutValue(xlSheet, ".Range(%s).Value = %s", "A4", "Source: IUCN Red List 2003 (http://www.redlist.org/info/tables/table2.html)")

        '        /* Apply a border around everything. Note '%m' means missing. */
                dhCallMethod(xlSheet, ".Range(%s).BorderAround(%d, %d, %m, %d)", "A1:E2", 1, 2, RGB(0,0,0))

'                /* Set column widths */
                dhPutValue(xlSheet, ".Columns(%s).ColumnWidth = %e", "A:E", 12.5)

'        } 
  '  END_WITH(xlSheet)

        '/* Set xlRange = xlApp.ActiveSheet.Range("A1:E2") */
        dhGetValue("%o", @xlRange, xlApp, ".ActiveSheet.Range(%s)", "A1:E2") 

        '/* Set xlChart = xlApp.ActiveWorkbook.Charts.Add */
        dhGetValue("%o", @xlChart, xlApp, ".ActiveWorkbook.Charts.Add")

        '/* Set up the chart */
        dhCallMethod(xlChart, ".ChartWizard(%o, %d, %d, %d, %d, %d, %b, %s)",xlRange, -4100, 7, 1, 1, 0, FALSE, "Critically Endangered Plants and Animals")

        dhPutValue(xlChart, ".HasAxis(%d) = %b", 3, FALSE) ' /* xlSeries */

'        /* Put the chart on our worksheet */
        dhCallMethod(xlChart, ".Location(%d,%s)", 2, "Critically Endangered")

'cleanup:
        dhToggleExceptions(FALSE)

        dhPutValue(xlApp, ".ActiveWorkbook.Saved = %b", TRUE)

        SAFE_RELEASE(xlRange)
        SAFE_RELEASE(xlChart)
        SAFE_RELEASE(xlApp)

        dhUninitialize(TRUE)
'        return 0;
'}

[phishguy]

Thanks Burt. I think this example would be helpful for anyone wishing to send (and get) data from Excel. It has helped me immensely.

BTW, any thoughts on your example for the active cell? Do I need to do another dhcallmethod to return to the previous state? I'm still a little confused on this part. It almost works until I manually enter any data on the active cell. At that point it will bomb out with the error unless I press enter after the data entry.

[phishguy]

Ok, I figured out my problem. You can't get or send objects if a cell is in edit mode. So, to get around this I used the sendkey API function to send a carriage return. Of course I will have to add some code to make sure the spreadsheet is the foreground window.

One more question. How do I format a column as text?