excel-automation

[Nifrabar]

In my app I offer users possibility to export to Excel.
Using automation I give all columns auto-fit width.
Due to this adjustments Excel is asking if I want to keep the changes.
I of course always will.

Would it be possible, prior to have the excel-sheet possible, to save this sheet without any Q.

KR
-Bart

This my code:

	IF LEN(ALLTRIM(thisform.excelselectie))<>0
		lcExcelName = PUTFILE("Export naar Excel","","XLS")
		IF !EMPTY(lcExcelName) 
			lcFields = thisform.ExcelSelectie 
			SET SAFETY off
			EXPORT TO &lcExcelName TYPE XL5 FIELDS &lcFields
			
			oExcel = CreateObject("Excel.Application")
			oExcel.Workbooks.Open(lcExcelName)
			oSheet = oExcel.ActiveSheet
			lnFields =OCCURS(",",lcFields) +1
			FOR j=1 TO lnFields
			     osheet.Columns(j).EntireColumn.Autofit 
			ENDFOR
			oExcel.Visible = .T.
		ENDIF
	ELSE
		=MESSAGEBOX("Geen informatie gevonden om te exporteren naar Excel",0,_screen.caption)			
	ENDIF

 

[foxmuldr2]

Yes. Use:

oExcel.DisplayAlerts = .F.

Best regards,
Rick C. Hodgin

 

[foxmuldr2]

You found this solution yourself back in 2009.

http://www.tek-tips.com/viewthread.cfm?qid=1556295

Best regards,
Rick C. Hodgin

 

[Mike Lewis]

Hi Bart,

This is easy. But you need an object reference to the workbook. So, where you do this:

oExcel.Workbooks.Open(lcExcelName)

Do this instead:

oBook = oExcel.Workbooks.Open(lcExcelName)

Then you can either call the Close method, passing .T. as the first parameter (this will close the workbook, save the changes, and not prompt the user):

oBook.Close(.T.)

Or, simply call the Save method, with no parameters (this will keep the book open):

oBook.Save()

Does that meet your requirements?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[bborissov]

lcExcelName = PUTFILE("Export naar Excel","","XLS")
IF !EMPTY(lcExcelName) 
   lcFields = thisform.ExcelSelectie 
   SET SAFETY off
   EXPORT TO &lcExcelName TYPE XL5 FIELDS &lcFields

  oExcel = CreateObject("Excel.Application")
[COLOR=red]
  oExcel.DisplayAlerts = .f.
[/color]
  oExcel.Workbooks.Open(lcExcelName)
  oSheet = oExcel.ActiveSheet
  lnFields =OCCURS(",",lcFields) +1
  FOR j=1 TO lnFields
       osheet.Columns(j).EntireColumn.Autofit 
  ENDFOR
[COLOR=red]
  oExcel.ActiveWorkbook.Save()
[/color]
  oExcel.Visible = .T.
ENDIF

Borislav Borissov
VFP9 SP2, SQL Server

 

[JRB-Bldr]

Another alternative piece of code you might consider could be:

Instead of:

lnFields =OCCURS(",",lcFields) +1
FOR j=1 TO lnFields
    osheet.Columns(j).EntireColumn.Autofit 
ENDFOR

You could do:

xlSheet = oExcel.activesheet
xlSheet.COLUMNS("A:AA").EntireColumn.AutoFit

Where, in this example, your last column would be 'AA'.
If your farthest right column needs to be something else, use that Character string instead.
Or you could programatically determine the last column with:

mnLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT

and then convert the numerical value to its Excel column character representation.

Good Luck,
JRB-Bldr

 

[Nifrabar]

wow!
That much solutions in such a short time.

I found the solution of bborissov most easy to use and go that way.

All thanks again for your help!

@Rick; this fits exactly with a remark I here recently made in the forum. over 55+ in age does no good for your memory ;-)

-Bart