Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    Hi again.

    I've sussed how to create the Excel WorkSheet and Automaticly attach it to an email.

    How can I Reformat the Excel Structure before sending. Calling it say from an Access 2007 Module?

    For example there are three Columns: Text1 (in A), Number1 (in [img]/forums/images/smilies/cool.gif[/img] and TrueFalse (In C) (all of Variable number of Rows) . If a want the Whole spreadsheet to be in Font: 10pt Verdana and the Column Titles in (A1:B1:C1) to be in Bold and Number1 (In [img]/forums/images/smilies/cool.gif[/img] to be Justify: Centre.

    How can I code this in so it will happen everytime we Output and Send a File, so theres no longer a need to do it by hand???

    Any help Much Appreciated. While I dabble in Access VBA, Excel VBA is very much a Mystery to me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    Let's say that you have a worksheet object wsh

    Dim wsh As Excel.Worksheet
    Set wsh = ...
    wsh.Cells.Font.Name = "Verdana"
    wsh.Cells.Font.Size = 10
    wsh.Range("A1:C1").Font.Bold = True
    wsh.Range("B:B").HorizontalAlignment = xlHAlignCenter

    (You can find a lot of what's needed by recording a macro in Excel; you'll have to adjust it for use in your code from Access, but the recorded code will tell you which properties you need etc.)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    Thanks Hans.

    Seem to be Having a problem recognising the Excel WorkBook/Sheet, if I pump in the Path of the WorkBook I get: "Type MisMatch" from the Compiler??

    Set wsh = "Cocuments and SettingsGraemeDesktoptest.xls"

    The Sheet I need to Reference is named: "Matched"

    Do I need to do Anything else about Defining the WorkBook as an Object etc to reference it properly. its Defined by XP in the File Description as "Microsoft Office Excel 97-2003 Worksheet"

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    A worksheet object is not a string, and moreover the string you provide refers to a workbook, not to a worksheet.
    Can you provide some info about the code you're already using?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    Hans Hi,

    I'm Simply Outputting a Table from an Access VBA Module Via docmd to Excel 97-2003 and then need to adjust the Format of the Cells as Stated, so the code I'm using is as per your prior post.

    I need to be able to reference the Outputted Object at path:"Cocuments and SettingsGraemeDesktopTest.xls" and access the Cells in worksheet: "Matched" to change the Font to 10pt verdana and Place the Column Headings in Bold and Center Specified Columns. And then Apply it to an Email Automaticly for Forwarding to a Recipient.

    I don't think theres much more I can give you.

    Any Help Appreciated, Muchly!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    I'm confused now - your subject says "Auto-Format Excel Cells from Outlook" and now it turns out you're using Access?

    You'll need to do something like this in Access after creating the workbook:

    Sub EditWorkbook()
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim blnStart As Boolean

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot start Excel", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If

    On Error GoTo ErrHandler

    Set xlWbk =xlApp.Workbooks.Open("Cocuments and SettingsGraemeDesktopTest.xls")
    Set xlWsh = xlWbk.Worksheets("Matched")
    xlWsh.Cells.Font.Name = "Verdana"
    xlWsh.Cells.Font.Size = 10
    xlWsh.Range("A1:C1").Font.Bold = True
    xlWsh.Range("B:B").HorizontalAlignment = -4108 ' xlHAlignCenter
    xlWbk.Close SaveChanges:=True

    ExitHandler:
    On Error Resume Next
    Set xlWbk = Nothing
    If blnStart Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You can call this from your own code:

    Call EditWorkbook

  7. #7
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    Yep, Just the Ticket. I've had to throw Out your error trapping, but as I'll know the File Definitly Exists and Definitly is in the Correct Format, there shouldn't be any more Problems.

    Ahh, the worlds of Possibilities that open.

    Hope theres a chance I can do you a Favour One day Hans..

    PS. Changed My mind about where I was going to Call the Routine IE Access Rather than Outlook.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Format Excel Cells from Outlook (Office 2007 Pro/Outlook)

    The code controls Excel from Access in VBA, this is called Automation. See WendellB's tutorial Automation 101 for background info.

    I used late binding, i.e. the Excel items are declared as Object, and you don't need a reference to Excel in your code.
    If you set a reference to the Microsoft Excel 12.0 Object Library in Tools | References (in the Visual Basic Editor), you can declare items as Excel.Application, Excel.Workbook etc., and use IntelliSense while writing the Excel code. This is called early binding.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •