Hi,

I am successfully creating Excel spreadsheets, populating them with data, formatting them etc etc all thanks to knowledge gained from "Simple Talk" !!!!!

A few questions however

When I save a file as below, and then open it with Excel I get a message saying the format does not match the file extension. I can still open the file and all my data is there. How can I stop this?

EXEC @ExecutionResult =sp_OAMethod @objWorkBook, 'SaveAs',null, 'C:\MyNewFolder\Stephen.xls'

I can set quite a lot of properties like font, grid lines and display zeros. I can't do anything that requires selection of a range, like freezing panes at a given location, or setting horizontal aligmnent of a range of cells

Can anyone suggest how to do this?

Thanks

Stephen

 

 

 

 

I can't see anything obviously wrong with this code.  What error do you get when you try to select a range?  It is a pretty important method, and it works in my code when I try it. Have you checked the obvious gotchas such as having a different version of Excel on the server and the machine you are loading the excel file from?

Hi Phil,

I can Set a range and give that range a name as below and that works fine.

@Command = 'Range("A1:Z1").Name'

Set @Command2 = 'Fred'

EXEC sp_OASetProperty @objExcel, @Command, @Command2

To do something like freezing the panes at cell A1 or setting the horizontal alignment for a range completely eludes me. Please can you give me an example of your code to do these two jobs?

 

Thanks Stephen

 

I know it's been a long time waiting for an answer (if one was never given), but I've successfully merged, aligned, changed font sizes, and weights using TSQL commands.

The main thing to remember is that FALSE = 0, TRUE = 1, and any xl**** command is typically a constant.

For Example, to set your range aligned "Center" you'd use:

@command = 'Range("A1:F1").HorizontalAlignment'
OR
@command = 'Range(A1, F1).Horizontal Alignment'


EXEC sp_OASetProperty @objExcel, @Command, 3

The 3 is the key. I've not had success when passing in 'xlCenter', but numerically it works. Setting borders works the same way.