(SOLVED) Can't Close Excel Aplication (Range Issue)

[G-Hawk]

Hi... I have been looking around for this problem, I already found where my problem is but I don't know how to solved.

I'm sure that my problem has to be with Range (I got this from a VBA but I don't know how to translate it to VB).

I'm trying to copy some information from Excel to a Flexgrid, but I have to sort this information before... after that I'm closing Excel but that's not happening, in the task manager I can see the process still running and closing only when I close my program.

Public oApp As Excel.Application
Public oWB As Excel.Workbook
Public osH As Excel.Worksheet
Public oRng As Excel.Range
  
   Set oApp = New Excel.Application
   oApp.Visible = False
   Set oWB = oApp.Workbooks.Open(Archivo)
   Set osH = oWB.Sheets(oWB.Sheets.Count)
   Set oRng = osH.Range("A1")
'*** Now I don't know how to continue...
   oApp.ActiveWorkbook.ActiveSheet.Range(oApp.Selection, oApp.Selection.End(xlDown)).Select
   oApp.Range(oApp.Selection, oApp.Selection.End(xlDown)).Select
   CalcRows = oApp.Selection.Rows.Count
   Clipboard.Clear
   oApp.ActiveWorkbook.ActiveSheet.Range("A1").Select
   oApp.ActiveWorkbook.ActiveSheet.Range(oApp.Selection, oApp.Selection.End(xlDown)).Select
   oApp.ActiveWorkbook.ActiveSheet.Range(oApp.Selection, oApp.Selection.End(xlToRight)).Select
   oApp.Selection.Sort Key1:=Range(Chr(Asc("A") + RosGL - 1) & "2"), Order1:=xlAscending, _
        Key2:=Range(Chr(Asc("A") + RosCoAst - 1) & "2"), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers
   oApp.Selection.Copy
   FG2.Clear
   FG2.Rows = CalcRows
   FG2.Cols = RosCols
   FG2.Redraw = False
   FG2.Row = 0
   FG2.Col = 0
   FG2.ColSel = FG2.Cols - 1
   FG2.RowSel = FG2.Rows - 1
   FG2.Clip = Replace(Clipboard.GetText, vbNewLine, vbCr)
   FG2.Col = 1
   oApp.DisplayAlerts = False
   oWB.Close SaveChanges:=False    '(App.Path + "\Test1.xls")          '"D:\Test.xls"
   Set oWB = Nothing
   LogError = "Archivo cerrado"
   oApp.Quit
   Set oApp = Nothing

[zaza]

Get rid of those ActiveWorkbooks and ActiveSheets - they only apply when you specifically want to use the active book/sheet. Replace them with specific directions as to what object you are using, eg. oWB to use the workbook, osH to use that specific sheet, oWB.worksheets(1) etc.

Also, get rid of the Selections and refer directly to the range. Why select a range and then do something to the current selection if you already know what the Range is? Just do it to the Range itself!


This is the one issue with using VBA macros to record your VBA - Excel interprets your actions using the ActiveBook / Sheet and the Selection object because that is what you are using; however for generic coding purposes it will not do.


zaza

[si_the_geek]

Indeed, as an example "oApp.ActiveWorkbook" should be replaced by "oWB", and "oApp.ActiveWorkbook.ActiveSheet" should be replaced by "osH".

You should remove the Selection too as zaza mentioned.. but I'm not entirely sure how in this case as you are extending the selection.

The actual problem you have noticed is on the Sort line.. which is caused by the fact you have specified just Range instead of sheetobject.Range


By the way, VBCode tags have changed now - you need to use this instead: [highlight=vb] code here [/highlight]

[G-Hawk]

Tnxs zaza & si_the_geek for your answers... anyway I'm still having the same problem :

@zaza:
How can I get rid off this:

oApp.ActiveWorkbook.ActiveSheet.Range(oApp.Selection, oApp.Selection.End(xlDown)).Select

I'm using this because with this statement I would know the number of columns I have to set up the FG. Now, if I don't use select then hou can I sort or copy the information

@si_the_geek:

I tried your suggestion but I still can't close Excel, anyway that should have done the job because I use the same code in other function but without the sort statement and I have no problems closing Excel.

[si_the_geek]

I've just had another look, and I think lines 16 to 18 can be replaced by this: osH.UsedRange.Select

..and to eliminate the Select/Selection, merge it with line 19 like this (also includes 2 corrections for the Range issue):

osH.UsedRange.Sort Key1:=osH.Range(Chr(Asc("A") + RosGL - 1) & "2"), Order1:=xlAscending, _
        Key2:=osH.Range(Chr(Asc("A") + RosCoAst - 1) & "2"), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers

If that doesn't solve it, show us your current code with the corrections mentioned above (using oWB etc).

[G-Hawk]

si_the_geek... thanks I already got the idea.. and now is working perfectly

Regards!