Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with a range (Excel 2002)

    Hello all,

    It's been a long long time but I'm once again working with Excel programming and I find myself back here.

    I'm exporting data from a program, SalesLogix, to Excel and I'm having trouble formatting a range.

    This code works:
    objExcel.Range("A" & intRow & "" & intRow).Interior.colorIndex = 1
    objExcel.Range("A" & intRow & "" & intRow).Interior.Pattern = xlSolid
    objExcel.Range("A" & intRow & "" & intRow).Merge

    But I need to be able to replace the column reference D with a variable, and I don't know how I would do that?
    I need to be able to select a Range so I can merge the cells.(Or center across selection)

    Right now I have two integers, intCol and intRow.
    This works, but it isn't a range:
    objExcel.Cells(intRow, intCol).Interior.colorIndex = 1
    objExcel.Cells(intRow, intCol).Interior.Pattern = xlSolid

    I can't figure out how to use the variables to select a Range.
    objExcel.Range(Cells(1, 1), Cells(3, 4)).Merge
    Doesn't work.

    Any idea's?

    Thank you,
    Justin K.

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

    Re: Working with a range (Excel 2002)

    Welcome back!

    From your code it appears that you are automating Excel from another program. If so, you must qualify ALL Excel objects. In the line

    objExcel.Range(Cells(1, 1), Cells(3, 4)).Merge

    Range belongs to objExcel but both occurrences of Cells don't belong to anything. This causes confusion. It should be

    objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(3, 4)).Merge

    With your variables:

    objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCol)).Merge

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with a range (Excel 2002)

    Hello,

    I had forgotten how fast you work around here. Thanks! It of course worked.

    Now I'm having an issue centering the cells after I've merged them.

    objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).HorizontalAlignment = xlCenter
    objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).Merge
    objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).HorizontalAlignment = xlCenter

    Just to be on the safe side I tried centering it before and after the merge, as well as separately. When recording a macro in Excel this is the property it uses, but it doesn't appear to be working when passed in from another program?

    Thanks again,
    Justin K.

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

    Re: Working with a range (Excel 2002)

    Have you set a reference to the Microsoft Excel 10.0 Object Library? If not, xlCenter is unknown. If you require all variables and constants to be declared explicitly, this would cause an error message, but if you don't, VBA will assume that xlCenter is a new variable with value 0, which is obviously not what you want. Try replacing xlCenter with its value -4108.

    BTW I don't like merged cells in Excel - they cause problems when you try to copy/paste, among other things. You can get the same visual effect off merge and center without the disadvantages by using

    objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).HorizontalAlignment = 7

    where 7 is the value of xlCenterAcrossSelection.

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with a range (Excel 2002)

    Hello,

    Once again that did it, using the value worked. Where would I be able to find these values out on my own?
    Also, if I needed to, how would I set a reference to the Microsoft Excel 10.0 Object Library?

    I agree with you about the merged cells, but that's not what was asked for. I'll have to see if I can change the design specs.

    Thanks again!
    Justin K.

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

    Re: Working with a range (Excel 2002)

    You can find the value of Excel-specific constants in the Visual Basic Editor in Excel itself.

    Method 1:
    - Press Ctrl+G to activate the Immediate window.
    - Type <code>? xlCenter</code>
    - Press Enter.

    Method 2:
    - Press F2 to activate the Object Inspector.
    - Type <code>xlCenter</code> in the Search box.
    - Press Enter.

    You can set a reference by ticking the corresponding check box in Tools | References... in the Visual Basic Editor.

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with a range (Excel 2002)

    Hello,

    Thanks again for all of your help. Hopefully now I can move forward without having to bother you every few hours.

    Justin K.

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

    Re: Working with a range (Excel 2002)

    It's best of course if you can move forward yourself, but don't hesitate to ask questions if necessary - that's what we're here for! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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