Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: Excel file with a name keeps opening
Thread ID: 311209 Message ID: 311209 # Views: 60 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Active X Controls & OLE Automation
Date: Wednesday, June 29, 2011 5:28:38 PM         
   


I wrote an EXCEL Automation program that was working fine until I suddenly had Excel opening the file before I even gave it a name. I believe that this is an Excel configuration issue, but don't know enough about Excel to stop this from re-occurring. I am stopped dead in my tracks and cannot test on this laptop, since Excel keeps opening an Excel file with a pre-determined name, something which is not even done in my code.

I'll show my code and where it occurs:


SET EXACT ON

RELEASE ALL LIKE o*
PUBLIC oExcel

CLEAR ALL
USE H:\dev\tmpNearly01 IN 0
USE H:\dev\tmpNearly02 IN 0
USE H:\dev\tmpNearly03 IN 0
USE H:\dev\tmpNearly04 IN 0
USE H:\dev\tmpNearly05 IN 0
USE H:\dev\tmpNearly06 IN 0
USE H:\dev\tmpNearly07 IN 0
USE H:\dev\tmpNearly08 IN 0
USE H:\dev\tmpNearly09 IN 0
USE H:\dev\tmpNearly10 IN 0
USE H:\dev\tmpNearly11 IN 0
USE H:\dev\tmpNearly12 IN 0

* Create the Spreadsheet object.
oExcel = CREATEOBJECT("Excel.Application")
oExcel.Visible = .T.
oExcel.DisplayAlerts = .f.
oWorkBook = oExcel.WorkBooks.Add() <<<<===== THIS IS WHERE THE FILE OPENS WITH A FILE NAME.=======<<<<<<
SET STEP ON
*oWorkBook.SaveAs('H:\BoogerReport.XLS')
oSheet = oWorkBook.ActiveSheet
lnColsOffsetToRight= 0
lnRow = 2 && Start with Column Headings on Row 2.
lnCol = 1 && Start in Column 1.
lnRowOffSet = 0
lcFirstTimeCarrier = 0
LOCAL oRange
* Last Row/Column
*!* lnLastRow = oSheet.UsedRange.Rows.Count
*!* lnLastCol = oSheet.UsedRange.Columns.Count

#DEFINE xlR1C1 -4150
#DEFINE xlLastCell 11

LOCAL lcOldCarr_Name AS Character, lcYear AS Character
lcYear="2010"
lcOldCarr_Name = SPACE(1)
SET STEP ON
lnMonths=12
FOR nMonth = 1 TO lnMonths
* Select the correct Month's cursor (table).
lcMonth=PADL(nMonth, 2, "0")
lcCursor=("tmpNearly"+lcMonth)
SELECT (lcCursor)
SCAN
lcCarr_Name = ALLTRIM(&lcCursor..Carr_Name) && Pick up the new Carrier Name.
oRange = oExcel.ActiveSheet.Range("A3:H3").Offset(lnRowOffset, lnColsOffsetToRight)
* If this is the first time in, lnRow equals row 2.
IF ALLTRIM(lcCarr_Name) != lcOldCarr_Name && Either first time in, or you have a new carrier.
lcFirstTimeCarrier = lcFirstTimeCarrier + 1
IF lcFirstTimeCarrier > 1
lnRowOffSet = 4
lnRow = lnRow + lnRowOffSet
*? oRange.Cells(lnRow, lnCol).Value
ENDIF
*!* IF lcFirstTimeCarrier = 1
*!* lnRowOffSet = 0
*!* lnRow = lnRow + 1 + lnRowOffSet
*!* ENDIF
*lnRow = lnRow + lnRowOffSet
oRange = oExcel.ActiveSheet.Range("A3:H3").Offset(lnRowOffset, lnColsOffsetToRight)
WITH oExcel.ActiveSheet
* Put the name of the first report in A1.
lcAlphaMonth = LEFT(GetAlphaMonth(nMonth),3)
* The following code is only executed once per month's data.
.Cells(lnRow-1,lnCol).Value = "Monthly-" + lcCarr_Name && Carrier Name.
.Cells(lnRow-1,lnCol+3).Value = lcAlphaMonth + " " + lcYear && Month and Year.
* Lay out the Columnn Headings from Left to Right.
.Cells(lnRow, lnCol).Value = "Group Name"
.Cells(lnRow, lnCol + 1).Value = "GrpID"
.Cells(lnRow, lnCol + 2).Value = "Emp. ID"
.Cells(lnRow, lnCol + 3).Value = "Benefit"
.Cells(lnRow, lnCol + 4).Value = "Month_Billed"
.Cells(lnRow, lnCol + 5).Value = "Month_Collected"
.Cells(lnRow, lnCol + 6).Value = "Annualized Premium"
.Cells(lnRow, lnCol + 7).Value = "Lives"
ENDWITH
* Move down a row for the new Carrier Name.
*oRange = oRange.Offset(1,0)
ENDIF
*oRange = oExcel.ActiveSheet.Range("A3:H3").Offset(lnRowOffSet, lnColsOffsetToRight)
*oRange = oExcel.ActiveSheet.Range("A3:H3").Offset(lnRowOffset, lnColsOffsetToRight)
*oRange = oExcel.ActiveSheet.Range("A3:H3").Offset(0, lnColsOffsetToRight)
WITH oRange
* Print the columns/fields to the page. This runs left to right on the default row.
.Columns[1].Value = GrpName && Group Name
.Columns[2].Value = GrpID && Group ID
.Columns[3].Value = Emp_ID && Employer's ID
.Columns[4].Value = Benefit && Benefit Name.
.Columns[5].Value = Billed && Monthly Billed Amount By GrpID, Employer.
.Columns[6].Value = Collected && Monthly Collected amount by GrpID, Employer.
.Columns[7].Value = Annualized && Annualized Premium.
.Columns[8].Value = Lives && # of Primary Insured.
* Move down one row.
oRange = oRange.Offset(1,0)
lnRowOffSet = lnRowOffSet + 2
lnRow = lnRow + lnRowOffSet && Since there is a New Carrier Name (New group), we need to move things down.
ENDWITH
lcOldCarr_Name = ALLTRIM(&lcCursor..Carr_Name) && Pick up the old Carrier Name.
ENDSCAN
* Reset everything. Going to a new month.
lcFirstTimeCarrier = 0
lnColsOffSetToRight=lnColsOffSetToRight + 9 && Reset to 9 columns to right with OffSet.
lnCol = lnColsOffSetToRight + 1 && This leaves a blank column between each month's data.
oRange = oExcel.ActiveSheet.Range("A3:H3").Offset(0, lnColsOffSetToRight) && Reset range to 3rd row and offset to right 9 cols.
lnRow = 2 && Rest row back to oiriginal place for new month's data.
lnRowOffSet = 0 && Reset Row OffSet to 0.
lcOldCarr_Name = SPACE(1) && Reset this MemVar back to blank.
ENDFOR

Cecil Champenois, Jr.

ENTIRE THREAD

Excel file with a name keeps opening Posted by Cecil Champenois @ 6/29/2011 5:28:38 PM
RE: Excel file with a name keeps opening Posted by Vladimir Zhuravlev @ 6/29/2011 7:40:24 PM
RE: Excel file with a name keeps opening Posted by Cecil Champenois @ 6/29/2011 8:50:09 PM
RE: Excel file with a name keeps opening Posted by Jun Tangunan @ 6/30/2011 1:00:03 AM
RE: Excel file with a name keeps opening Posted by Cecil Champenois @ 7/5/2011 4:01:29 PM