topic Re: variable date in macro in QlikView

variable date in macro

gerhardl — Thu, 12 Jul 2012 08:17:29 GMT

Hi there,

I am busy with a macro which exports various tables to a report in Excel.

Currently I rename the excel sheet like this:

XLSheet1.Name = "INSERT DATE HERE"

How can I change the name to yesterday's date, in DDMMYYYY format?

Very new to VBA, so please spell it out for me...

Thanks,

Gerhard

Re: variable date in macro

Anonymous — Thu, 12 Jul 2012 08:25:25 GMT

Here you go:

msgbox(ActiveDocument.Evaluate("=Date(Today()-1,'DDMMYYYY')"))

Re: variable date in macro

gerhardl — Thu, 12 Jul 2012 08:30:18 GMT

Beautiful, I did this:

XLSheet1.Name = ActiveDocument.Evaluate("=Date(Today()-1,'DDMMYYYY')")

No need for the message box - bu thanks.

Before I mark this as answered - I am busy doing a daily report, where I do a whole bunch of things in QlikView, copy to excel, and then finalise the report there and send out.

I am trying to get the whole thing done with a macro, triggered by a button in QV. I am making great progress - but this is my first EVER macrom, so I have a few questions...

Should I do separate posts for each of my questions?

Re: variable date in macro

Anonymous — Thu, 12 Jul 2012 08:38:17 GMT

No worries Gerhard.

You might want to do a quick search of the forums first though. I remember seeing several examples of exporting stuff to customized Excel reports over the years. Probably a lot of copy/paste stuff in those examples for you to re-use.

Re: variable date in macro

gerhardl — Thu, 12 Jul 2012 08:46:57 GMT

Yes - that has helped me a LOT. Every line in my macro is stolen from someone else.... and I'm loving it.

I will then post new questions when I have them.

A quick one though...

I am trying to paste a table in excel, but I need it Transposed.

I use the following:

XLSheet1.Paste.Special.Transpose XLSheet1.Range("R8")

When I run it I get an Excel pop up saying "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste it anyway?"

Firstly, I cannot have this message popping up, as it will interrupt the flow - but anyway, when I press OK QlikView says at the top of the Edit Module Screen:

"Cannot change part of a merged cell"

Cell R8 is not yet used in any other part of my script.

Re: variable date in macro

Anonymous — Thu, 12 Jul 2012 08:50:13 GMT

Hm, that sounds tricky. I'm sure it can be coded around somehow but me being a lazy bugger sometimes would probably just create the transposed table in a hidden sheet somewhere and export it straight if that works

Re: variable date in macro

gerhardl — Thu, 12 Jul 2012 08:59:44 GMT

Yes, I found a work-around by pasting the table as straight, then copying the values to the correct cells and then deleting the original straight table.

Thanks Johannes

Re: variable date in macro

Tue, 03 Sep 2013 07:22:30 GMT

can you please share the entire code?

I am struggling hard with the same issue