variable date in macro

[gerhardl]

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

[Anonymous · Accepted solution]

Here you go:

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

[gerhardl]

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?

[Anonymous]

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.

[gerhardl]

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.

[Anonymous]

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

[gerhardl]

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

[#8]

can you please share the entire code?

I am struggling hard with the same issue