Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Here you go:
msgbox(ActiveDocument.Evaluate("=Date(Today()-1,'DDMMYYYY')"))
Here you go:
msgbox(ActiveDocument.Evaluate("=Date(Today()-1,'DDMMYYYY')"))
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?
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.
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.
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
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
can you please share the entire code?
I am struggling hard with the same issue