Outputs, Reports, KPI's stc. (1 Viewer)

Hello experts. Apologize in advance if this is the wrong place to ask. I am just looking for the advice on reporting protocol. Excel does a great job but normally I have to export, update, email. And A lot I do through code I just know there is so much more. I am able to do this via individual task. the reports I have produced have been well received and some other plants are looking for similar applications.
I read a lot about OLAP cubes, HTML etc. Not looking for specifics here although links are appreciated.
In a perfect word I would like to fire a command. Update tables, update excel reports. attach said excel to report via outlook with a brief summary in the email body.

Thanks

If you are using Access, you can do all or most of this from within Access. If users don't need to edit data, depending on type of output, you can make some nice looking reports in Access, save them as PDF and email. If they actually need excel, then you can output to excel and also use vba to format there too.

sxschech said:

If you are using Access, you can do all or most of this from within Access. If users don't need to edit data, depending on type of output, you can make some nice looking reports in Access, save them as PDF and email. If they actually need excel, then you can output to excel and also use vba to format there too.

Click to expand...

Yes the reports in access are fine. However for KPI's I need to have lots of pretty charts (snicker). Excel seems to be the only method for providing this format.

What type(s) of charts are you using? Access can do bar and line charts very easily. I haven't been able to do a pie chart (yet) as Access lacks the graphics I would need.

I do similar things: Our products are delivered with a Certificate of Analysis. I generate this one by opening an Excel template, pumping the required information from a query in Excel, saving it as pdf and attaching this pdf to a mail.
I think this will give you an idea about the possibilities with Access.

To generate and send CoA's took us before 2,5 days / week, a lot of manual handling and included several points where it could go wrong.
(and we know Murphy's law)
Now it is a press on a button + (if I programmed it correctly) free of errors

regards,

Ben

Example code

' Open Excel template to write
Set objExcelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then Set objExcelApp = CreateObject("Excel.Application")
Set objworkbook = objExcelApp.Workbooks.Open(“YourFileName”)

'Write Date information to the opened Excel template
Row = 5
Col = "J"
objworkbook.Worksheets(Sheetnme).Range(Col & Row) = Date ‘etc
' you add your own lines here

objExcelApp.Application.Visible = True ' First make the created Excel visible for the user
With objworkbook

' Save now the file as new PDF
objworkbook.Save '(“YourNewFileName”)

.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Filenme _
, Quality:=xlQualityMinimum, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End With


' Sending mail (We are using outlook 365, slow and it hangs from time to time but this is all I have)

Set oApp = CreateObject("outlook.application")
Set oMail = oApp.createitem(OLmailitem)

'28-aug
' Build the string for the body if the mail
Email_message = "Dear Customer, <br><br>Please find enclosed "
If Me.frm_NrOfCoA = 1 Then
Email_message = Email_message & "the CoA "
Else
Email_message = Email_message & Me.frm_NrOfCoA & " CoA's "
End If
Email_message = Email_message & "for our delivery " & Me.frm_Delivery & ". <br><br>With kind regards <br> <br>3B-Fibreglass Norway AS"

'SEND EMAIL (Creates mail, adds attachment and opens it such that the user can confirm and click on send”

Set oApp = CreateObject("outlook.application")
Set oMail = oApp.createitem(OLmailitem)

With oMail

.To = “Your SendTo” 'receiver
.CC = “Your SendCC” 'carbonCopy
.BCC = “Your SendBCC “
.Subject = “Your Subject” 'SubjectLine

.HTMLBody = “Your message”
' -------------------------------------
' Add the CoA(s) as attachment
' -------------------------------------
.Attachments.Add “Your FileName”
.Display ' show the mail to the user

' This part works only when OUTLOOK is opened

Do While .ReplyRecipients.Count > 0
.ReplyRecipients.Remove (1)
Loop
' .Recipients.Add (“Your reply_address”) 'reply_address
.ReplyRecipients.Add reply_address 'reply_address

I've done bar and pie charts within Access, was a bit tricky to set up, once it was then simple to maintain as data flowed through to the report and didn't have to worry about formatting after that.

sxschech,

I don't use the built in functions for bar charts or line charts. I write functions to update a form/report. Rather easy once you get the hang of it and much better results.

Mark_ said:

What type(s) of charts are you using? Access can do bar and line charts very easily. I haven't been able to do a pie chart (yet) as Access lacks the graphics I would need.

Click to expand...

Just wondering why you think pie charts are more difficult to do in Access?
See attached

@Ridders,

Because I don't like the built in charting? Never seems to let me get things looking the way I want it to look.

ridders said:

Just wondering why you think pie charts are more difficult to do in Access?
See attached

Click to expand...

That really pretty good ridders. I would like to learn how you did that.