Re: Problem when importing data from a xls file

Instead of using the built in xls functions is there any other way to access an external xls file? Can you please explain me how this is done?


Regards

Message forwarded from pcsoft.us.windev

You can use OLE.
In WD XI you have one example that can help you.
C:\WinDev 11\Examples\Training\WD Excel Control

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.

Thank you guys for your help! The point is that I have an excel file and need to copy 2 columns from the second sheet of this file and paste them into a brand new xls file created during the execution of the program and then use this just created file. I have never used an ole control. Can you help me out?


Regards