monthly Report to excel-file via macro

[#1]

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

[RSvebeck]

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

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

[#3]

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

[Anonymous]

Hi

     Hope the attached file will be helpful for your requirement

Regards

Ashok

[RSvebeck]

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

[#6]

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

[RSvebeck]

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?

[#8]

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

[RSvebeck · Accepted solution]

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

[#10]

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