BBC BASIC
« COMLIB Excel Close method »

Welcome Guest. Please Login or Register.
Jan 20th, 2018, 4:22pm


Cross-platform BBC BASIC (Win32, Linux x86, Android, Mac OS-X, Raspberry Pi)

« Previous Topic | Next Topic »
Pages: 1  Notify Send Topic Print
 thread  Author  Topic: COMLIB Excel Close method  (Read 390 times)
Edja
New Member
Image


member is offline

Avatar




PM


Posts: 22
xx COMLIB Excel Close method
« Thread started on: Apr 20th, 2017, 4:15pm »

I have been working for a while with COMLIB to interface with Excel. Until now I've only been working on xlsx files already present on disc. And all was well !
Two days ago I've made some changes to my code to also work with new xlsx files (not pre-existing). I've made the following working code to illustrate how I went about :
Code:
      INSTALL @lib$+"COMLIB"
      FileName$=@dir$+"Experiment.xlsx"
      PRINT FileName$
      PROC_cominit
      Xlapp% = FN_createobject("Excel.Application")
      PROC_putvalue(Xlapp%, "Visible(BTRUE)")
      Xlbook% = FN_getobject(Xlapp%, "Workbooks.Add(-4167)")
      PROC_putvalue(Xlbook%,"WorkSheets(1).Name(""My First Sheet"")")

      FOR I%=1 TO 10
        FOR J%=1 TO 15
          A$=CHR$(63+J%+I%)
          PROC_putvalue(Xlbook%,"ActiveSheet.cells(I%,J%).value("""+A$+""")")
        NEXT J%
      NEXT I%

      PROC_callmethod(Xlbook%,"Close(B TRUE,"""+FileName$+""",)")
      PROC_callmethod(Xlapp%,"Quit")
      PROC_cleanup
      PROC_comexit
      END

      DEF PROC_cleanup
      Xlapp% += 0  : IF Xlapp%  PROC_releaseobject(Xlapp%)  : Xlapp% = 0
      Xlbook% += 0 : IF Xlbook% PROC_releaseobject(Xlbook%) : Xlbook% = 0
      ENDPROC
 

It was my understanding that the line
Code:
  PROC_callmethod(Xlbook%,"Close(B TRUE,"""+FileName$+""",)") 
would save the file in the location @dir$+"Experiment.xlsx" which it does IF there is not already a file with that name present and that it would replace a file with that name if present, which it doesn't.
I thought the parameter B TRUE in the Close-method would unconditionally replace a pre-existing file. Instead Excel stays open and first asks me the OK to overwrite the existing file.
Only then the workbook is closed and the Quit-method executes to remove Excel from the screen.
I suppose I'm using the wrong parameters but after consulting the WIKI and the MSDN pages I still can't find out how to do this correctly.

How can I unconditionally replace the pre-existing file and Quit Excel without user intervention?

User IP Logged

Richard Russell
Administrator
ImageImageImageImageImage


member is offline

Avatar




Homepage PM


Posts: 689
xx Re: COMLIB Excel Close method
« Reply #1 on: Apr 20th, 2017, 5:30pm »

on Apr 20th, 2017, 4:15pm, Edja wrote:
I thought the parameter B TRUE in the Close-method would unconditionally replace a pre-existing file.

I don't see anything in the MSDN description to suggest that. It seems to say only that the Boolean determines whether any changes are saved or not. If they are, and the file exists, then you will be prompted.

Google suggests that you need to execute the Workbook.SaveAs method first (remember that you don't necessarily need to specify all the parameters, if the defaults are acceptable). Even then you will probably get an overwrite prompt unless XLapp.DisplayAlerts is set to FALSE.

Can't you simply delete the existing file before closing the workbook and eliminate the overwrite prompt that way?

Richard.
« Last Edit: Apr 20th, 2017, 6:56pm by Richard Russell » User IP Logged

Edja
New Member
Image


member is offline

Avatar




PM


Posts: 22
xx Re: COMLIB Excel Close method
« Reply #2 on: Apr 20th, 2017, 8:43pm »

Quote:
Even then you will probably get an overwrite prompt unless XLapp.DisplayAlerts is set to FALSE

The lines
Code:
PROC_callmethod(Xlbook%,"SaveAs("""+FileName$+""")")
PROC_callmethod(Xlbook%,"Close(B FALSE,,)") 

instead of
Code:
 PROC_callmethod(Xlbook%,"Close(B TRUE,"""+FileName$+""",)") 

have the same result. But adding
Code:
PROC_putvalue(Xlapp%, "DisplayAlerts(BFALSE)") 

immediatly after the creation of the application object does the trick. In both cases the existing file is overwritten and no overwrite prompt shows up. Just to illustrate, here is the complete example code again with corrections
Code:
      INSTALL @lib$+"COMLIB"
      FileName$=@dir$+"Experiment.xlsx"
      PRINT FileName$
      PROC_cominit
      Xlapp% = FN_createobject("Excel.Application")
      PROC_putvalue(Xlapp%, "DisplayAlerts(BFALSE)")
      PROC_putvalue(Xlapp%, "Visible(BTRUE)")
      Xlbook% = FN_getobject(Xlapp%, "Workbooks.Add(-4167)")
      PROC_putvalue(Xlbook%,"WorkSheets(1).Name(""My First Sheet"")")

      FOR I%=1 TO 10
        FOR J%=1 TO 20
          A$=CHR$(63+J%+I%)
          PROC_putvalue(Xlbook%,"ActiveSheet.cells(I%,J%).value("""+A$+""")")
        NEXT J%
      NEXT I%
      PROC_callmethod(Xlbook%,"SaveAs("""+FileName$+""")")
      PROC_callmethod(Xlbook%,"Close(B FALSE)")
      PROC_callmethod(Xlapp%,"Quit")
      PROC_cleanup
      PROC_comexit
      END
     
      DEF PROC_cleanup
      Xlapp% += 0  : IF Xlapp%  PROC_releaseobject(Xlapp%)  : Xlapp% = 0
      Xlbook% += 0 : IF Xlbook% PROC_releaseobject(Xlbook%) : Xlbook% = 0
      ENDPROC 

Quote:
Can't you simply delete the existing file before closing the workbook and eliminate the overwrite prompt that way?

Yes, and that was plan B. But in the spirit to gradually come to grips with the COMLIB's potential I wanted to understand/use the available Excel methods. Babysteps !!

Thanks for the help !!
Eddy
User IP Logged

Richard Russell
Administrator
ImageImageImageImageImage


member is offline

Avatar




Homepage PM


Posts: 689
xx Re: COMLIB Excel Close method
« Reply #3 on: Apr 20th, 2017, 9:20pm »

on Apr 20th, 2017, 8:43pm, Edja wrote:
In both cases the existing file is overwritten and no overwrite prompt shows up.

That's interesting, because it is not what MSDN says happens. Setting DisplayAlerts to FALSE is supposed to cause Close not to save the file but SaveAs to save the file. See both the link I gave last time and here where it states "Changes to Book1.xls are not saved":

Code:
Application.DisplayAlerts = False 
Workbooks("BOOK1.XLS").Close 
Application.DisplayAlerts = True 

Perhaps supplying the optional filename to Close changes this behaviour, but I've not seen that stated anywhere. Either way, the code you listed (which includes SaveAs) should be guaranteed to save the file.

Richard.
« Last Edit: Apr 20th, 2017, 9:23pm by Richard Russell » User IP Logged

Edja
New Member
Image


member is offline

Avatar




PM


Posts: 22
xx Re: COMLIB Excel Close method
« Reply #4 on: Apr 21st, 2017, 10:07am »

Quote:
That's interesting, because it is not what MSDN says happens. Setting DisplayAlerts to FALSE is supposed to cause Close not to save the file but SaveAs to save the file

Just to be sure, I've just double checked the version with the line
Code:
PROC_callmethod(Xlbook%,"Close(B TRUE,"""+FileName$+""",)") 

and I can confirm my previous post : with DisplayAlerts set to FALSE the file is closed and saved, overwriting an already present version and without showing an overwrite prompt showing up.

Nevertheless, having consulted the link that you've included and taking into account your comment
Quote:
the code you listed (which includes SaveAs) should be guaranteed to save the file

I'll stick to use SaveAs to stay in line with the officially documented rules.

Eddy

User IP Logged

Richard Russell
Administrator
ImageImageImageImageImage


member is offline

Avatar




Homepage PM


Posts: 689
xx Re: COMLIB Excel Close method
« Reply #5 on: Apr 21st, 2017, 3:54pm »

on Apr 21st, 2017, 10:07am, Edja wrote:
I'll stick to use SaveAs to stay in line with the officially documented rules.

I expect specifying the optional filename in the Close method causes it to call SaveAs 'behind the scenes', but I think you are wise not to rely on it.

Richard.
User IP Logged

Pages: 1  Notify Send Topic Print
« Previous Topic | Next Topic »

Donate $6.99 for 50,000 Ad-Free Pageviews!


This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls