[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA - code is not closing excel and vb editor

Posted on 2009-12-30
17
Medium Priority
?
640 Views
Last Modified: 2012-08-13
Hello Experts,

I created macro that takes runs from File 1, opens File 2 as an input file, and creates an output - File 3.

After the code is finished running, I am trying to close all windows including Excel itself.

With what I've coded so far, everything is closing except the Visual Basic Editor and Excel application itself stay open.    In the VB Editor, I can see FUNCRES.XLAM (Analysis Tool pack add-in).   See attached screenshot.

ICan someone advise on what I need to do in order for my program to close everything and an explanation why the codes not working as expected.  

If further clarification is needed, please let me know.    Thanks.

LCha
'Open GL File
Set SourceWb = Workbooks.Open("c:\input.xlsx")


'ADDED THESE 2 LINES OF CODE TO MAKE EXCEL SHUT DOWN
Dim xlApp As Object
Set xlApp = GetObject(, "excel.application")

..
...
...
  'Close the active workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
'ADDED THIS CODE TO MAKE EXCEL SHUTDOWN 
   xlApp.Quit
    Set xlApp = Nothing
    
    'exit this instance of Excel
    Application.Quit

Open in new window

FUNCRES.png
0
Comment
Question by:lcha
  • 9
  • 7
17 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 26149856
What about Sourcewb ? it is still active try
Sourcewb.close true (or false which ever you want to save)
set Sourcewb = nothing

gowflow
0
 
LVL 4

Expert Comment

by:cd_morris
ID: 26150903
Gowflow, you have two choice  - First remove this:
  'Close the active workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
 'ADDED THIS CODE TO MAKE EXCEL SHUTDOWN  
   xlApp.Quit
    Set xlApp = Nothing
     'exit this instance of Excel
    Application.Quit

And replace with this in the same module

    Application.DisplayAlerts = False
    Application.Quit


Or you can put this in the This workbook module under Microsoft Excel Objects

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Application.Quit
End Sub
0
 

Author Comment

by:lcha
ID: 26168171
apology for the delay in response.  I posted something previously or at least thought I did but notice now that it didn't appear on the thread.

Right above line 12 in the above code snipped I close SourceWB as follows - I omitted a lot of the other code because I thought it was irrelevant.

    'Close the Source workbook
    SourceWb.Close
     
    'Save the Summary worksheet to a csv file
    Sumwksht.SaveAs Filename:="C:\Summary.csv", FileFormat:=xlCSVWindows

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 31

Expert Comment

by:gowflow
ID: 26171234
SO basically what I suggested at first?
goflow
0
 

Author Comment

by:lcha
ID: 26207958
yes
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26208376
ok fine. You need any further help pls do not hesitate to ask.
rgds and have a great year 2010
gowflow
0
 

Author Comment

by:lcha
ID: 26319526
thanks for the feedback.   Have a great year as well!    

BTW, I don't see any solution to this so I am going to request to close the issue.   It's no longer also an issue for me.   Thanks again for your help.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26320875
Pls can you state the problem so we can see if we can help ? Did my recommendation help you solve it ?
rgds/gowflow
0
 

Author Comment

by:lcha
ID: 26326102
the problem is still the same as stated in the initial question.  Your earlier response didn't help solve it because you had suggested something that was already in my code.    
I
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26331721
ok fine can you post the file so I can troubleshoot ? it is much easier than guessing on a small portion of code.
rgds/gowflow
0
 

Author Comment

by:lcha
ID: 26344989
Here's the full code. Let me know if you have any questions.    Thanks!
Sub GLparse()

Dim pos As Integer
Dim WS_Count As Integer
Dim objSourceWS As Object
Dim rowval As Integer
Dim rowcount As Integer
Dim Sourcerow As Integer
Dim summary_count As Integer
Dim intposition As Integer
Dim colpos As Integer
Dim fourdigit As String
Dim dash_plus2 As String
Dim firstfour As String
Dim wksht As Worksheet
Dim Sumwksht As Worksheet
Dim wb As Workbook
Dim ItemRefRow
Dim ArrayCount As Integer
Dim objSummary As Object
Dim SourceWb As Workbook
Dim ws As Worksheet

Dim GLArray() As Double
Dim GLStrArray() As String


'The start of counter for populating array
Sourcerow = 0

'for counting the total number of rows in the worksheet
rowcount = 0

'counter for populating the summary output file starting at row 2 to populate header columns
summary_count = 2

Dim strDir As String
strDir = ActiveWorkbook.Path


Dim xlApp As Object


Set xlApp = GetObject(, "excel.application")
Set wb = ActiveWorkbook

'Open GL File
Set SourceWb = Workbooks.Open(strDir & "\input.xlsx")

'Copy worksheets from GL File to output Excel file
Dim i As Integer
i = 1
For Each ws In SourceWb.Worksheets

  If ws.Visible <> False Then
    ws.Copy Before:=ThisWorkbook.Sheets(i)
    i = i + 1
  End If
    
Next ws

'Delete default worksheets, turn off alert before deletion and then turn it back on after sheets are deleted
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Worksheets("Sheet2").Delete
Worksheets("Sheet3").Delete
Application.DisplayAlerts = True

'Add the Summary worksheet to end of workbook
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary"
        
'Activate the Summary Worksheet
Set Sumwksht = ActiveWorkbook.Worksheets("Summary")
Sumwksht.Activate
             
'Populate column names on Summary worksheet
Sumwksht.Cells(summary_count, 2).Value = "Name"
Sumwksht.Cells(summary_count, 3).Value = "Debit"
Sumwksht.Cells(summary_count, 5).Value = "Credit"
Sumwksht.Cells(summary_count, 6).Value = "Company"
Sumwksht.Cells(summary_count, 7).Value = "ISIS Name"
Sumwksht.Cells(summary_count, 8).Value = "Debits and Credits"
         
summary_count = summary_count + 1

Dim iSheetCount As Integer
Dim iSheet As Integer

iSheetCount = ActiveWorkbook.Worksheets.Count

For iSheet = 1 To iSheetCount
    Set wksht = Worksheets(iSheet)
    wksht.Activate
   
'Loop through all worksheets
'For Each wksht In Worksheets

    ItemRefRow = 3
    rowcount = 0
    ArrayCount = 0
        
    If wksht.Name <> "Summary" Then
    
        'Count the number of rows that have values in them
        Do Until wksht.Cells(rowcount + 3, 2).Value = ""
            rowcount = rowcount + 1
        Loop

         
        'Dimension the Array
        ReDim GLStrArray(rowcount, 4)
        ReDim GLArray(rowcount, 2)
    
        strwkshtname = wksht.Name
                   
        'Populate Array with values from GL
        Do Until ArrayCount = rowcount + 1
    
            'Populate the GL Category, Debit, Credit, Worksheet name column values in the array for each row in the spreadsheet
            
            'GL Category
            GLStrArray(ArrayCount, 0) = ActiveWorkbook.Worksheets(strwkshtname).Cells(ItemRefRow, 2).Value
            'Debit
            If Not IsNumeric(ActiveWorkbook.Worksheets(strwkshtname).Cells(ItemRefRow, 3).Value) Then
                GLArray(ArrayCount, 0) = 0
            Else
                GLArray(ArrayCount, 0) = ActiveWorkbook.Worksheets(strwkshtname).Cells(ItemRefRow, 3).Value
            End If
        
            'Credit
            GLArray(ArrayCount, 1) = ActiveWorkbook.Worksheets(strwkshtname).Cells(ItemRefRow, 5).Value
            'Worksheet Name
            GLStrArray(ArrayCount, 1) = strwkshtname
                     
            'Populate array for all rows other than the summary row
            If Not GLStrArray(ArrayCount, 0) = "" Then
            
                GLString = GLStrArray(ArrayCount, 0)
        
                'Search backwards for colon
                colpos = InStrRev(GLString, ":")
        
                'Check for colon and if it's found perform logic to populate array
                If colpos <> 0 Then
        
                    'return section of the string of 4 characters starting at position returned from backward search
                    fourdigit = Mid(GLString, colpos + 1, 4)
                
                    strISISrow = fourdigit & " - " & strwkshtname
            
                    'Check to see if there is a dash immediately following the account number
                    'If there is a dash include the dash and the next two characters for the ISIS row name
                    If Mid(GLString, colpos + 5, 1) = "-" Then
                        dash_plus2 = Mid(GLString, colpos + 5, 3)
                        strISISrow = fourdigit & dash_plus2 & " - " & strwkshtname
                    End If
                    
            
                'If a colon is not found in the backwards search
                Else
                'Check to see if the first four characters are numeric, if yes then ISIS row will be worksheetname - acct num e.g. PSC - 1065
                    firstfour = Mid(GLString, 1, 4)
                    
                    If IsNumeric(firstfour) Then
                        strISISrow = firstfour & " - " & strwkshtname
            
                    'string is not numeric, then ISIS row will be worksheetname - value in the row e.g. PSC Mexico Land Development
                    Else
                        strISISrow = strwkshtname & " " & GLString
                    End If
        
                End If
                
                'populate array with the ISIS Row determined by logic above
                GLStrArray(ArrayCount, 2) = strISISrow
                
            
                ArrayCount = ArrayCount + 1
                
                'keep incrementing row counter for the spreadsheet until it is equal to the array count plus 2
                If ItemRefRow <= rowcount + 2 Then
                    ItemRefRow = ItemRefRow + 1
                Else
                    Exit Do
                End If
            
        'Populate array with values for trial balance row
        Else
            GLStrArray(ArrayCount, 1) = strwkshtname
            GLStrArray(ArrayCount, 2) = strwkshtname & " - Total Trial Balance"
            ArrayCount = ArrayCount + 1
        
        End If
        
        Loop
    
        'Loop through array and populate the Summary worksheet with rows that do not have a 0 value in credit or debit
         For Sourcerow = 0 To UBound(GLArray)
        
            If GLArray(Sourcerow, 0) <> 0 Or GLArray(Sourcerow, 1) <> 0 Then
    
                Sumwksht.Cells(summary_count, 2).Value = GLStrArray(Sourcerow, 0)
                Sumwksht.Cells(summary_count, 3).Value = GLArray(Sourcerow, 0)
                Sumwksht.Cells(summary_count, 5).Value = GLArray(Sourcerow, 1)
                Sumwksht.Cells(summary_count, 6).Value = GLStrArray(Sourcerow, 1)
                Sumwksht.Cells(summary_count, 7).Value = GLStrArray(Sourcerow, 2)
                
                Dim debtcred As Double
                
                
                If (IsEmpty(GLStrArray(Sourcerow, 0)) = False) And (IsEmpty(GLStrArray(Sourcerow, 1)) = False) Then
                    debtcred = GLArray(Sourcerow, 0) - GLArray(Sourcerow, 1)
                
                ElseIf GLStrArray(Sourcerow, 0) <> "" Or IsEmpty(GLStrArray(Sourcerow, 0)) = True Then
                    debtcred = GLArray(Sourcerow, 0) - 0
                Else
                    debtcred = 0 - GLArray(Sourcerow, 1)
                End If
                
                               
                Sumwksht.Cells(summary_count, 8).Value = debtcred
                
                summary_count = summary_count + 1
                
            End If
        
         Next
         
         End If
         
    'Next wksht
    Next iSheet
    
    'Close the Source workbook
    SourceWb.Close
      
    'Save the Summary worksheet to a csv file
    Sumwksht.SaveAs Filename:=strDir & "\Summary.csv", FileFormat:=xlCSVWindows
    
    'Close the active workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    xlApp.Quit
    Set xlApp = Nothing
    
    'exit this instance of Excel
    Application.Quit
    
End Sub

Open in new window

0
 
LVL 31

Accepted Solution

by:
gowflow earned 1000 total points
ID: 26347490
Fine pls you will need to do this:

REPLACE THIS
=============================================
   'Close the Source workbook
    SourceWb.Close
     
    'Save the Summary worksheet to a csv file
    Sumwksht.SaveAs Filename:=strDir & "\Summary.csv", FileFormat:=xlCSVWindows
   
    'Close the active workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
   
    xlApp.Quit
    Set xlApp = Nothing
   
    'exit this instance of Excel
    Application.Quit
=================================

WITH THIS
=================================
   'Save the Summary worksheet to a csv file
    Sumwksht.SaveAs Filename:=strDir & "\Summary.csv", FileFormat:=xlCSVWindows

    'Close the Source workbook
    SourceWb.Close

    'Close the active workbook
    Application.DisplayAlerts = False
   
    'exit this instance of Excel
    Application.Quit
==================================================

Pls let me know if it does what you want.
rgds/gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26390948
Did you hv a chance to try the suggested code ?
rgds/gowflow
0
 

Author Comment

by:lcha
ID: 26395177
sorry for the delay!   I will try asap and keep you posted, thanks for checking up!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26407113
ok take your time. Do not hesitate to let me know of any help needed
rgds/gowflow
0
 

Author Comment

by:lcha
ID: 26410133
that seems to have fixed the issue, thank you gowflow!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26411977
Welcome. Sorry it took sometime !
rgds/gowflow
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question