Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm creating a tool that cuts an incoming e-mail (which is in a specific format because it comes from an online submission form) into its individual bits, displays those different bits in a dialog box, allowing the e-mail monitor to correct them and add more information from drop-downs and such, and then passes those bits as an array to an Excel subroutine. The Excel subroutine then looks at the data in the array and, based on that information, adds a new entry in a table on one of 14 worksheets. Outlook then files that e-mail in one of 16 e-mail folders.

    After a month of working, I have the Outlook end functioning, and I have the Excel end functioning. But I can't figure out how to link the two.

    At the end of Outlook's process, I have an array called ExcelArray() that needs to become an argument for an Excel subroutine: Sub EmailInfo(ParamArray OutlookArray As Variant)

    I've tried a few things, and none of them seem to work:

    Dim XL as Object
    Set XL = GetObject("C:\[pathname]\EmailTracking.xlsm")

    XL.Run EmailInfo(ExcelArray) 'doesn't work
    XL.Run "EmailInfo" , ExcelArray 'does work regardless of whether I put parens after ExcelArray
    Call XL.EmailInfo(ExcelArray) 'throws an error

    I'm so close to a solution here that I can taste it. I'd much rather taste the elixir of victory than this bitter near-win. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It'd be best to include the Excel code in the Outlook code or vice versa (depending on where you want to run the code from), using Automation.

    For example, if you run the code from Outlook, you can set a reference to the Microsoft Excel 12.0 Object Library in Tools | References...
    You can then start an instance of Excel from your code, open the appropriate workbook and apply Excel VBA to it. Because the code runs in your Outlook macro, it can use your array.
    You must take great care to make each instance of an Excel object refer back directly or indirectly to the Excel application object.

    For example:

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open("C:\[pathname]\EmailTracking.xlsm")
    ' Excel code follows here
    ...
    ' Correct usage:
    MsgBox xlWbk.Worksheets.Count
    ' Wrong usage:
    MsgBox ActiveWorkbook.Worksheets.Count

    The latter instruction uses ActiveWorkbook without referring it back to xlApp. This may cause a second instance of Excel to be started that will remain in memory after your code has finished.

  3. #3
    Lounger
    Join Date
    Oct 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry

    (sigh)

    Not what I wanted to hear, Hans. But thanks. I'd appreciate any other workarounds.

    Maybe I could write the data to a temporary text file, call the Excel code without arguments, and have Excel retrieve the data from the temp file and then delete the file?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Andyman' post='777996' date='02-Jun-2009 17:57']Maybe I could write the data to a temporary text file, call the Excel code without arguments, and have Excel retrieve the data from the temp file and then delete the file?[/quote]
    That should be workable...

  5. #5
    Lounger
    Join Date
    Oct 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Hans. Even though you didn't give me exactly the answer I had hoped for, you did point me in the right direction.
    For future Loungers, to pass an array of values from Outlook to Excel, I created a new temporary (and hidden) worksheet in Excel, exported the values from the Outlook array into a specific range in that new sheet, and then called the Excel code from Outlook. Then, in Excel, I created a new Array from the values in the temporary worksheet and then deleted the worksheet.

    But first, I had to work around the problem of whether or not the source Excel file was already open. Here's how it played out:

    In Outlook:
    [pre]
    Dim XL As Excel.Application
    Dim XLWB As Excel.Workbook
    Dim XLWS As Excel.Worksheet

    'Create a new Excel object or get the one that's already open
    If Excel.Application.Workbooks.Count = 0 Then
    Set XL = CreateObject("Excel.Application")
    Else:
    Set XL = Excel.Application.Workbooks(1).Application
    XL.Visible = True
    End If

    On Error Resume Next
    'Set XLWB to the right worksheet, whether it's already opened or not
    Set XLWB = Excel.Application.Workbooks("EmailTracking.xlsm")
    If Err <> 0 Then
    Set XLWB = Nothing 'I'm not sure whether this is necessary, but it couldn't hurt, right?
    Set XLWB = XL.Workbooks.Open("C:\[pathname]\EmailTracking.xlsm")
    Err.Clear
    End If
    XL.ScreenUpdating = False

    'create new "temp" worksheet to hold values
    Set XLWS = XLWB.Worksheets.Add
    With XLWS
    .Name = "temp"
    .Visible = False
    End With
    ...[lotsa other code here]...
    XL.ScreenUpdating=True
    XL.Run "acSubmission" 'acSubmission is the subroutine in Excel
    Set XL = Nothing
    Set XLWB = nothing
    Set XLWS = Nothing
    [/pre]

    Then I placed the array values in the first column of Worksheet("temp"). There are a number of ways you can do this.

    Then, on the Excel end, I began the acSubmission code with
    [pre]
    Application.DisplayAlerts = False
    [/pre]
    So that the warning message doesn't come up when the code deletes the "temp" worksheet. (Remember to change this back to Application.DisplayAlerts = True at the end.)

    Then I filled newExcelArray(10) with the values on the "temp" sheet and deleted the "temp" sheet. The rest of the code ran as it should.

    I hope someone someday finds this useful. Now if I can only add three buttons to the Outlook Mail.Read Inspector without having to create a COM add-in.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To check whether Excel is running you can use code like this:

    Code:
    Dim XL As Excel.Application
    On Error Resume Next
    Set XL = GetObject(, "Excel.Application")
    If XL Is Nothing Then
      Set XL = CreateObject("Excel.Application")
      If XL Is Nothing Then
    	MsgBox "Can't activate or start Excel.", vbExclamation
    	Exit Sub
      End If
    End If
    On Error GoTo 0 ' or specify an error handler label
    ...

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you could also create e public property in the excel workbook, pass the array to that and have your macro read from there.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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