Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Excel VBA macro - how to close the excel application

    What is the code/syntax to close excel application in excel macro programming? thanks.

    this is my code:
    application.activeworkbook.close
    application.close

    it only closes the workbook but the excel application remains.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    .quit if I remember correctly
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    i used .quit
    still encountering the same problem.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    tested and works.

    Please post all code. Please describe exactly what you observe. Upload if you like.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    119
    hi, i sent you the codes.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    I would post your code here.

    In any event, I think your problem is that Quit works fine, but you instantiate another Excel application (xlsAp).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2005
    Posts
    119
    I modified my code and removed the portion of saving (Save As) to another excel file. Now the problem is gone, the application quits properly now.

    I simplified my code and opted to just copy the records from one source file to a destination file. The excel macro file is just the middle man. Now, got another problem though, I can't save the destination file after copying the records.

    Here's my code:

    Dim xlsAppSource As Excel.Application
    Dim xlsAppDest As Excel.Application

    Dim fs

    Dim rowDestSheet1 As Integer
    Dim rowDestSheet2 As Integer

    Dim cntFiles As Integer

    Sub Consolidate()

    cntFiles = 1
    rowDestSheet1 = 5
    rowDestSheet2 = 5

    Set xlsAppDest = New Excel.Application
    xlsAppDest.Workbooks.Open ActiveWorkbook.Path & "\" & "Report-CONSOLIDATED.xls"

    While cntFiles < 8
    CopyRecords ("Report" & cntFiles & ".xls")
    Wend

    'xlsAppDest.Workbooks(1).Saved = True
    xlsAppDest.Workbooks("Report-CONSOLIDATED").Saved = True
    xlsAppDest.Workbooks.Close

    End Sub

    Private Sub CopyRecords(strSourceFile As String)
    'Copy records from source to destination
    Dim rowSource As Integer
    Dim colSource As Integer

    Set xlsAppSource = New Excel.Application

    xlsAppSource.Workbooks.Open ActiveWorkbook.Path & "\" & strSourceFile

    '--Sheet1
    rowSource = 2
    col = 1
    While xlsAppSource.Sheets("Sheet2").Cells(rowSource, 1).Value <> ""
    For col = 1 To 8
    xlsAppDest.Sheets("Sheet1").Cells(rowDestSheet1, col).Value = xlsAppSource.Sheets("Sheet2").Cells(rowSource, col + 1).Value
    Next
    rowSource = rowSource + 1
    rowDestSheet1 = rowDestSheet1 + 1
    Wend

    '--Sheet2
    rowSource = 2
    col = 1
    While xlsAppSource.Sheets("Sheet3").Cells(rowSource, 1).Value <> ""
    For col = 1 To 8
    xlsAppDest.Sheets("Sheet2").Cells(rowDestSheet2, col).Value = xlsAppSource.Sheets("Sheet3").Cells(rowSource, col + 1).Value
    Next
    rowSource = rowSource + 1
    rowDestSheet2 = rowDestSheet2 + 1
    Wend

    xlsAppSource.Workbooks.Close

    End Sub


  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    xlsAppDest.Workbooks(1).Save
    or
    xlsAppDest.Workbooks(1).SaveAs newName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Posts
    119
    Thanks a lot. =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •