Excel/Word Export

[fauxanadu]

Is it possible to export data to Word and Excel documents and, once it is exported to the required document type, format it from within Excel?

[NeoPa]

Is it possible to export data to Word and Excel documents and, once it is exported to the required document type, format it from within Excel?

When you say "format it from within Excel?", do you mean "format it from within the related application?"?
If so, then yes. BUT, it is quite advanced and requires Application Automation.
I know very little about Word coding, but I imagine that whichever you're using, you could use the clipboard in your code to transfer the data across.

[fauxanadu]

Basically I have a bunch of forms that will be stored in a database by project. I need to be able to export these forms to word or excel in order to send them to other people via E-Mail. I need to export them, maintaining their formatting, though, so that the people on the other end can simply print them out.

More specifically, here is what I would like to do, step by step:

1) User fills out form, clicks print, chooses E-Mail as Excel Document
2) Create an instance of an Excel object.
3) Format the form (such as setting the background colour of cells, changing the width of columns and height of rows, adding borders to cells, adding the base text, adding tables, formatting those tables, setting alignment, et cetera).
4) Export the information from the database table to the appropriate spaces on the newly formatted form.
5) Save the form to a temporary location
6 Create a new Outlook E-Mail instance
7) Attach the temporary file to the E-Mail
8) Attach a Subject line to the E-Mail
9) Add a list of standard recipients to the new E-Mail

Steps 2-4 are the ones that I have no earthly idea how to do. If all of this is possible, I just need the basic idea behind it and I can figure out how to make it all work together. (More specifically, what classes/sub-routines and what functions are used in this process via VBA.) Unfortunately the entire thing absolutely MUST be done in Access. If this is not possible, I'll hack something together to make it happen, but I'd prefer not to have to do that.

Thanks

(BTW, that article is a good start to what I need, but I need something perhaps a bit more in depth)

[LacrosseB0ss]

I had a prof tell me once "If you can think about it, you can program it". The trick is it depends how complex you want to get. The export parts aren't really that difficult with the right references added and such (off hand I forget what they are) but the Excel formatting could hurt your brain.

Is it possible to export data to Word and Excel documents and, once it is exported to the required document type, format it from within Excel?

[NeoPa]

(BTW, that article is a good start to what I need, but I need something perhaps a bit more in depth)

The article simply introduces you to the concept of Application Automation (with some how-to's thrown in). It doesn't deal with Excel & Word & Outlook & ... etc. This is, after all, an Access forum. I can help with specific questions in Excel (I'm certainly not going to code your project for you), but the Outlook & Word areas are foreign to me. That stuff you'll have to research.
The concept of Application Automation does not include expertise in all the various applications that are able to be programmed in this way.

[fauxanadu]

I found the information I was looking for. This is what I came up with, for anyone who would like to use this feature in their programs:

 
' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
' In VBE, goto Tools->References... and select it from the list
 
Private Sub ExportToExcel()
 
    ' Variable Declarations
    Dim objExcel As Excel.Application
 
    ' If Excel is open, use GetObject, otherwise create a new Excel object
    ' Error 429 is Application Not Running
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If Err = 429 Then Set objExcel = New Excel.Application
 
    With objExcel
        ' Adds a new workbook to the Excel environment
        .Workbooks.Add
 
    ' Excel VBA Code goes here
 
    ' Causes the Excel window to become visible
        .Visible = True
    End With
End Sub
 
 

And just for kicks, here is the code to send a new E-mail via Outlook:

 
Private Sub ExportToOutlook()
 
' Requires A References to the Microsoft Outlook Object Library
' in VBE, select Tools->References, and ensure it is checked
 
Private Sub ExportToOutlook()
 
    ' Variable Declaration
    Dim objOutlook as Object
    Dim objEMail as Object
 
    ' If Outlook is open, use GetObject, otherwise open Outlook
    Set objOutlook = GetObject(, "Outlook.Application")
    If objOutlook Is Nothing Then Set objOutlook = CreateObject("Outlook.Application")
 
    ' Creates a new e-mail
    Set objEMail = objOutlook.CreateItem(0)
    With objEMail
 
        ' Adds To Recipient
        Set ToContact = .Recipients.Add("[e-mail]")
 
        ' Adds CC recipient
        ToContact.Type = olCC
        Set ToContact = .Recipients.Add("[e-mail]")
 
        ' Sets the Subject
        .Subject = "Service Report 1234"
 
        ' Sets the Body
        .Body = "Attached herein are the Reports"
 
        ' Adds attachment
        .Attachments.Add "c:\Service Report 1234", olByValue, , "Service Report"
 
        ' Embeds attachment
        .Attachments.Add "c:\JoeBob.gif", olEmbeddedItem, , "Joe Bob's Picture"
 
        ' Receipt upon delivery
        .OriginatorDeliveryReportRequested = True
 
        ' Recipt upon read
        .ReadReceiptRequested = True
 
        ' Displays the E-Mail
        .Display
 
        ' Sends the E-Mail
        .Send
 
        ' Saves a Draft of the E-Mail
        .Save                                        
    End With
 
End Sub
 
 
 

Lastly, I wouldn't expect you to code my entire project. I have close to 100 different forms that need to be linked together via a database and exported to excel/word then e-mailed. This is quite a large undertaking. At the moment the forms and database are all working fine except for export to excel and e-mail functions of each one that requires them. Now that I have this I can do almost everything I need to do.

[NeoPa]

Lastly, I wouldn't expect you to code my entire project. I have close to 100 different forms that need to be linked together via a database and exported to excel/word then e-mailed. This is quite a large undertaking. At the moment the forms and database are all working fine except for export to excel and e-mail functions of each one that requires them. Now that I have this I can do almost everything I need to do.

First of all, let me congratulate you for getting past your earlier problems.
The 'Entire Project' bit was about how you asked the question. Scope is very important here, and you can get a lot more help if you break it down into more bite-sized chunks. Larger scoped questions can be seen as expecting some of the leg-work be done for you (so many OPs do I'm afraid).
Be that as it may, your code may prove vey helpful to others (as it hapens) and I'm going to try to add your two example pieces of code into the article I linked earlier. I fully expect to use the Outlook part myself too, so my gratitude is due for that also. All in all, a lot of benefit from your last post, so thanks for that.
As a sort of 'quid pro quo', if you need help formatting (or doing other clever things in Excel), I may well be able to help. Post any (bite-sized) questions in this thread so that I see the updates (I've registered in this thread so will get any updates). I don't get to see all the new threads anymore, as there are simply too many for me to cope with usually :(

[NeoPa]

Check the Application Automation link now to see your contributions as part of a bona fide article.

[fauxanadu]

I'm glad I could contribute to the article^.^

And I understand where you are coming from. I just wanted to clarify that I would never expect such from someone. Thank you for the offer to help in Excel. I might take you up on that offer^.^ I'm currently having to start from scratch on the database part though, so it might be a while.

[NeoPa]

Remember if you have Excel problems to post the question in here (or PM me with a link to any new question - but remind me I asked you to, otherwise I may respond with my standard response which is not toooo friendly ;) I get so many PM requests you understand).
Good luck with your project anyway.

[LacrosseB0ss]

faux, I also thank you for your code. Well done! Sadly a lot of posts on here are "Do my homework assignment" but yours was a serious coder looking for something they're stuck on.

Thanks again for the tips.
- LB