topic Macro commands to control excel in QlikView

Macro commands to control excel

eliran — Tue, 22 Feb 2011 09:47:20 GMT

Hi,

The main reason for this Question is to find out how can I align cells in excel using a macro.

Of course I'm after creating the excel and copying the data to it, I merged couple of cells and want them to be aligned to the center.

I can't seem to find what is the command to do so.

And in a general note, Is there a way to learn / find the commands and properties of the excel?

For example, in Excel, the easiest way to do such things is to record a macro and then view the code generated.

Regards,

Eliran.

Macro commands to control excel

amars — Tue, 22 Feb 2011 10:04:01 GMT

Hi Eliran,

Const xlCenter = -4108

Const xlNone = -4142

Const xlDown = -4121

Const xlRight = -4152

Const xTop = -4160

XLSheet.Range("B1:B2").HorizontalAlignment = xlCenter

XLSheet.Range("B1:B2").VerticalAlignment = xlCenter

Thanks

Macro commands to control excel

eliran — Tue, 22 Feb 2011 11:30:22 GMT

Thanks amars,

Your answer had the missing piece I needed.

i have used:

ExcelDoc.ActiveSheet.Range("B1:D1").HorizontalAlignment = xlCenter

like I'm using in the excel, but it didn't work, I had to declare:

Const xlCenter = -4108

Btw I didn't understand why the extra code you wrote is needed.

Thanks.

Macro commands to control excel

amars — Tue, 22 Feb 2011 11:41:29 GMT

Hi Eliran,

If we don't declare the constant, we never know what the -4108 means thereafter , also the meaning of -4108 . So it is always good to declare the constants & then use it.

Thanks

Macro commands to control excel

eliran — Tue, 22 Feb 2011 12:02:08 GMT

Thanks for replying,

But by extra code I meant, the XlRight/Down/None

I didn't need to use it to get the allignment.

And a follow up question if you may, is there an easy way to get the all the properties that I can use with the Range? for example I also want to play with the boarder colour, In vb it's:

Borders(xlEdgeLeft).TintShade= 0.32131 (some colour I picked up)

How do I know the code that can be used in the qv macro?

Thanks a lot!

Eliran.

Re: Macro commands to control excel

Anonymous — Sun, 22 Jul 2018 16:06:37 GMT

I also have a similar problem.

Is there a possibility to open an excel and find any data or record.

I can use loop but i am looking for a simple find syntax.