Global 'Excel.Range' alternative?

[rrenis]

Hi - I'm using the following code which exports data from MS Project to Excel. Unfortunately Excel doesn't shut down when the code has finished and I cannot open the xls file created by the code (filename.xls). I'm sure that it's the global Excel.Range that's causing the problem but can't find a way around it :banghead:

I was hoping that someone may be able to suggest a way of altering the code to remove the global Excel.Range? : pray2:


Option Explicit
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range

Sub CreateList()
Dim fso As New FileSystemObject
Dim xlApp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim xlWorkSheet As Object
Dim tsk As Task
Dim Tsks As Tasks
Dim TaskNumber As Integer
Dim sRef As String
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.ScreenUpdating = False
Set xlbook = xlApp.Workbooks.Add
Set xlsheet = xlbook.Worksheets.Add
xlsheet.Name = "Tasks"
xlApp.DisplayAlerts = False
If fso.FileExists("C:\filename.xls") Then
fso.DeleteFile ("C:\filename.xls")
End If
xlbook.SaveAs FileName:="C:\filename.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sRef = Left(ActiveProject.Name, 4)
Set Tsks = ActiveProject.Tasks
For Each tsk In Tsks
If Not tsk Is Nothing Then
Set xlCol = xlRow.Offset(0, TaskNumber)
If TaskNumber = 0 Then xlCol = sRef
rgt 1
TaskNumber = TaskNumber + 1
xlCol = tsk.Start
rgt 1
xlCol = tsk.Finish
End If
Next tsk
xlbook.Save
Set xlApp = Nothing
Set fso = Nothing
End Sub

Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub

Sub rgt(i As Integer)
Set xlCol = xlCol.Offset(0, i)
End Sub


Thanks for looking :)

Cheers,
rrenis

[Bob Phillips]

Quit Excel



xlbook.Save
xlApp.Quit
Set xlApp = Nothing
Set fso = Nothing
End Sub

Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub

Sub rgt(i As Integer)
Set xlCol = xlCol.Offset(0, i)
End Sub

[rrenis]

Hi xld, thanks for your help. Unfortunately this still doesn't close excel in the task manager for some reason. :banghead:

By the way, I've been messing around with the code and I'd accidently missed some of the code when I posted previously, the following should be between the "xlbook.SaveAs..." code and the "sRef = Left..." code.


Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
dwn 1
xlRow = "Tasks"
dwn 1

Do you think that the problem lies with the global Excel.Range or should xlApp.Quit have shut down all instances of excel created from the code? :think:

cheers,
rrenis

[Kenneth Hobs]

I would recommend that you try GetOject before CreateObject as I did for this MSWord example. Quiting when Excel was already open might cause some users grief.


Sub FillForm()
Dim WDApp As Object, wd As Object, rn As Long
Dim myRange As Excel.Range, a() As Variant
rn = ActiveCell.Row
Set myRange = ThisWorkbook.Worksheets("Sheet1").Range("A1", _
Range("E" & Rows.Count).End(xlUp))
On Error Resume Next
Set WDApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WDApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wd = WDApp.Documents.Open(ThisWorkbook.Path & "\Test.doc")
WDApp.Visible = True
With wd
'.FormFields("Brand").Result = Cells(rn, "B")
.Bookmarks.Item("Bookmark1").Range.InsertAfter Join(myRange, vbCrLf)
End With
Set wd = Nothing
Set WDApp = Nothing
End Sub

Notice that I set the objects to Nothing in reverse order.

Here is a routine that you can use to check if "EXCEL.EXE" is running.

Function ISexeRunning(EXEName As String) As Boolean
Dim tf As Boolean
Dim oShell As Object, oWMI As Object, cApps As Object
Dim sQuery As String
Set oShell = CreateObject("Shell.Application")
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sQuery = "Select Name From Win32_Process Where Name='" & Left(EXEName, 15) & "'"
Set cApps = oWMI.ExecQuery(sQuery)
tf = cApps.Count > 0
Set cApps = Nothing
Set oWMI = Nothing
Set oShell = Nothing
ISexeRunning = tf
End Function

[Bob Phillips]

I would recommend that you try GetOject before CreateObject as I did for this MSWord example. Quiting when Excel was already open might cause some users grief.

How can it? If he creates an Excel instance, he owns that instance, so quitting it is not a problem.

[rrenis]

Hi Kenneth Hobs and xld :hi:

I guess ultimately terminate process code could be used if Kenneth's IsexeRunning shows that Excel is still in the process list following the completion of the code. Whilst an option it would ideally be a last resort as there may be times where excel may already be open with another spreadsheet - it would be an inconvenience to have to shut excel down following the execution of the code, although I suppose this may be the only option.

I still feel that the problem lies with the Global Excel.Range used across the 3 Subs - although I'm certainly a VBA novice so it's more of a hunch really. I've tried to think of an alternative way of doing aside from the Global Excel.Range but keep on coming up with nothing viable. I'd be very grateful for any suggestions, if not then I'll go with the option of terminating the excel process after the code has finished.

Cheers,
rrenis

[Kenneth Hobs]

See if this helps any. I like to mix early and late binding.

Sub CreateTestXLS()
Dim xlApp As Excel.Application 'Early Binding
'Dim xlApp As Object 'Late Binding
Dim xlsheet As Excel.Worksheet
Dim xlbook As Excel.Workbook
Dim r As Excel.Range
On Error GoTo theEnd
Set xlApp = CreateObject("excel.application") 'Better method
'Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.ScreenUpdating = False
Set xlbook = xlApp.Workbooks.Add
Set xlsheet = xlbook.Worksheets.Add
xlsheet.Name = "Greetings"
Set r = xlsheet.Range("A1")
r.Value = "Hello World!"
On Error Resume Next
Kill "c:\temp\test.xls"
On Error GoTo theEnd
xlbook.SaveAs "c:\temp\test.xls"
xlbook.Close False
theEnd:
On Error Resume Next
Set xlsheet = Nothing
Set xlbook = Nothing
xlApp.Quit
Set xlApp = Nothing
Shell "cmd /c c:\temp\test.xls"
End Sub


Shell opens the created xls in a new instance or an existing instance.

[rrenis]

Hi Kenneth Hobs - Thanks for your reply! After my last post I started playing with the code and added the following code (from your post - amended for excel)...


On Error Resume Next
Set xlApp = GetObject(, "excel.Application")
If Err.Number <> 0 Then
Set xlApp = CreateObject("excel.Application")
End If
On Error Goto 0

It works great in that if I were to have an instance of excel open it won't create a new one, therefore I could now introduce some terminate process code if CreateObject is used safe in the knowledge that I won't be forcing the shut down of any open spreadsheets. Maybe there's another solution out there but as this seems to work with the little testing I've done so far I'm more than happy!! :bow:

cheers,
rrenis

[Kenneth Hobs]

We like happy. :hi:

You can also use the Count property in the IsEXERunning() function to get a count of Excel running processes. A short function doing this is easy enough. I just threw it in because it can be handy at times.

Please edit your first post and mark this thread solved. You can always post back if you have another question or followup that relates.

cheerio