OLE XL manipulation, how do you select+autofit

[Chris.Tanti]

As part of a previous post I reported the inconsistency in saving reports in Excel format.

I am trying to create a fix using OLE, the intention is that once A5 has created the XL report, I would load the file, select all cells (Ctrl-A), AutoFit the columns and Autofit the rows.

Unfortunately I can't seem to get the syntax correct, I have created a macro in Excel to get the basic properties, but I am buggered if I can get it to parse through Alpha5. Below is the closest I have come, basically I get

a missing or invalid keyword

as it is, and if I try and place a pointer in front of it

x=oWorksheet1.cells.Select

then the element is never found. The other problem I have is save the document and bypassing the overwright warning, it requires user interaction at the moment.

I know not many people use OLE, there are a few examples in the help section, but a dedicated sample list would be helpful for Excel and Word specifically.

dim oExcel as P
dim oWorkbook as P
dim oWorksheet1 as P

dim fn as C
fn = "c:\a.xls"
oWorkbook = ole.GetObject(fn)
oWorkbook.Application.visible = .T.
oWorkbook.windows(1).activate()
oWorksheet1 = oWorkbook.Worksheets(1)

[COLOR="Red"]oWorksheet1.cells.Select
oWorksheet1.Cells.EntireColumn.AutoFit
oWorksheet1.Cells.EntireRow.AutoFit[/COLOR]

[COLOR="red"]oWorkbook.SaveAs(fn)[/COLOR]
oWorkbook.Application.visible = .F.
oWorkbook.close()
delete oWorkbook
delete oExcel

[Stan Mathews]

Re: OLE XL manipulation, how do you select+autofit

oWorksheet1.cells.Select()

For autofit, something on the lines of

oWorksheet1.Columns("A:I").Autofit()

The other problem I have is save the document and bypassing the overwright warning

Wrap your saveas line with

oWorksheet1.DisplayAlerts = .F.
'your line here
oWorksheet1.DisplayAlerts = .T.

[Chris.Tanti]

Re: OLE Excel manipulation, how do you select+autofit

Stan, that worked beutifully, thanks!
I had to amend the code very slightly, but basically its functioning as I had hoped. One more question, how would I justify the entire selected cells to the left?

This is the functioning code as it stands

dim oExcel as P
dim oWorkbook as P
dim oWorksheet1 as P
dim oRange as P
dim fn as C
fn = "c:\a.xls"
oWorkbook = ole.GetObject(fn)
oWorkbook.Application.visible = .T.
oWorkbook.windows(1).activate()
oWorksheet1 = oWorkbook.Worksheets(1)

oWorksheet1.cells.Select()
oWorksheet1.Columns("A:Z").Autofit()
oWorksheet1.rows("1:2000").AutoFit()


oWorkbook.Application.DisplayAlerts = .F.
oWorkbook.SaveAs(fn)
oWorkbook.Application.DisplayAlerts = .T.

oWorkbook.Application.visible = .F.
oWorkbook.close()
delete oWorkbook
delete oExcel

[Stan Mathews]

Re: OLE XL manipulation, how do you select+autofit

If I understand correctly you want

oWorksheet1.cells.Select()
oWorksheet1.Selection.HorizontalAlignment = -4131
oWorksheet1.Columns("A:Z").Autofit()
oWorksheet1.rows("1:2000").AutoFit()

Capitalization is for clarity, not necessity.

[Chris.Tanti]

Re: OLE XL manipulation, how do you select+autofit

Stan,

Not sure why, but its giving an error

OWorksheer1.Selection.HorizontalAlignment=-4131
Property not found
oWorksheet1.Selection subelement not found

Have placed it as you sugested

oWorksheet1.cells.Select()
[COLOR="Red"]oWorksheet1.Selection.HorizontalAlignment = -4131[/COLOR]
oWorksheet1.Columns("A:Z").Autofit()
oWorksheet1.rows("1:2000").AutoFit()

[Stan Mathews]

Re: OLE XL manipulation, how do you select+autofit

Your example uses a different approach than I am accustomed to using.

Try it this way.

dim oExcel as P
dim oWorkbook as P
dim oWorksheet1 as P
dim oRange as P
dim fn as C
fn = "c:\a.xls"
oExcel = ole.Create("Excel.Application")
oExcel.workbooks.open(fn)
oExcel.visible = .T.

oExcel.Columns("A:Z").Autofit()
oExcel.rows("1:2000").AutoFit()
oExcel.Sheets(1).Range("A1:Z2000").Select()
oExcel.Selection.HorizontalAlignment = -4131
oExcel.DisplayAlerts = .F.
oExcel.ActiveWorkbook.SaveAs(fn)
oExcel.Workbooks.Close()
oExcel.DisplayAlerts = .T.

oExcel.visible = .F.
oExcel.quit()
delete oExcel

[Chris.Tanti]

Re: OLE XL manipulation, how do you select+autofit

Many thanks Stan,

Works perfectly now.

The method I used to open the XLS was directly from the help file, so not sure why it misbehaved.

Can I ask 1 more question please!?

If I wanted to make sure the file saved in the latest version of XL, what method should I use. I am thinking specifically of when I do a quick and dirty export from Alpha5 in Excelv4, would be good if I could open and re save in v8 (or whatever the latest version is)

[Stan Mathews]

Re: OLE XL manipulation, how do you select+autofit

oExcel.ActiveWorkbook.SaveAs(fn,-4143)

[Chris.Tanti]

Re: OLE XL manipulation, how do you select+autofit

Once again, very much appreciated.

I will re post this working code onto the v9 Excel Report thread, it may help.

Thanks Stan.