locked
Access Excel methods from Outlook doesnt work RRS feed

  • Question

  • Hello,

    i start a macro from Outlook and access Excel - neither the range command nor the selection  of the slicer object work - what do id do wrong

    Thank you in advance

    Martin

    Sub Digitalisierung()
    
    Dim ar
    
    
    
     Dim xlObj As Object
     Dim rn As Range
     Dim wb As Workbook
     Dim ws As Worksheet
     
        On Error Resume Next
    
        Set xlObj = GetObject(, "Excel.Application")    'Get or open excel application
    
        If Err.Number <> 0 Then
    
            Err.Clear
    
            Set xlObj = CreateObject("Excel.Application")
    
        End If
        Set wb = Workbooks("Digipivot.xlsm")
        Set ws = wb.Sheets("Dashboard")
    
        Set rn = ws.Range("a8")
        rn.Select
        ar = wb.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList
        wb.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList = Array(ar(1))
        AppActivate ("Digipivot.xlsm")
     
    End Sub

    Saturday, April 14, 2018 7:27 PM

All replies

  • When you automate another application - Excel in this situation - you must make sure that ALL object variables in that application refer directly or indirectly to the application object that you create. Your variable wb does not refer to xlObj, so it causes problems.

    Change

        Set wb = Workbooks("Digipivot.xlsm")

    to

        Set wb = xlObj.Workbooks.Open("Digipivot.xlsm")

    By the way, if you have set a reference to the Microsoft Excel n.0 Object Library (n depends on your version), I'd change the declarations to

    Dim xlObj As Excel.Application
    Dim rn As Excel.Range
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    If not, I'd use

    Dim xlObj As Object
    Dim rn As Object
    Dim wb As Object
    Dim ws As Object


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 14, 2018 7:54 PM
  • thank you very much for your help - i have changed it - but it still doesnt work - but it still doesnt work - the cell a8 is not selected and the slicer item wasnt selected - any ideas left - thank you!

    Martin

    Sub Digitalisierung()

    Dim ar

     Dim xlObj As Excel.Application
    Dim rn As Excel.Range
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

     
        On Error Resume Next

        Set xlObj = GetObject(, "Excel.Application")    'Get or open excel application

        If Err.Number <> 0 Then

            Err.Clear

            Set xlObj = CreateObject("Excel.Application")

        End If
       ' Set wb = Workbooks("Digipivot.xlsm")
        Set wb = xlObj.Workbooks.Open("Digipivot.xlsm")
        Set ws = wb.Sheets("Dashboard")

        Set rn = ws.Range("a8")
        rn.Select
        ar = wb.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList
        wb.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList = Array(ar(1))
        AppActivate ("Digipivot.xlsm")
     
    End Sub

    Sunday, April 15, 2018 3:57 PM
  • I'd have to see a copy of the workbook without sensitive/proprietary information...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, April 15, 2018 4:44 PM
  • Hi krugi,

    I try to make a test with your code from Outlook.

    I try to change the xlObj , wb, ws, rn  to Object type.

    Then I try to open the Excel file and try to select the cell.

    I find that it is working properly.

    I suggest you to again make a test and try to check the result and let us know about it.

    We will try to provide further suggestions, If needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 16, 2018 5:57 AM
  • thank you very much - your code with a new example file works perfect but if i use my file (even if i try in a blank new sheet) doesnt work - could it be that it is because it is an macro file?

    Any other idea?

    Thank you

    Martin

    Tuesday, April 17, 2018 5:10 PM
  • Hi krugi,

    You had asked,"could it be that it is because it is an macro file?"

    I don't think it makes any difference whether it is a .xlsx file or .xlsm file. All type of file should work without any issue.

    I am not sure here because you just mentioned that,"it doesn't work".

    Did you got any error?

    If possible then try to post a sample file. We will try to check the file and try to find the root cause for this issue.

    We will try to make a test with it on our side to check the issue.

    Then we can try to provide accurate suggestion to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 3:05 AM
  • Hi krugi,

    Is your issue solved?

    I find that you did not follow up this thread for a long time.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 27, 2018 9:31 AM