none
Intrinsic Constant In Excel RRS feed

  • Question

  • In an Excel Macro example,"previous thread", the statement: "Range("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" is used. I thought this would be useful in a Outlook Macro to find the rightmost empty cell.  However, this statement does works in an Excel macro but not in a Outlook Macro.  The problem appears to be the intrinsic constant "xlToLeft".  Outlook help come back no record when searching for "xlToLeft"

    I readly amitt my lack of understanding. But, myunderstanding is that "Automation" provides access to all Excel comands.  Is this correct?  Do I have to somehow declare Excel intrinic constants or a link to an Excel libary?

    What am I not understanding?

    Sunday, March 4, 2012 7:22 PM

Answers

  • Do you have a project reference set to Excel in your Outlook VBA project? If not then VBA in Outlook won't have a clue as to what the Excel constant "xlToLeft" is.
     
    In the VBA project select Tools, References and make sure that Excel is referenced.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "JoeRob" <=?utf-8?B?Sm9lUm9i?=> wrote in message news:1d28b517-b6d3-4a7e-a7b7-fdc1e842ec8e...

    In an Excel Macro example,"previous thread", the statement: "Range("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" is used. I thought this would be useful in a Outlook Macro to find the rightmost empty cell.  However, this statement does works in an Excel macro but not in a Outlook Macro.  The problem appears to be the intrinsic constant "xlToLeft".  Outlook help come back no record when searching for "xlToLeft"

    I readly amitt my lack of understanding. But, myunderstanding is that "Automation" provides access to all Excel comands.  Is this correct?  Do I have to somehow declare Excel intrinic constants or a link to an Excel libary?

    What am I not understanding?


    Ken Slovak MVP - Outlook
    Tuesday, March 6, 2012 4:01 PM
    Moderator

All replies

  • Hi Joe,

    Welcome! From the constant name xlToLeft, it prefixed with "xl" which means this is Excel API. So it could not work in Outllook macro.  If you can elaborate your goal in more detail, people may help further.  From your statement "Outlook Macro to find the rightmost empty cell", the goal is not so clear.  Outlook doesn't have cells builtin, is it a table you added?

    [edit]Outlook doesn't provide macro recording feature.

    Please let me know if there's anything unclear, I'll try to assist.


    Forrest Guo | MSDN Community Support | Feedback to us


    Tuesday, March 6, 2012 5:53 AM
    Moderator
  • Do you have a project reference set to Excel in your Outlook VBA project? If not then VBA in Outlook won't have a clue as to what the Excel constant "xlToLeft" is.
     
    In the VBA project select Tools, References and make sure that Excel is referenced.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "JoeRob" <=?utf-8?B?Sm9lUm9i?=> wrote in message news:1d28b517-b6d3-4a7e-a7b7-fdc1e842ec8e...

    In an Excel Macro example,"previous thread", the statement: "Range("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" is used. I thought this would be useful in a Outlook Macro to find the rightmost empty cell.  However, this statement does works in an Excel macro but not in a Outlook Macro.  The problem appears to be the intrinsic constant "xlToLeft".  Outlook help come back no record when searching for "xlToLeft"

    I readly amitt my lack of understanding. But, myunderstanding is that "Automation" provides access to all Excel comands.  Is this correct?  Do I have to somehow declare Excel intrinic constants or a link to an Excel libary?

    What am I not understanding?


    Ken Slovak MVP - Outlook
    Tuesday, March 6, 2012 4:01 PM
    Moderator
  •  My project is an Outlook Macro that opens an Excel worksheet.  Cells in the worksheet are filled with data that I want to access.  I used the line of code "Range ("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" as a test.  After dimensioning MyXL as Object, I set MyXl to the worksheet, using code examples for doing this.  I know that MyXl has been set because I can access the worksheet, i.e. "MyXL.Range("A1").select".  According to the example I used, the line of code above should select the first empty cell to the right.  For instance, if "F1" has data the line of code selects "G1".  This works fine in an Excel macro, but does not work in an Outlook macro.  Also, I have tried MyXl.Range("IV1".... and other variations.  Nothing I tried seems to work in Outlook.

    Lastly, want I really wanted to do is find the bottom most cell, i.e. "xlDown".  I was using the sample code to understand the method.

    Any replied are appreciated

    Tuesday, March 6, 2012 4:59 PM
  • Object has no properties, it's late bound. You need to declare objects as fully qualified in Outlook code for Excel objects to work, and you need to reference Excel as I mentioned. Declare MyXL as Excel.Application, and fully declare all other Excel objects.
     
    In Excel VBA code you'd need to do the same thing for any Outlook code.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "JoeRob" <=?utf-8?B?Sm9lUm9i?=> wrote in message news:ca12ae4f-304c-43e3-b37b-f2fbe8068594...
     My project is an Outlook Macro that opens an Excel worksheet.  Cells in the worksheet are filled with data that I want to access.  I used the line of code "Range ("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" as a test.  After dimensioning MyXL as Object, I set MyXl to the worksheet, using code examples for doing this.  I know that MyXl has been set because I can access the worksheet, i.e. "MyXL.Range("A1").select".  According to the example I used, the line of code above should select the first empty cell to the right.  For instance, if "F1" has data the line of code selects "G1".  This works fine in an Excel macro, but does not work in an Outlook macro.  Also, I have tried MyXl.Range("IV1".... and other variations.  Nothing I tried seems to work in Outlook.

    Lastly, want I really wanted to do is find the bottom most cell, i.e. "xlDown".  I was using the sample code to understand the method.

    Any replied are appreciated


    Ken Slovak MVP - Outlook
    Tuesday, March 6, 2012 5:23 PM
    Moderator
  • Ken,

    ]Thanks for your reply.  I'm still confussed.  I've include somemore code.  Perhaps you could comment on your previous response.  I think I declared the Excel object and Linked (is that the correct word) with Excel.

    Dim MyXL As Object

    Set MyXL = GetObject("C:\Documents and Settings\Joe Robinson\My Documents\Joe's Files\Men's Club Files\HandiCap List To CleanUP.xls")

        ' Show Microsoft Excel through its Application property. Then
        ' show the actual window containing the file using the Windows
        ' collection of the MyXL object reference.
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(2).Visible = True
    Set MyXL = MyXL.Application

         MyXL.Columns("A:A").ColumnWidth = 10
      
        MyXL.Columns("B:B").Delete Shift:=xlToLeft
        MyXL.Columns("B:B").ColumnWidth = 20
       
        MyXL.Columns("C:C").Delete Shift:=xlToLeft

    In the above code, the ColumnWidth and Delete statements work fine.  Note the use of the intrinic constant "xlToLeft".

    But, the statement "Range("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" doesn't work.

    Again note that I tried "MyXL.Range("A1").Copy Destination:= MyXL. Range("..... and variations, but nothing seems to work.

    Your response and help are greatly appricated.

    JoRob

    Tuesday, March 6, 2012 7:00 PM
  • If I were doing some work with Excel from the Outlook VBA project I'd do it something like this, after ensuring that a reference to Excel was set in Tools, References:
     
    Dim oBook As Excel.Workbook
    Dim oExcel As Excel.Application
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
      
    Set oBook = oExcel.Workbooks.Open("("C:\Documents and Settings\Joe Robinson\My Documents\Joe's Files\Men's Club Files\HandiCap List To CleanUP.xls")
     
    In Outlook code I'd try the destination Range as Range("IV1").End(XlDirection.xlToLeft).Offset(0, 1), assuming "IV1" is valid. Are you sure that "IV1" is valid?
       
     
    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "JoeRob" <=?utf-8?B?Sm9lUm9i?=> wrote in message news:d634365e-5c03-4630-bdf3-b8a5f4920144...

    Ken,

    ]Thanks for your reply.  I'm still confussed.  I've include somemore code.  Perhaps you could comment on your previous response.  I think I declared the Excel object and Linked (is that the correct word) with Excel.

    Dim MyXL As Object

    Set MyXL = GetObject("C:\Documents and Settings\Joe Robinson\My Documents\Joe's Files\Men's Club Files\HandiCap List To CleanUP.xls")

        ' Show Microsoft Excel through its Application property. Then
        ' show the actual window containing the file using the Windows
        ' collection of the MyXL object reference.
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(2).Visible = True
    Set MyXL = MyXL.Application

         MyXL.Columns("A:A").ColumnWidth = 10
      
        MyXL.Columns("B:B").Delete Shift:=xlToLeft
        MyXL.Columns("B:B").ColumnWidth = 20
       
        MyXL.Columns("C:C").Delete Shift:=xlToLeft

    In the above code, the ColumnWidth and Delete statements work fine.  Note the use of the intrinic constant "xlToLeft".

    But, the statement "Range("A1").Copy Destination:= Range("IV1").End(xlToLeft).Offset(0, 1)" doesn't work.

    Again note that I tried "MyXL.Range("A1").Copy Destination:= MyXL. Range("..... and variations, but nothing seems to work.

    Your response and help are greatly appricated.

    JoRob


    Ken Slovak MVP - Outlook
    Tuesday, March 6, 2012 7:51 PM
    Moderator
  • Ken,

     

    Thank you for responding.  I copied your code to a macro, but got an error message when I tried to compile the code.  So I reread your message and tried to read what you said not what I thought you said.  And, I read "after ensuring that a reference to Excel was set in Tools, References:".  What's that! As you know there is a reference to 'Microsoft Excel 12.0 Object Library"  And once set, your code compiles and what more important my code runs as expected.  The line "MyExcel.Range("A1").Copy Destination:= MyExcel.Range("IV1").End(xlToLeft).Offset(0, 1)" does exactly what I expected it to do.

    Would you take a moment to respond to another question.  I used an example and the GetObject method to open the Excel workbook and access the workbook.  You used the CreateObject method and open command to accomplish the same thing.  What is the difference in the two methods?  And, why use one method instead of the other?

    JoRob

    Wednesday, March 7, 2012 2:12 AM
  • Joe,

    Nice to know Ken's suggestion works for your scenario. Would you please create new thread for new question? This way we keep this thread has a good and clear topic. This could benifit community members in long term.

    Thanks and Best Regards,


    Forrest Guo | MSDN Community Support | Feedback to us

    Thursday, March 8, 2012 7:51 AM
    Moderator
  • CreateObject() will create a new instance of whatever's being instantiated, GetObject() can get a running instance or create one, depending on how the call is set up.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "JoeRob" <=?utf-8?B?Sm9lUm9i?=> wrote in message news:7557bfd6-8b68-47dd-a826-c75227716561...

    Ken,

     

    Thank you for responding.  I copied your code to a macro, but got an error message when I tried to compile the code.  So I reread your message and tried to read what you said not what I thought you said.  And, I read "after ensuring that a reference to Excel was set in Tools, References:".  What's that! As you know there is a reference to 'Microsoft Excel 12.0 Object Library"  And once set, your code compiles and what more important my code runs as expected.  The line "MyExcel.Range("A1").Copy Destination:= MyExcel.Range("IV1").End(xlToLeft).Offset(0, 1)" does exactly what I expected it to do.

    Would you take a moment to respond to another question.  I used an example and the GetObject method to open the Excel workbook and access the workbook.  You used the CreateObject method and open command to accomplish the same thing.  What is the difference in the two methods?  And, why use one method instead of the other?

    JoRob


    Ken Slovak MVP - Outlook
    Thursday, March 8, 2012 4:07 PM
    Moderator