Selection Object in Excel Object Model

I am coding against an Excel workbook from within Visual Basic (not VBA). I've recorded a macro and want to paste the code into my application.  A portion of the code generated within Excel is below. I know Range belongs the worksheet but I am wondering how I would code this within Visual Basic?
Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Font
        .FontStyle = "Bold"
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

Open in new window

LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
assuming you have already the object to the worksheet (say, variable is named objWorksheet), do the transformation like below:
avoid to use ".Select" and using the selection afterwards.
better use the range object itself, and use the properties as shown below.

if you are doing lots of formatting, you should read up the ScreenUpdating property of the excel application property
dim objRange as Excel.Range
set objRange = objWorksheet.Range( objWorksheet.Selection, objWorksheet.Selection.End(xlToRight))
with objRange
  .Font.FontStyle = "Bold"
  .Borders(xlDiagonalDown):LineStyle = xlNone
  .Borders(xlDiagonalUp):LineStyle = xlNone
  With .Borders(xlEdgeLeft)
     .LineStyle = xlContinuous
     .Weight = xlThin
     .ColorIndex = xlAutomatic
  End With
End with

Open in new window

0
dbbishopAuthor Commented:
objRange = .Range(objWorksheet.Selection, objWorksheet.Selection.End(xlToRight))

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll

"Public member 'Selection' on type 'Worksheet' not found."
0
dbbishopAuthor Commented:
AngelIII: Any more thoughts?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
how did you defined the objWorksheet variable, actually?
dim objRange as Excel.Range
objRange = objWorksheet.Range( objWorksheet.Selection, objWorksheet.Selection.End(xlToRight))
with objRange
  .Font.FontStyle = "Bold"
  .Borders(xlDiagonalDown):LineStyle = xlNone
  .Borders(xlDiagonalUp):LineStyle = xlNone
  With .Borders(xlEdgeLeft)
     .LineStyle = xlContinuous
     .Weight = xlThin
     .ColorIndex = xlAutomatic
  End With
End with

Open in new window

0
dbbishopAuthor Commented:
The entire Sub is below:

    Private Sub FormatExcelWorkbook(ByVal fileName As String)
        Dim objExcel As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim objRange As Excel.Range
 
        Const xlUp = -4162
        Const xlDiagonalDown = 5
        Const xlDiagonalUp = 6
        Const xlNone = -4142
        Const xlEdgeLeft = 7
        Const xlContinuous = 1
        Const xlThin = 2
        Const xlMedium = -4138
        Const xlAutomatic = -4105
        Const xlEdgeTop = 8
        Const xlEdgeBottom = 9
        Const xlEdgeRight = 10
        Const xlInsideVertical = 11
        Const xlInsideHorizontal = 12
        Const xlSolid = 1
        Const xlDown = -4121
        Const xlToRight = -4161
 
        objExcel = CreateObject("Excel.Application")
 
        objWorkbook = objExcel.Workbooks.Open(fileName)
        With objExcel
            .Application.ScreenUpdating = False
            .DisplayAlerts = False
        End With
 
        objWorksheet = objWorkbook.ActiveSheet
 
        With objWorksheet
            objWorksheet.Range("A1").Select()
            objRange = .Range(objWorksheet.selection, objWorksheet.selection.End(xlToRight))
            With objRange
                .Font.Bold = True
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Interior
                    .ColorIndex = 15
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                End With
                .Range("E2").Select()
                .Range(.Selection, .Selection.End(xlDown)).Select()
                .Range(.Selection, .Selection.End(xlToRight)).Select()
                With objRange
                    .Borders(xlDiagonalDown).LineStyle = xlNone
                    .Borders(xlDiagonalUp).LineStyle = xlNone
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                End With
                .Borders(xlInsideVertical).LineStyle = xlNone
                .Borders(xlInsideHorizontal).LineStyle = xlNone
            End With
        End With
 
        objExcel.Workbooks(0).Close(True)
        objExcel.Quit()
    End Sub

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, let's see:
 Private Sub FormatExcelWorkbook(ByVal fileName As String)
        Dim objExcel As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim objRange As Excel.Range
 
        Const xlUp = -4162
        Const xlDiagonalDown = 5
        Const xlDiagonalUp = 6
        Const xlNone = -4142
        Const xlEdgeLeft = 7
        Const xlContinuous = 1
        Const xlThin = 2
        Const xlMedium = -4138
        Const xlAutomatic = -4105
        Const xlEdgeTop = 8
        Const xlEdgeBottom = 9
        Const xlEdgeRight = 10
        Const xlInsideVertical = 11
        Const xlInsideHorizontal = 12
        Const xlSolid = 1
        Const xlDown = -4121
        Const xlToRight = -4161
 
        objExcel = CreateObject("Excel.Application")
 
        objWorkbook = objExcel.Workbooks.Open(fileName)
        With objExcel
            .Application.ScreenUpdating = False
            .DisplayAlerts = False
        End With
 
        objWorksheet = objWorkbook.ActiveSheet
 
        With objWorksheet
            objRange = objExcel.Intersect(objWorksheet.Range("1:1"), objWorksheet.UsedRange)
            With objRange
                .Font.Bold = True
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Interior
                    .ColorIndex = 15
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                End With
             End With
 
             objRange = objExcel.Intersect(objWorksheet.Range("E2:E64000"), objWorksheet.UsedRange)
             With objRange 
                    .Borders(xlDiagonalDown).LineStyle = xlNone
                    .Borders(xlDiagonalUp).LineStyle = xlNone
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                End With
                .Borders(xlInsideVertical).LineStyle = xlNone
                .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With
 
        objExcel.Workbooks(0).Close(True)
        objExcel.Quit()
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
Thanks, I'll give it a try. It will probably be Monday before I can try it out.
0
dbbishopAuthor Commented:
AngelIII, I got it to work. I have just one more quick question. At the end of my code, I have:

        objExcel.Quit()
        objExcel = Nothing

However, Excel remains active in Task Manager, and I want to make sure it terminates completely. Do you know what I need to do to make that happen?

Also, if Excel is already in memory when the method is executed, do you know what changes I need to make to use that instance of Excel rather than creating a new instance. I would still want to completely close down Excel when the procedure is done, but I want to make use of the loaded instance if it is there.
0
dbbishopAuthor Commented:
and... I know when I coed in VB6, intellisense was available for the constants (xlThin, xlMedium, etc.) but don't seem to be in VB.NET. Is there a way to expose them?

I know I'm stepping outside the scope of the original question and can ask it in another if you prefer. Just let me know.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
see this previous thread:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21622897.html

resume from the Assisted answers:
 
       ' do stuff
       System.Runtime.InteropServices.Marshal.ReleaseComObject(ws)
        ws = Nothing
        wb.close(False)
       System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
       wb = nothing  
   
        xlApp.quit()
       System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp)
        xlApp = Nothing
 
       Gc.Collect()
       Gc.WaitForPendingFinalizers()
 
also, this article should explain:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q317109

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in regards to the "intellisense", you just have to:
Excel.XlDirection.xlUp
instead of xlUp

so, the Excel class has it all, you just have to find the correct Enum ...
0
dbbishopAuthor Commented:
Great. That works. I got rid of all the constants. So any idea how to do the other?
0
dbbishopAuthor Commented:
Sorry, I didn't see your post prior to the one about the constants. Let me look it over.
0
dbbishopAuthor Commented:
I tried the code
System.Runtime.InteropServices.Marshal.ReleaseComObject(...)
on the range, worksheet, woorkbook and application, and the GC methods and still have Excel running in Task Manager even when the VB app shuts down.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you must be missing something, 1 little object, and it won't work (thanks MS)
0
dbbishopAuthor Commented:
Here is the full code:

For now, I am using
        Try
            xlExcelApp = GetObject(, "Excel.Application")
        Catch ex As Exception
            xlExcelApp = CreateObject("Excel.Application")
        End Try

at the start of the method, which just latches onto a running instance of Excel if it exists. Seems to work and keeps me from having multiple instances running. Unless you see something I am missing in my code below, I will probably leave it as-is.

I would like to insure that I've shut down the instance of Excel that I created, as it really does not need to be running. This app is going to be running on a disconnected machine, ultimately as a service if I can put those pieces together, and will be processing one file/day.
    Private Sub FormatExcelWorkbook(ByVal fileName As String)
        Dim xlExcelApp As Excel.Application
        Dim xlWorkbook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim xlRange As Excel.Range
 
        xlExcelApp = CreateObject("Excel.Application")
 
        xlWorkbook = xlExcelApp.Workbooks.Open(fileName)
        With xlExcelApp
            .Application.ScreenUpdating = False
            .DisplayAlerts = False
        End With
 
        xlWorksheet = xlWorkbook.ActiveSheet
 
        With xlWorksheet
            xlRange = xlExcelApp.Intersect(xlWorksheet.Range("1:1"), xlWorksheet.UsedRange)
            With xlRange
                .Font.Bold = True
                .Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                .Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlThin
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlThin
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlMedium
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlThin
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlThin
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Interior
                    .ColorIndex = 15
                    .Pattern = Excel.XlPattern.xlPatternSolid
                    .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
            End With
 
            xlRange = xlExcelApp.Intersect(xlWorksheet.Range("E2:F64000"), xlWorksheet.UsedRange)
            With xlRange
                .Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                .Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlMedium
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlMedium
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlMedium
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .Weight = Excel.XlBorderWeight.xlMedium
                    .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                End With
                .Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                .Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
            End With
        End With
 
        ComReleaseObject(xlRange)
        ComReleaseObject(xlWorksheet)
        xlWorkbook.Close(True)
        ComReleaseObject(xlWorkbook)
 
        xlExcelApp.Quit()
        ComReleaseObject(xlExcelApp)
 
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
 
    Private Sub ComReleaseObject(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to be honest, i am a bit surprised by the ComReleaseObject to say "byval" ...
I would think that it should be byref:
Private Sub ComReleaseObject(byref o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

Open in new window

0
dbbishopAuthor Commented:
I can give that a try. Will probably be tomorrow.
0
dbbishopAuthor Commented:
AngelIII - I am going to PAQ this with your solution. Although I was not able to get the application to completely terminate Excel, my workaround appears to be working. Thank you for going beyond my original question and trying to help me solve the other problem. FYI, I did try changing the signature to ByRef but that didn't help.

Doug
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.