Excel Automation Question 1

[JonusQ]

Hi.

I am doing some Excel automation through VFP. I have a place where I need to delete "left over" worksheets from the Excel file that I am creating, but when I do it stops with the following message:

The selected worksheets will be permanently deleted.
To deleted the selected sheets, click ok
to cancel the deletion, click cancel

I know this message comes up while deleting a sheet in Excel, but is there a way to supress the message in a VFP automation program?

Any help is appreciated.

Thanks.
Rt

 

[JRB-Bldr]

Here is a portion of the code that I am using...

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION

* --- Set Excel to only have one worksheet ---
oExcel.SheetsInNewWorkbook = 1

* --- Delete the Default Workbook that has 3 worksheets ---
oExcel.Workbooks.CLOSE

* --- Now Add a new book with only 1 worksheet ---
oExcel.Workbooks.ADD
xlBook = oExcel.ActiveWorkbook.FULLNAME
xlSheet = oExcel.activesheet

* --- Name Worksheet ---
xlSheet.NAME = "Sheet Name"

* --- Make Excel Worksheet Visible To User ---
oExcel.VISIBLE = .T. && Set .F. if you want to print only

<do whatever>

oExcel.WINDOWS(xlBook).ACTIVATE
xlSheet.RANGE([A2]).SELECT

* --- Save Excel Results ---
oExcel.CutCopyMode = .F. && Clear the clipboard from previous Excel Paste
oExcel.DisplayAlerts = .F.

* --- Save Results ---
xlSheet.SAVEAS(mcExclFName)

* --- Close the Worksheet ---
oExcel.workbooks.CLOSE

* --- Quit Excel ---
oExcel.QUIT
RELEASE oExcel

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[jimoo]

JRB. Good work and worth a star.

I have a basic Excel Automation FAQ. Maybe you can add one near mine, or if you don't have time I can add your code at the bottom and credit you. That way we keep it handy for newbees.

faq184-4428 Excel - How to do Automation from VFP

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[JRB-Bldr]

jimoo - feel free to add whatever you want of it to your FAQ. It is good to have it in a single, common location.

Here are a few more...

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION
oExcel.ReferenceStyle = 1 && Ensure Columns in A-B Format instead of 1-2 Format

mcStrtColRow = 'A1'
mcEndColRow = 'AB5'
mcLastCol = 'AZ:'

* --- Time Masquerading As Text Format Cells ---
xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "h:mm:ss"

* --- Standard Text Format Cells ---
xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "@"

* --- Date Format Cells ---
xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "mm/dd/yyyy"

* --- Auto-Fit All Columns ---
xlSheet.COLUMNS("A:" + mcLastCol).EntireColumn.AutoFit

Note - None of this is anything original on my part. I merely 'harvested' it from a variety of places on the web and maybe 'tweaked' some of it to fit my needs.



JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[jimoo]

The new commands have been added to the bottom of

FAQ184-4428 Excel - How to do Automation from VFP

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[JonusQ]

Thank you both for your help. I was able to do what I needed thanks to you.

Rt