[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2290
  • Last Modified:

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

0
dbbishop
Asked:
dbbishop
  • 12
  • 7
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now