Workbooks.Close

sugargenius

New Member
Joined
Sep 16, 2009
Messages
40
I have a little vfp app that merges data from several excel workbooks and does some formatting. For some reason, I can't close the workbooks programmatically. I've tried the following


loCAMNotebook.Close(.t.)

loExcelApp.Workbooks.Close

FOR EACH oWkBk IN loExcelApp.Workbooks
oWkBk.Close(.f.)
NEXT oWkBk

lnWBCount = loExcelApp.Workbooks.Count
FOR n = lnWBCount TO 1 STEP -1
loExcelApp.Workbooks(n).Close
ENDFOR
wbclose.jpg
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
Where's the code for opening/merging etc?
 

sugargenius

New Member
Joined
Sep 16, 2009
Messages
40
Where's the code for opening/merging etc?

Here's where I instantiate Excel app:
Code:
*--create an Excel instance
loExcelApp = CreateExcelApp()

IF ISNULL(loExcelApp) OR TYPE( 'loExcelApp' ) # 'O'
    BRSERROR("Error instantiating Excel application")
    RETURN .F.
ENDIF

*-- set excel app properties
WITH loExcelApp
    .DisplayAlerts = .F.
    .Visible = .F.
    .ScreenUpdating = .F.
    .EnableEvents = .F.
ENDWITH


FUNC CreateExcelApp
LOCAL loExcelApp, llErr, loErr as Exception
    
    llErr = .f.
    TRY
        loExcelApp = CREATEOBJECT( 'Excel.Application' )
    CATCH TO loErr
        llErr = .t.
        AddLogLine( "Could not create instance of Excel application." + CRLF +;
                "Error: " + TRANSFORM( loerr.ErrorNo) + CRLF +;
                "Description: " + loerr.Message )
    ENDTRY
    
    IF llErr 
        BRSERROR( "Error creating Excel object reference." + CRLF +;
            "Make sure Excel is properly installed and re-run" )
        RETURN .f.
    ENDIF

RETURN loExcelApp

Here's where I create the output file from a template:
Code:
*--create the cam notebook
loCAMNotebook = loExcelApp.Workbooks.Add( goApp.cCurrentPath + RMS_WORKBOOK_TEMPLATE )

*-- make sure we got a new workbook
IF ISNULL( loCAMNotebook ) OR TYPE( 'loCAMNotebook' ) # 'O'
    BRSERROR("Error creating cam notebook")
    RETURN .F.
ENDIF

*--wbl changed 6-21-05
*--seperate cobra batch report dir from output dir                
*--now try to save it.  it may fail if open already, so TRY...CATCH
lcCAMNotebookFileName = lcProgramOutputDir + "CAM_" + lcCAM + "_" + lcProgramName +;
    "_" + RMS_NOTEBOOK_NAME + "_" +  lcStatusDate + ".xls"

llFileSaveError = .f.
TRY                
    loCAMNotebook.SaveAs( lcCAMNotebookFileName , xlWorkbookNormal )
CATCH TO loErr
    llFileSaveError = .t.
    AddLogLine( "Couldn't save CAM notebook " + lcCAMNotebookFileName + CRLF +;
        "Error: " + TRANSFORM( loerr.ErrorNo) + CRLF +;
        "Description: " + loerr.Message )            
    BRSERROR( "Error saving CAM notebook " + lcCAMNotebookFileName + CRLF +;
        "Make certain the file isn't open." )
ENDTRY
IF llFileSaveError
    IF TYPE( "loExcelApp" ) = "O" AND !ISNULL( loExcelApp )
        FOR EACH oWkBk IN loExcelApp.Workbooks
            oWkBk.Close(.f.)
        NEXT oWkBk 
        loExcelApp.Quit()
    ENDIF
    RELEASE loCAMNotebook, loExcelApp
    RETURN .f.
ENDIF

Here's where I open one of the source reports and copy some data to the output file
Code:
lcBaseReportFileName = lcCobraProgramReportsDir + CPR5CA_REPORT_NAME

IF !FILE( lcBaseReportFileName )
    BRSERROR( "Couldn't locate report " + lcBaseReportFileName  )
    RETURN .F.
ENDIF

llOpenSourceBookErr = .F.
TRY
    *--open the base report
    loSourceReportBook = loExcelApp.Workbooks.Open( lcBaseReportFileName )
CATCH TO loErr
    llOpenSourceBookErr = .t.
    AddLogLine( "Couldn't open report " + lcBaseReportFileName + CRLF +;
        "Error: " + TRANSFORM( loErr.ErrorNo) + CRLF +;
        "Description: " + loErr.Message )
ENDTRY

*-- make sure we didn't get an error opening the source workbook
IF llOpenSourceBookErr 
    BRSERROR( "Could open report " + lcBaseReportFileName )
    RETURN .f.
ENDIF

llOpenSourceSheetErr = .F.
TRY
    *--open the sheet
    loSourceReportSheet = loSourceReportBook.Sheets( COBRA_REPORTS_SHEETNAME )
CATCH TO loErr
    llOpenSourceSheetErr = .t.
    AddLogLine( "Couldn't locate Reports sheet in " + lcBaseReportFileName + CRLF +;
        "Error: " + TRANSFORM( loErr.ErrorNo) + CRLF +;
        "Description: " + loErr.Message )
ENDTRY

*-- make sure we didn't get an error opening the Reports sheet
IF llOpenSourceSheetErr
    BRSERROR(  "Couldn't locate Reports sheet in " + lcBaseReportFileName )
    RETURN .f.
ENDIF

*--add a sheet to the cams notebooks for the cpr5
loManagerNotebookCpr5Sheet = loManagerNotebook.Sheets.Add()
loManagerNotebookCpr5Sheet.Name = CPR5CA_SHEETNAME

*--loop through the cursor of accounts for this CAM/PEM
IF !USED( lcMgrTableName )
    RETURN
ENDIF

lnLastRow = loSourceReportSheet.Cells(65536, 1).End(xlUp).Row
loSrchRng = loSourceReportSheet.Range( "A1:A" + Num2TStr( lnLastRow ) )
lnPasteCount = 0

SELECT ( lcMgrTableName )    
SCAN
    loFoundRng = .F.
    lcAccount = RTRIM( CA1 ) + SPACE(1) + RTRIM( CA2 ) + SPACE(1) + RTRIM( DESCRIP )
    lnFoundRow = 0
    
    *--search col A for this account        
    loFoundRng = loSrchRng.Find( lcAccount, , xlValues, xlPart, xlByColumns, xlNext, .f.  )
    
    *--make sure a match was found, if not then add a log entry and loop to the next account
    IF ISNULL( loFoundRng )
        AddLogLine( "Account " + lcAccount + " not found in " + lcBaseReportFileName )
        LOOP
    ENDIF

    lnFoundRow = loFoundRng.Row
    
    *--build range spec in "RC:RS" notation like "A1:J30"
    lcThisAccountRng = "A" + Num2TStr( lnFoundRow - 2 ) + ":" + CPR5_LAST_COL_STRING + Num2TStr( lnFoundRow + 20 )
    
    *--copy this account from the source report
    loSourceReportSheet.Range( lcThisAccountRng ).Copy
    
    *--paste into cam notebook
    *--all the data in cobra reports are formulas, so have to paste value then paste formats
    lcPasteDestRng = "A" + Num2TStr( CPR5_ROWS_PER_ACCOUNT * lnPasteCount + ( lnPasteCount + 1 ) )
    loManagerNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial( xlPasteValues )
    loManagerNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial( xlPasteFormats )
    llCamNotebookDirtyFlag = .t.
    lnPasteCount = lnPasteCount + 1
ENDSCAN

loSourceReportBook.Close(.f.)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
Which workbook/workbooks are you trying to close using the code from post #1?

I'm asking because you appear to be closing the source workbook once you've copied the relevant data to the output workbook.
 

sugargenius

New Member
Joined
Sep 16, 2009
Messages
40

ADVERTISEMENT

Which workbook/workbooks are you trying to close using the code from post #1?

I'm asking because you appear to be closing the source workbook once you've copied the relevant data to the output workbook.
The source workbooks get closed in the individual procedures where they are opened. That appears to be working fine. It's the output workbooks that won't close.


After opening, copy/paste, closing several other source workbooks, it runs a macro that does the formatting. The macro is in a module in the template that the target workbook was created from:

Code:
*double quotes in filenames, so excel app.run() will work
lcCAMNotebookFileName = loCAMNotebook.Name
lcCAMNotebookFileName = STRTRAN( lcCAMNotebookFileName, "'", "''" )
lcMacroName = ['] + lcCAMNotebookFileName + ['] + "!" + EXCEL_MACRO_NAME        
        
*--run the Excel formatting macros
llExcelMacroErr = .f.
TRY
    loExcelApp.Run( lcMacroName, loXLParams )
CATCH TO loErr
    llExcelMacroErr = .t.
    AddLogLine( "Error running Excel formatting macro." + CRLF +;
            "Error: " + TRANSFORM( loerr.ErrorNo) + CRLF +;
            "Description: " + loerr.Message )
ENDTRY
IF llExcelMacroErr 
    BRSERROR( "Error running Excel formatting macro." + CRLF +;
        "Check log and contact administrator." )

    IF TYPE( "loExcelApp" ) = "O" AND !ISNULL( loExcelApp )
        FOR EACH oWkBk IN loExcelApp.Workbooks
            oWkBk.Close(.f.)
        NEXT oWkBk 
        loExcelApp.Quit()
    ENDIF
    RELEASE loCAMNotebook, loExcelApp
    RETURN .f.
ENDIF

After the formatting macro runs, I finally attempt to save/close the output workbook:
Code:
*--save and close the workbook
loCAMNotebook.Close(.t.)

No errors are thrown, but the workbook is still open.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
Is Excel visible when you run the code?

If it is are you receiving any alerts/error messages from Excel?
 

sugargenius

New Member
Joined
Sep 16, 2009
Messages
40
Is Excel visible when you run the code?

If it is are you receiving any alerts/error messages from Excel?

That's a good point. I'll re-enable application.displayalerts and application.visible and maybe something will show up.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,364
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top