Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Process Hanging in Task Manager After Code is Executed 1

Status
Not open for further replies.
BitNet33

BitNet33

Technical User
Feb 2, 2011
21
CA
Hey All,

I have some VBA code in Word that opens Excel and puts values into a bunch of cells on several Worksheets, but only one workbook. Then when my code finishes I find that the Excel process is still running in the task manager. I thought I had used to proper code to close it properly but it doesn't seem to be working and I'm not sure why. My code is like this:
Code: 
Dim xl as Object
Set xl = CreateObject("Excel.Application")

With xl.Workbooks.Add
    Worksheets.Add.name = "Trend"
    With ActiveSheet.name = "Trend"
        'Put some values into cells...
    End With
    WorkSheets.Add.name = "More Trends"
    With ActiveSheet.name = "More Trends"
        'Put some values into cells...
    End With
    'Clean up
    ActiveWorkbooks.SaveAs ("C:\Document...\File.xlsx")
    ActiveWorkbook.Close
    Workbooks.Close
    xl.Quit
    set xl = Nothing
End With
Any help to close Excel properly so that the process doesn't hang would be great.

Thanks,
BitNet
 
Sort by date Sort by votes


hi,

You had SEVERAL syntax errors.

EVERY reference to an Excel object, MUST have an Excel reference.

You cannot ADD and object and supply the Name property value in the same statement.
Code: 
Sub test()
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    
    With xl.Workbooks.Add
        With .WorkSheets.Add
            .Name = "Trend"
            'Put some values into cells...
        End With
        
        With .WorkSheets.Add
            .Name = "More Trends"
            'Put some values into cells...
        End With
        'Clean up
        .SaveAs ("C:\File.xlsx")
        .Close
    End With
    
    xl.Quit
    Set xl = Nothing
End Sub

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Ya I felt like my syntax was a little off. Anyways, I made the changes which you suggested and I still have the problem of the process not ending. Any idea why this is still happening?
 
Upvote 0 Downvote


Go ahead an close EXCEL.EXE in Processes.

Leave the Task Manager open ON TOP.

Step thru the code, noting when Excel opens and closes.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I stepped through and excel opens when I reference my xl Object. Then, as I suspected, it just doesn't close. Are there any other possible syntax errors? Or something component that I'm forgetting to close?
 
Upvote 0 Downvote



The strange thing is that Excel opens and closes when I run the same code.

Are you running the code I posted WITHOUT MODIFICATION?

If not, please post the code that you are running in Word VBA.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
That is strange. And yes I did make the modifications. Here is my complete code:
Code: 
Sub XMLtoExcel()

'Create and load the XML file
Dim xmlDoc As MSXML2.DOMDocument
Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.Load ("C:\Documents and Settings\Desktop\Test XML Files (Copies)\test.xml")

'Create the Excel object
Dim xl As Object
Set xl = CreateObject("Excel.Application")

'initialize variables for grabbing values
Dim beat As MSXML2.IXMLDOMNodeList
Set beat = xmlDoc.getElementsByTagName("HOLTER_ECG/BEAT_LIST/BEAT")

Dim tempNode As MSXML2.IXMLDOMElement

'Create a header string to hold the value returned from the createHeader() function
Dim header As String
header = createHeader1()

'Create the Excel file, create and name the worksheets, and insert the data in
    With xl.Workbooks.Add
        With Worksheets.Add
            .name = "Rest 1"
            ActiveSheet.PageSetup.CenterHeader = header
            'insert the headers
            For i = 0 To 5
                ActiveSheet.Cells(1, i + 1).Value = beat.Item(0).Attributes(i).nodeName
            Next
            'create temporary strings to hold values
            Dim Btype, typeEX, qon, rr, filtered, qt As String   
            'insert the values
            For i = 0 To beat.Length - 1
                Set tempNode = beat.Item(i)
                Btype = tempNode.getAttribute("TYPE")
                typeEX = tempNode.getAttribute("TYPE_EX")
                qon = tempNode.getAttribute("QON")
                rr = tempNode.getAttribute("RR")
                filtered = tempNode.getAttribute("FILTERED_RR")
                qt = tempNode.getAttribute("QT")
                
                ActiveSheet.Cells(2 + i, "A").Value = Btype
                ActiveSheet.Cells(2 + i, "B").Value = typeEX
                ActiveSheet.Cells(2 + i, "C").Value = qon
                ActiveSheet.Cells(2 + i, "D").Value = rr
                ActiveSheet.Cells(2 + i, "E").Value = filtered
                ActiveSheet.Cells(2 + i, "F").Value = qt
            Next
        End With
        'clean up
        .SaveAs ("C:\Documents and Settings\Desktop\test.xlsx")
        .Close
    End With
    
    xl.Quit
    Set xl = Nothing
End Sub
 
Upvote 0 Downvote
oops, I'm still referring to "Activesheet" in this code, could that be the problem?

I'll fix the syntax and try it again and I'll repost my code.

My bad
 
Upvote 0 Downvote
Ok, so I fixed the "Activesheet" references but Excel is still hanging in the Processes...

This is what I fixed my code to:
Code: 
Sub XMLtoExcel()

'Create and load the XML file
Dim xmlDoc As MSXML2.DOMDocument
Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.Load ("C:\Documents and Settings\Desktop\Test XML Files (Copies)\test.xml")

'Create the Excel object
Dim xl As Object
Set xl = CreateObject("Excel.Application")

'initialize variables for grabbing values
Dim beat As MSXML2.IXMLDOMNodeList
Set beat = xmlDoc.getElementsByTagName("HOLTER_ECG/BEAT_LIST/BEAT")

Dim tempNode As MSXML2.IXMLDOMElement

'Create a header string to hold the value returned from the createHeader() function
Dim header As String
header = createHeader1()

'Create the Excel file, create and name the worksheets, and insert the data in
    With xl.Workbooks.Add
        With Worksheets.Add
            .name = "Rest 1"
            .PageSetup.CenterHeader = header
            'insert the headers
            For i = 0 To 5
                .Cells(1, i + 1).Value = beat.Item(0).Attributes(i).nodeName
            Next
            'create temporary strings to hold values
            Dim Btype, typeEX, qon, rr, filtered, qt As String   
            'insert the values
            For i = 0 To beat.Length - 1
                Set tempNode = beat.Item(i)
                Btype = tempNode.getAttribute("TYPE")
                typeEX = tempNode.getAttribute("TYPE_EX")
                qon = tempNode.getAttribute("QON")
                rr = tempNode.getAttribute("RR")
                filtered = tempNode.getAttribute("FILTERED_RR")
                qt = tempNode.getAttribute("QT")
                
                .Cells(2 + i, "A").Value = Btype
                .Cells(2 + i, "B").Value = typeEX
                .Cells(2 + i, "C").Value = qon
                .Cells(2 + i, "D").Value = rr
                .Cells(2 + i, "E").Value = filtered
                .Cells(2 + i, "F").Value = qt
            Next
        End With
        'clean up
        .SaveAs ("C:\Documents and Settings\Desktop\test.xlsx")
        .Close
    End With
    
    xl.Quit
    Set xl = Nothing
End Sub

See any problems?
 
Upvote 0 Downvote

just one
Code: 
'Create the Excel file, create and name the worksheets, and insert the data in
    With xl.Workbooks.Add
        With [b][red].[/red][/b]Worksheets.Add
'...         [b][red]^[/red][/b]
'...         [b][red]|[/red][/b]

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Ah, right. Ok thats fixed. So now my code is a whole lot cleaner (thanks!) but I'm still having the same initial problem...

I don't get it, it should be closing...

Would it maybe be that I'm loading the XML file (xmlDoc) and not "closing it" or something? Would that cause it to hang? I'm just shooting in the dark at this point to try to find the problem.

I tried "Set xmlDoc = Nothing" at the end of my code but that didn't solve it...

Any other ideas? This is frustrating the heck out of me, having to go and close the process after each time I run my code is a big pain.
 
Upvote 0 Downvote


As I stated earlier, when I ran the code Iposted, the Excel appliction unloaded.

I could not reproduce your circumstance from MS Word VBA.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote



I hasten to add that I did NOTHING with regard to any XML code. ONLY creating the Excel objects et al.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Right, so do you think the problem lies in what I'm going with the XML?
 
Upvote 0 Downvote



Comment out that stuff and try it.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hey! I just made some small changes while continuing to work on the program (not trying to fix the process problem) and now I no longer have the process hanging! I'm not exactly sure what fixed it but I imagine it has to do with the formatting and syntax problems I fixed.

Thanks for your help SkipVought! I'm so happy I no longer have this problem!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
367
strongm
strongm
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
938
morechocolate
morechocolate
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
560
mikrom
mikrom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
456
sal21
sal21

Part and Inventory Search

Sponsor

Back
Top