Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Excel/Word Export

Member
  
Join Date: Mar 2007
Posts: 60
#1: May 24 '07
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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#2: May 25 '07

re: Excel/Word Export

Quote:

Originally Posted by 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?

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.
Member
  
Join Date: Mar 2007
Posts: 60
#3: May 29 '07

re: Excel/Word Export

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's Avatar
Member
  
Join Date: Oct 2006
Location: Brampton, ON, Canada
Posts: 116
#4: May 29 '07

re: Excel/Word Export

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.

Quote:

Originally Posted by 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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#5: May 29 '07

re: Excel/Word Export

Quote:

Originally Posted by fauxanadu

(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.
Member
  
Join Date: Mar 2007
Posts: 60
#6: May 30 '07

re: Excel/Word Export

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
  3. ' In VBE, goto Tools->References... and select it from the list
  4.  
  5. Private Sub ExportToExcel()
  6.  
  7.     ' Variable Declarations
  8.     Dim objExcel As Excel.Application
  9.  
  10.     ' If Excel is open, use GetObject, otherwise create a new Excel object
  11.     ' Error 429 is Application Not Running
  12.     On Error Resume Next
  13.     Set objExcel = GetObject(, "Excel.Application")
  14.     If Err = 429 Then Set objExcel = New Excel.Application
  15.  
  16.     With objExcel
  17.         ' Adds a new workbook to the Excel environment
  18.         .Workbooks.Add
  19.  
  20.     ' Excel VBA Code goes here
  21.  
  22.     ' Causes the Excel window to become visible
  23.         .Visible = True
  24.     End With
  25. End Sub
  26.  
  27.  
And just for kicks, here is the code to send a new E-mail via Outlook:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub ExportToOutlook()
  3.  
  4. ' Requires A References to the Microsoft Outlook Object Library
  5. ' in VBE, select Tools->References, and ensure it is checked
  6.  
  7. Private Sub ExportToOutlook()
  8.  
  9.     ' Variable Declaration
  10.     Dim objOutlook as Object
  11.     Dim objEMail as Object
  12.  
  13.     ' If Outlook is open, use GetObject, otherwise open Outlook
  14.     Set objOutlook = GetObject(, "Outlook.Application")
  15.     If objOutlook Is Nothing Then Set objOutlook = CreateObject("Outlook.Application")
  16.  
  17.     ' Creates a new e-mail
  18.     Set objEMail = objOutlook.CreateItem(0)
  19.     With objEMail
  20.  
  21.         ' Adds To Recipient
  22.         Set ToContact = .Recipients.Add("Me@Gmail.Com")
  23.  
  24.         ' Adds CC recipient
  25.         ToContact.Type = olCC
  26.         Set ToContact = .Recipients.Add("You@Gmail.com")
  27.  
  28.         ' Sets the Subject
  29.         .Subject = "Service Report 1234"
  30.  
  31.         ' Sets the Body
  32.         .Body = "Attached herein are the Reports"
  33.  
  34.         ' Adds attachment
  35.         .Attachments.Add "c:\Service Report 1234", olByValue, , "Service Report"
  36.  
  37.         ' Embeds attachment
  38.         .Attachments.Add "c:\JoeBob.gif", olEmbeddedItem, , "Joe Bob's Picture"
  39.  
  40.         ' Receipt upon delivery
  41.         .OriginatorDeliveryReportRequested = True
  42.  
  43.         ' Recipt upon read
  44.         .ReadReceiptRequested = True
  45.  
  46.         ' Displays the E-Mail
  47.         .Display
  48.  
  49.         ' Sends the E-Mail
  50.         .Send
  51.  
  52.         ' Saves a Draft of the E-Mail
  53.         .Save                                        
  54.     End With
  55.  
  56. End Sub
  57.  
  58.  
  59.  
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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#7: May 30 '07

re: Excel/Word Export

Quote:

Originally Posted by fauxanadu

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#8: May 31 '07

re: Excel/Word Export

Check the Application Automation link now to see your contributions as part of a bona fide article.
Member
  
Join Date: Mar 2007
Posts: 60
#9: May 31 '07

re: Excel/Word Export

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#10: Jun 1 '07

re: Excel/Word Export

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's Avatar
Member
  
Join Date: Oct 2006
Location: Brampton, ON, Canada
Posts: 116
#11: Jun 1 '07

re: Excel/Word Export

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
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,419 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved