Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Location
    Wrexham
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Word and Excel macro compatablity

    I've recently written a Word macro that goes one page at a time through a multi page document of invoices and saves it as a Pdf. To ensure it give the page a unique name I set up an array in the Word macro. Is it possible though to read an Excel spreadsheet with the same macro that I can put the company name into? i.e. it reads the name from Excel but it is used in the word macro?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Turtle,

    Welcome to the Lounge as a New Poster!

    Yes this is entirely possible. You will have to do a little research on how you want to set it up though. You have 2 choices Early Binding (You need to set a reference to Excel in the VBE editor.) or Late Binding where the reference isn't needed.

    Early Binding Advantages:
    You can use intellisense to discover method & properties of Excel objects.
    Much easier debugging.

    Late Binding Advantages:
    Usually works with any version of Excel since the connection is resolved at run time.

    Here's an example of some code using Compiler Directives that allow you to switch between Early & Late Binding. The example uses Word to access Outlook to send email.

    Code:
    'Declaring Compiler Directive Constants:
    'Set Compiler Constant for Early/Late Binding conditional code
    ' 0 = Early Binding
    ' 1 = Late Binding
    #Const LateBinding = 1  'Place at top of Module for Module scope.
    'Set in Tools->[Project Name] Properties for Global Scope {i.e. all modules}
    
    
    'Dual Declarations for Early/Late Binding
    #If LateBinding = 0 Then    'Early Binding
       Dim appOL      As Outlook.Application
       Dim miMail     As Outlook.mailitem
    #Else
       Dim appOL      As Object
       Dim miMail     As Object
    #End If
    
    'Dual Code for Early/Late Binding
    #If LateBinding = 0 Then
           Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
    #Else
           Set miMail = appOL.CreateItem(0)
    #End If
    
    'Dual Code where builtin constants are used and not available in Late Binding
    #If LateBinding = 0 Then
           .Selection.HomeKey unit:=wdStory  'wdStory = 6
    #Else
           .Selection.HomeKey unit:=6
    #End If
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Turtle (2015-06-06)

  4. #3
    New Lounger
    Join Date
    Jun 2015
    Location
    Wrexham
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you for the information. This is a bit beyond the sort of code I've worked with before but i think i can work my way through it - thanks again :-)

  5. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts
    Why not write an Excel macro to work with the Excel spreadsheet?

Posting Permissions

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