topic monthly Report to excel-file via macro in QlikView

monthly Report to excel-file via macro

Fri, 17 Feb 2012 07:36:30 GMT

Hi everbody,

Is it possible to write monthly one table to one excel-file with one sheet for one month?
For example, the script runs at 01.01.2012 and creates an Excel-file where in the first sheet the table was exported.
On 01.02.2012 the script runs again and export the table in the Excel-File from january in the second sheet, and so on.
Is it possible?

Regards vicky

monthly Report to excel-file via macro

RSvebeck — Fri, 17 Feb 2012 09:02:30 GMT

Hi, yes it is possible.

This macro will open an already existing document, add a sheet, save a value to the sheet , save it and then quit excel.

The solution requires that you have Excel installed on the computer this runs on.

Regards

Robert

----------------------------

Sub SaveToExcel
Dim objExcel
Dim objWorkbook
Dim objWorksheet

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

Set objWorkbook = objExcel.Workbooks.Open ("C:\test.xlsx")

set objWorksheet = objExcel.Worksheets.Add()
objWorksheet.Activate

objWorksheet.Name = "January"

objExcel.Cells(1, 1).Value = "Test value 3"
objWorkbook.Save()
objWorkbook.close
objExcel.Quit 'this is important to avoid memory leaks

msgbox "Workbooks saved"

End Sub

----------------------------

monthly Report to excel-file via macro

Mon, 20 Feb 2012 09:41:13 GMT

Hi Robert,

Thanks for your answer.

I have some problems with your solution. I want to export a pivot-table, but with
objExcel.Cells(1,1).Value = "CH404"
the Excelsheet has only this string, not the table. Maybe the GetSheetObject(objectID) is missing in your example? I'm a real beginner in VB.

Then I want to save it automatical, but the sheet name is fixed. Can I write there something like
objWorksheet.Name = MonthName(today())?

Regards vicky

Re: monthly Report to excel-file via macro

Anonymous — Mon, 20 Feb 2012 10:42:38 GMT

Hope the attached file will be helpful for your requirement

Regards

Ashok

monthly Report to excel-file via macro

RSvebeck — Mon, 20 Feb 2012 14:09:41 GMT

Hi Vicky, have a look at Ashok's great example. It seems to do what you want. If not - post your QWV with your pivot here if you need a more specific/detailed solution. //Robert

Re: monthly Report to excel-file via macro

Wed, 27 Jun 2012 07:51:29 GMT

Hi,

I'm late, sorry.

I have now tried the sample out. It works

I have only one further question. There are lots of formatting stuff to do afterwards exporting the table to excel. The question is now: Can I only add the table for the actual month to the excel file?
This macro generates every time all month new - and whyever not in the correct order. Today the excel file beginns with sheet march till juni and then january and february. But that's not so important.

The file would be always at the same place.

Thanks for your help again.

Vicky

Re: monthly Report to excel-file via macro

RSvebeck — Wed, 27 Jun 2012 07:57:28 GMT

So if I understand you right, you want to update the same excel file once every month, lets say the first day every month, and add a sheet with the previous month data into that excel file?

Re: monthly Report to excel-file via macro

Wed, 27 Jun 2012 11:02:05 GMT

Hi Robert,

Yes, that would be great. Or isn't it possible?
The excel file is only for one year, so it's starts always with a zero sheet where to add the data for january.

Vicky

Re: monthly Report to excel-file via macro

RSvebeck — Wed, 27 Jun 2012 12:59:33 GMT

Hi. It is possible.

See my attached file. You need to set up some variables in the load script to get it running on your machine and if you need to test it today then change the variable vDayForExcelReport = 1 to todays day-numer and then reload again. It triggers the macro on reload, but you may also run it with the macro button.

Good luck.

//Robert

Re: monthly Report to excel-file via macro

Thu, 05 Jul 2012 11:54:31 GMT

Hi Robert,

That's great, thanks. After I noticed that the DayForExcelReport wasn't today in the test, how you still wrote, I was amazing.

Only one question, yet, for a better understanding.
In the first macro you wrote

ActiveDocument.Fields("Month").GetPossibleValues(totBranch)

Now the second you wrote
ActiveDocument.Fields("Month").GetPossibleValues(12)

In both cases there are only 12 month. When do I use totBranch and when the real number?

Thanks a lot!

Regards Vicky

Re: monthly Report to excel-file via macro

RSvebeck — Mon, 16 Jul 2012 08:29:22 GMT

Hi Vicky,

I have been on vacation, so thats why the late reply.

At first I did not understand, but now I see that you are comparing my macro with the macro by Ashok.

In Ashoks macro you are refering to the line:

Set strMonth = ActiveDocument.Fields("Month").GetPossibleValue(totBranch)

and you compare it to the line in my macro:

Set MonthField = ActiveDocument.Fields("Month").GetPossibleValues(12)

I don't know what totBranch comes from since it is not defined in his macro. The result is therefore comparable to just writing

Set strMonth = ActiveDocument.Fields("Month").GetPossibleValue()

The difference in writing

1) GetPossibleValue()
2) GetPossibleValue(12)
3) GetPossibleValue(1923)

is that Qkliview will fetch
1) All possible values
2) Max 2 possible values
3) Max 1923 possible values.

And you can also write it like this:

whatever=10

GetPossibleValue(whatever)

Which will get max 10 possible values, since the variable whatever is equal to 10.

if you write like this:

GetPossibleValue(anything)

and never define "anything" it will be like writing

GetPossibleValue()

Which is the same as getting all possible values.

Hop this helps.

Best Regards
Robert

Re: monthly Report to excel-file via macro

Tue, 17 Jul 2012 06:55:42 GMT

Hi Robert,

Hope you enjoined your vacations

Thanks for your explanation. Now I'm understanding the macro better. Hope I will keep it in mind for the next time

Have a nice day

Best Regards

Vicky