New call-to-action
Results 1 to 8 of 8

Thread: Excel controlled by Alpha, another demo

  1. #1
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Excel controlled by Alpha, another demo

    The function below

    opens excel
    creates a new workbook
    resizes some cells to square
    draws some silly block animation
    saves and closes the workbook


    Just another demo of what Alpha's ole methods can do.

    What can I say, it is Friday!

    Code:
    'Date Created: 09-Jan-2003 08:35:07 PM
    'Last Updated: 17-Apr-2007 08:36:56 AM
    'Created By : original framework by John Magno - http://www.JohnMagno.com
    'Updated By  : 
    FUNCTION ex_demo AS C (excelname AS C) 
    'usage is like......    ex_demo("C:\demo.xls")
    Dim xlApp as p 
    dim myCell as c 
    xlApp = ole.create("Excel.Application") 
    'xlApp.Workbooks.Open(excelname) 
    xlApp.Workbooks.Add()
    xlApp.Columns("A:BZ").Select()
    xlApp.Selection.ColumnWidth = 2
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Visible = .T. 'comment this line out if you don't want to see Excel , which wouldn't make sense in this function
    xlApp.Range("A30").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    sleep(1)
    xlApp.Range("B28").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    sleep(1)
    xlApp.Range("C26").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    sleep(1)
    xlApp.Range("D24").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("A30").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("E22").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    sleep(1)
    xlApp.Range("B28").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = -4142
    xlApp.Range("G20").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("C26").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = -4142
    sleep(1)
    xlApp.Range("I19").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("D24").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = -4142
    sleep(1)
    xlApp.Range("K19").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("E22").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = -4142
    sleep(1)
    xlApp.Range("M21").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("G20").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("I19").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("K19").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("M21").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("L20:N22").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    sleep(.5)
    xlApp.Range("L20:N22").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    sleep(1)
    xlApp.Range("K19,O19,O23,K23").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("J18,P18,P24,J24,K21,M19,O21,M23").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("I17,Q17,Q25,I25,J21,M18,P21,M24").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("H16,R16,R26,H26,I21,M17,Q21,M25").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("G15,S15,S27,G27,H21,M16,R21,M26").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("F14,T14,T28,F28,G21,M15,S21,M27").Select()
    xlApp.Selection.Interior.ColorIndex = 3
    xlApp.Selection.Interior.Pattern = 1
    sleep(2)
    xlApp.Range("L20:N22").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("K19,O19,O23,K23").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("J18,P18,P24,J24,K21,M19,O21,M23").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("I17,Q17,Q25,I25,J21,M18,P21,M24").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("H16,R16,R26,H26,I21,M17,Q21,M25").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("G15,S15,S27,G27,H21,M16,R21,M26").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("F14,T14,T28,F28,G21,M15,S21,M27").Select()
    xlApp.Selection.Interior.ColorIndex = 6
    xlApp.Selection.Interior.Pattern = 1
    Sleep(1)
    xlApp.Range("L20:N22").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("K19,O19,O23,K23").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("J18,P18,P24,J24,K21,M19,O21,M23").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("I17,Q17,Q25,I25,J21,M18,P21,M24").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("H16,R16,R26,H26,I21,M17,Q21,M25").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("G15,S15,S27,G27,H21,M16,R21,M26").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    Sleep(.5)
    xlApp.Range("F14,T14,T28,F28,G21,M15,S21,M27").Select()
    xlApp.Selection.Interior.ColorIndex = -4142
    xlApp.Selection.Interior.Pattern = 1
    xlApp.Range("A1").Select()
    Sleep(1)
    xlapp.DisplayAlerts = .F.
    xlApp.ActiveWorkbook.SaveAs(excelname,-4143) '33 is a constant for Excel 4.0, dbf4 is 11
    xlApp.Workbooks.Close()
    xlapp.DisplayAlerts = .T.
    xlApp.Quit() 
    END FUNCTION

  2. #2
    Member
    Real Name
    Walter
    Join Date
    Jun 2003
    Location
    South Africa
    Posts
    456

    Default Re: Excel controlled by Alpha, another demo

    Hello Stan
    This code is pretty nice!

    Would you be able to help out to export records from a dbf table to a excel file?
    This is what I have so far:
    Setting the "headers" in the excel file is ok, but to add the records from the dfb. file am I not sure how to approach the issue
    I got this code from the version 9 ole automation demo, and am using version 9 currently.

    Thanks
    Regards
    Code:
    'Date Created: 08-Jul-2002 10:14:12 PM
    'Last Updated: 17-Jul-2008 10:34:45 AM
    'Created By  : John Magno - http://www.JohnMagno.com
    'Updated By  : Walter
    FUNCTION Excel_Insert_Cell_Data AS L (docName AS C )
    Dim xlApp as p
    dim myCell as c
    
    
    'If excen is already open, then get a pointer to it. Else, open it.
    xlApp = ole.GetObject("","Excel.Application")
    
    'If you wanted to open a new instance every time:
    'xlApp = ole.create("Excel.Application")   
    
    
    xlApp.Workbooks.Add() 'adds a workbook object
    xlApp.ActiveSheet.Name = "A5 Ole Automation"
    
    xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
    
    'Set Headers in Row 1 of Sheet
    '******************************************************************************
    'Cell A1
    myCell = "A1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Job Number" 'adds text to that cell
    
    'Cell B1
    myCell = "B1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Date" 'adds text to that cell
    
    'Cell C3
    myCell = "C1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Time" 'adds text to that cell
    
    'Cell D1
    myCell = "D1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Job Progress" 'adds text to that cell
    
    '******************************************************************************
    'Now I need to add the job numbers, dates, times and progress underneath
    'the "headings" from a dbf table.
    'In some cases it is 1 to 50 records...
    
    'I was thinking of using a while statement, but how do you 
    'reference the next "mycell" value
    
    'Record 1 from the dbf table would go to "b2" - "c2" - "d2" etc.
    'record 2 from the dbf tbl would then go to "b3" - "c3" etc
    
    'I am sure there is a better or faster way to do this..
    
    
    '****************************code from the version 9 ole demo app**************
    'myCell = "B1"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "Sales"
    'myCell = "A2"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "January"
    'myCell = "B2"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "5000"
    'myCell = "A3"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "February"
    'myCell = "B3"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "6000"
    'myCell = "A4"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "March"
    'myCell = "B4"
    'p.Range(myCell).Select()
    'xlApp.ActiveCell.FormulaR1C1 = "7000"
    '****************************code from the version 9 ole demo app**************
    
    xlApp.ActiveWorkbook.Saveas(docName)
    'xlApp.Quit()
    
    delete xlApp
    END FUNCTION

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Excel controlled by Alpha, another demo

    I handle the loop like this

    Code:
    i = 4 'variable to determine start row of data
    	dim global newgde as l
    	WHILE .not. tbl.fetch_eof()
    		'		'-----------------------
    		IF (.not. newgde)
    			icode = tbl.Code
    			idesc = tbl.Descriptio
    			iord = eval("tbl.b"+store)
    			iflg = tbl.Flg_il
    			ibcode = tbl.Bcode
    		ELSE
    			'USE WHEN FSC EFFECTIVE -----------------------
    			icode = tbl.Fscde
    			idesc = tbl.Fscdesc
    			iord = eval("tbl.b"+store)
    			iflg = tbl.Flg_il
    			ibcode = tbl.Fscbcode
    		END IF
    		'		'-----------------------
    		cr_line = alltrim(str(i)) 'cr_line is abbreviation for current line
    		myCell = "A" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = icode
    		myCell = "B" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = idesc
    		myCell = "C" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = iord
    		myCell = "D" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = iflg
    		myCell = "N" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = "'"+ibcode 'this concatenates a single apostrophe with a numerical value
                    'causes it to be treated as text and preserve any leading zeroes
    		ctn_ttl = ctn_ttl + iord
    		IF between(tbl.Ex_posit,1,5)
    			'stuff deleted here for brevity
    			myCell = "K"+cr_line
    			xlApp.Sheets(1).Range(myCell).Select()
    			xlApp.ActiveCell.FormulaR1C1 = ceiling(iord*.66)
    			myCell = "L"+cr_line
    			xlApp.Sheets(1).Range(myCell).Select()
    			xlApp.ActiveCell.FormulaR1C1 = ceiling(iord*.66)
    			myCell = "N" + cr_line
    			xlApp.Sheets(1).Range(myCell).Select()
    			xlApp.ActiveCell.FormulaR1C1 = ""
    		END IF
    		i = i + 1 'done with this iteration so we increment the current line variable
    		tbl.fetch_next()
    	END WHILE
    Can't say whether this is the best way.

  4. #4
    Member
    Real Name
    Walter
    Join Date
    Jun 2003
    Location
    South Africa
    Posts
    456

    Default Re: Excel controlled by Alpha, another demo

    Hello Stan,

    Thank you very much, it is working 100% for me.
    I appreciate your help.

    Have a nice day
    Walter

  5. #5
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Re: Excel controlled by Alpha, another demo

    Stan,
    Don't know if I should be posting here or the v9 desktop forum, but have a question on the following code from your post to Walter. Could you explain what this is and how it is used?

    Code:
    i = 4 'variable to determine start row of data
    dim global newgde as l
    IF (.not. newgde)
    	icode = tbl.Code
    	idesc = tbl.Descriptio
    	iord = eval("tbl.b"+store)
    	iflg = tbl.Flg_il
    	ibcode = tbl.Bcode
    ELSE
    	'USE WHEN FSC EFFECTIVE -----------------------
    	icode = tbl.Fscde
    	idesc = tbl.Fscdesc
    	iord = eval("tbl.b"+store)
    	iflg = tbl.Flg_il
    	ibcode = tbl.Fscbcode
    END IF
    I think the i=4 is because Walter has four columns. What I am needing to do is export from a table created by a copy operation from a set. Therefore, I will have up to 7 records from the child table that I want to list across, not down. Don't know if this can be done or not.
    Code:
    customer - basic order cnt. - child field 1 - child fld 2 - ....etc.
    Customer and basic order cnt. are from parent and therefore could have up to 7 records for a customer. Don't want to list same customer in 7 rows. Also, to make it even more complicated, I need the child records categorized and listed in their proper column. I think the attached zip will help explain it. Have the function done to input the column headings across the top. A simple form with a button does this. Now need to read thru the table and get the records in the proper column. If this is too convoluted to mess with, just let me know, and I'll understand.
    Thanks.
    Ernie

  6. #6
    Member
    Real Name
    Walter
    Join Date
    Jun 2003
    Location
    South Africa
    Posts
    456

    Default Re: Excel controlled by Alpha, another demo

    Hi,
    Code:
    i = 4 'variable to determine start row of data
    This piece determine which row in the excel file to start writing, this one would start in row 4. If you change this value to 2, then it would start in row 2

    Code:
    dim global newgde as l
    IF (.not. newgde)
    	icode = tbl.Code
    	idesc = tbl.Descriptio
    	iord = eval("tbl.b"+store)
    	iflg = tbl.Flg_il
    	ibcode = tbl.Bcode
    ELSE
    	'USE WHEN FSC EFFECTIVE -----------------------
    	icode = tbl.Fscde
    	idesc = tbl.Fscdesc
    	iord = eval("tbl.b"+store)
    	iflg = tbl.Flg_il
    	ibcode = tbl.Fscbcode
    END IF
    This piece of code is a table mapping. Stan most probably copied this from an existing piece of code.
    icode, idesc, iord, iflg etc are variables that are mapped to the table field names. You can identify the table fields as follows:
    tbl is a table pointer; You would create this pointer like this "Dim tbl as P"
    tbl.code - tbl = table pointer, code = table field name thus tbl.code

    Also in this example, 5 columns would be filled:
    Start row = 4
    A4 would be filled with icode which is mapped to tbl.code
    B4 would be filled with idesc which is mapped to tbl.Descriptio
    etc.

    You can remove the if ...else. ..end if.. statement if you want to.

    Here is the revised code I am currently using. (Thanks again to Stan for the code)
    Code:
    FUNCTION Excel_Insert_Cell_Data AS L (docName AS C )
    Dim xlApp as p
    dim myCell as c
    
    
    'If excen is already open, then get a pointer to it. Else, open it.
    xlApp = ole.GetObject("","Excel.Application")
    
    'If you wanted to open a new instance every time:
    'xlApp = ole.create("Excel.Application")   
    
    
    xlApp.Workbooks.Add() 'adds a workbook object
    xlApp.ActiveSheet.Name = "A5 Ole Automation"
    
    xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
    
    'Set Headers in Row 1 of Sheet
    '******************************************************************************
    'Cell A1
    myCell = "A1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "WO Number" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell B1
    myCell = "B1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Site Name" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell C3
    myCell = "C1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Product" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell D1
    myCell = "D1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Work Done" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell E1
    myCell = "E1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Parts Used" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell F1
    myCell = "F1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Arrive Date" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell G1
    myCell = "G1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Arrive Time" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell H1
    myCell = "H1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Depart Date" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell I1
    myCell = "I1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Depart Time" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell J1
    myCell = "J1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Acceptor" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    'Cell K1
    myCell = "K1"
    dim p as p 
    p = xlApp.Sheets(1)
    p.Range(myCell).Select() 'selects a cell
    xlApp.ActiveCell.FormulaR1C1 = "Status" 'adds text to that cell
    xlApp.ActiveCell.Font.Bold = .T.
    
    
    '******************************************************************************
    'Now I need to add the job numbers, dates, times and progress underneath
    'the "headings" from a dbf table.
    Dim tbl as P
    Dim wonumber as C
    Dim site as C
    Dim product as C
    Dim workdone as C
    Dim parts as C
    Dim arrdte as C
    Dim arrtm as C
    Dim depdte as D
    Dim deptm as C
    Dim acceptor as C
    Dim status as C
    
    
    
    tbl = table.open("t_jobfdbck")
    tbl.fetch_first()
    i = 3 'variable to determine start row of data
    	
    	WHILE .not. tbl.fetch_eof()
    		'		'-----------------------
    			wonumber = tbl.Fb_wono
    			site = tbl.Fb_site
    			product = tbl.Fb_prdct
    			arrdte = tbl.Fb_arrdte
    			arrtm = tbl.Fb_arrtm
    			depdte = tbl.Fb_depdte
    			deptm = tbl.Fb_deptm
    			workdone = tbl.Fb_prog
    			status = tbl.Fb_stat
    			acceptor = tbl.Fb_acceptr
    			parts = tbl.Fb_part
    
    
    		'		'-----------------------
    		cr_line = alltrim(str(i)) 'cr_line is abbreviation for current line
    		myCell = "A" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = wonumber
    
    		myCell = "B" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = site
    
    		myCell = "C" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = product
    
    		myCell = "D" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = workdone
    
    		myCell = "E" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = parts
    
    		myCell = "F" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = arrdte
    		
    		myCell = "G" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = arrtm
    		
    		myCell = "H" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = depdte
    		
    		myCell = "I" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = deptm
    		
    		myCell = "J" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = acceptor
    		
    		myCell = "K" + cr_line
    		xlApp.Sheets(1).Range(myCell).Select()
    		xlApp.ActiveCell.FormulaR1C1 = status
    		
    		i = i + 1 'done with this iteration so we increment the current line variable
    		tbl.fetch_next()
    	END WHILE
    
    
    xlApp.ActiveWorkbook.Saveas(docName)
    'xlApp.Quit()
    
    delete xlApp
    END FUNCTION
    Hope this helps
    Walter
    Last edited by Walter; 07-29-2008 at 03:35 PM.

  7. #7
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Re: Excel controlled by Alpha, another demo

    Walter,
    Thanks so much for getting back to me. I'll do some more studying of the code. I just wasn't sure what Stan was referring to in his code, but had assumed at one point it might be referencing table fields. I'm sure I'll have more questions on this.
    Thanks again.
    Ernie

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Excel controlled by Alpha, another demo

    Quote Originally Posted by enstorms View Post
    Walter,
    Thanks so much for getting back to me. I'll do some more studying of the code. I just wasn't sure what Stan was referring to in his code, but had assumed at one point it might be referencing table fields. I'm sure I'll have more questions on this.
    Thanks again.
    Ernie
    I started a thread in the v8 area for further discussion. Walter did a great job of explaining that, didn't he.

Similar Threads

  1. Demo: Exporting a Filtered Grid to Excel
    By Steve Workings in forum Application Server Version 8
    Replies: 0
    Last Post: 04-08-2007, 10:51 PM
  2. Can't append excel to alpha with NEW excel?
    By Robert Rough in forum Alpha Five Version 7
    Replies: 5
    Last Post: 02-22-2007, 01:54 PM
  3. How to Get tables for Alpha 5V7 Application Server Demo
    By oldtony in forum Web Application Server v7
    Replies: 2
    Last Post: 01-04-2007, 03:42 PM
  4. Alpha Sports Demo Web Site
    By jeff_schneidr in forum Web Application Server v7
    Replies: 18
    Last Post: 05-08-2006, 08:18 AM
  5. Print Preview - user controlled
    By Ron McCormack in forum Alpha Five Version 4
    Replies: 11
    Last Post: 07-15-2000, 07:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •