Ok, below is some code I stripped from a working program...hope I didn't miss copying anything that causes it to not make sense. I use it to format an XLS file that is created with the WinDev TableToExcel() function.
Now, this is all code to manipulate an existing XLS file. I also have some where I scan down through these same XLS files, read data from specific cells on each row and then use that information to update an external database via OLE/DB. My client is using the XLS files as a way for their external contractors to report back daily sales information.
I believe that it was from this forum that I got the idea to record a macro in Excel and then use the generated code as a starting point to convert to the WinDev >> format.
For example, this Excel macro:
<pre>
Columns("H:H").Select
Selection.Font.Bold = True
</pre>
Would become this WinDev code:
<pre>
oExcel>>Columns("H")>>Select
oExcel>>Selection>>Font>>Bold = OLETrue
</pre>
------------------------------------------------------------------------------
With Excel you use constants quite a bit. I just Googled and found whatever I needed.
These would go in the Initialization portion of your Project.
<pre>
CONSTANT
con_xlLastCell = 11
con_xlMaximized = -4137
con_xlRangeAutoformatClassic2 = 2
con_xlPortrait = 1
con_xlSolid = 1
con_xlEdgeTop = 8
con_xlContinuous = 1
con_xlThick = 4
END
//=========================================================================================
// Use OLE to manipulate the created XLS file
//=========================================================================================
// Open Excel via OLE
oExcel is object OLE dynamic
oExcel = new object OLE "Excel.Application"
oExcel>>Visible = OLEFalse
// open the workbook ( a workbook is a file in XLS talk )
oExcel>>Workbooks>>Open(CompleteDir(fCurrentDir()) + l_sXLSFile)
// rename the Sheet to same as the file name
oExcel>>ActiveSheet>>Name = l_sXLSFile
// Make it so it is maximized when opened
oExcel>>Application>>WindowState = con_xlMaximized
// Make all used columns autofit
oExcel>>Columns("A:M")>>EntireColumn>>AutoFit
// Setup the Lock status for the cells
oExcel>>Columns("A:K")>>Select
oExcel>>Selection>>Locked = OLETrue
oExcel>>Columns("L:M")>>Select
oExcel>>Selection>>Locked = OLEFalse
oExcel>>Rows("1:1")>>Select
oExcel>>Selection>>Locked = OLETrue
// This is how you would change the background color
//oExcel>>Range("A2:J40")>>Select
//oExcel>>Selection>>Interior>>ColorIndex = 34
//oExcel>>Selection>>Interior>>Pattern = con_xlSolid
// Sample of working with borders ( This places a large border on the top of the cells )
l_nTempInt = 4 // This is usually a variable in a loop
// oExcel>>Range("A"+NumToString(l_nTempInt)+":M"+NumToString(l_nTempInt))>>Select
// oExcel>>Selection>>Borders(con_xlEdgeTop)>>LineStyle = con_xlContinuous
// oExcel>>Selection>>Borders(con_xlEdgeTop)>>Weight = con_xlThick
// Make the customer number column bold
oExcel>>Columns("J")>>Select
oExcel>>Selection>>Font>>Bold = OLETrue
// Draws
oExcel>>Columns("K")>>Select
oExcel>>Selection>>Interior>>ColorIndex = 35
oExcel>>Selection>>Interior>>Pattern = con_xlSolid
// Adjustments
oExcel>>Columns("L")>>Select
oExcel>>Selection>>Interior>>ColorIndex = 36
oExcel>>Selection>>Interior>>Pattern = con_xlSolid
// Returns
oExcel>>Columns("M")>>Select
oExcel>>Selection>>Interior>>ColorIndex = 38
oExcel>>Selection>>Interior>>Pattern = con_xlSolid
oExcel>>Range("M2:M" + NumToString(l_nXLSRows))>>Select
oExcel>>Selection>>ClearContents
// Column titles
oExcel>>Range("A1:M1")>>Select
oExcel>>Selection>>Interior>>ColorIndex = 10
oExcel>>Selection>>Interior>>Pattern = con_xlSolid
oExcel>>Selection>>Font>>ColorIndex = 2
oExcel>>Selection>>Font>>Bold = OLETrue
// Start them off selected in the first cell for returns
oExcel>>Range("M2")>>Select
// Make it so they can only change columns that are unlocked
oExcel>>ActiveSheet>>EnableSelection = 1 // xlUnlockedCells
oExcel>>ActiveSheet>>Protect("password")
oExcel>>ActiveWorkbook>>Save
oExcel>>ActiveWorkbook>>Close
oExcel>>Application>>Quit
delete oExcel
</pre>
Edited 1 time(s). Last edit at 07/30/2008 02:44PM by David Martin.