Deleting first row in excel (2007) spreadsheet - Problem

[keepingbusy]

Hi

In 2007 I posted a thread (thread184-1403060) which at the time, resovled my issue. Recently I used the below code for another project with similar requirements but for whatever reason is not deleting the first row in the spreadsheet created which contains the field names.

STORE "MyReport" TO mfile
EXPORT TO &mfile TYPE XLS
STORE SYS(5)+SYS(2003)+"\"+TRIM(mfile)+".xls" TO mfileopen
oxl = CREATEOBJECT("Excel.Application")
OWORKBOOK = oxl.WORKBOOKS.OPEN(mfileopen)
ORANGE = OWORKBOOK.SHEETS(1).ROWS(1)
ORANGE.DELETE()
oxl.QUIT
RELEASE oxl

My goal here is to remove row 1 from the spreadsheet so the field names do not appear.

Here is the strange thing, when the file opens automatically, the fields are not shown in the spreadsheet. When you the process is complete, if you open the file in the folder where it has been created, the field names are shown in that file.

Has something changed since the introduction of Excel 2007 or am I missing something?

I'm using VFP9 and Office 2007.

Thanks guys

Lee

 

[Dan Freeman]

Quite a lot changed with Excel 2007, among them an entirely new file format with the extension XSLX. Are you sure you're opening the file you think you're opening?

Sounds like you're opening a file that doesn't exist, which is why you're not seeing the field names, and later opening the "real" file which still has them.

 

[Mike Lewis]

Lee,

I might be wrong about this, but I can't see where you're saving the workbook. As far as I can see, you are correctly deleting the row, but then quitting without a save (unless the Quit does a save anyway?).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

Dan
Yes. I knew about the different file formats (docx etc) but I don't think this is the issue.

Mike
How are you? The workbook is being saved automatically for the client so they can view it, move the columns if required etc for cosmetic reasons.

The quit doesn't do an automatic save as this option comes up in Excel (which I would expect it to do anyway).

I'm sure something has changed somewhere that is stopping the orignal code from removing the field names.

Lee

 

[Dan Freeman]

Looks like you'll have to set oXL.Visible=.t. and step through it to see what's really happening in there.

 

[keepingbusy]

Ok. I added the additional line as suggested by danfreeman:

oxl = CREATEOBJECT("Excel.Application")

[b]oXL.Visible=.t.[/b]

OWORKBOOK = oxl.WORKBOOKS.OPEN(mfileopen)

The Excel file opened showing the field names as described. About two seconds later the field names dissapeared.

When I went to close the spreadsheet I was offered the option to save it, which I did (without the field names).

When I checked the file in the folder it is saved in, the field names were still in the file.

However, what I have now discovered is that there are two files being created, one XLS (Older version that has the field names) and a xlsx (Newer version without the field names).

So how do I force the file into becoming an older version without the field names?

Lee

 

[Mike Lewis]

Lee, perhaps you can explicitly call the workbook's SaveAs method, passing the filename in the first parameter and the format in the second parameter.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Hi Lee,

The old format xls file comes from the EXPORT TO &mfile TYPE XLS line, the xlsx extension is from the default file format excel 2007 saves. So it seems the quit does autosave, but not to the xls file, it creates a new xslx file instead. So don't rely on the autosave feature.

You need to programmatically save before quit and specify the file format. Excel 8 is the oldest format supported. And it's not really the original Excel 8 format. Excel 2007 lists it as Excel97-2003 compatibility format.

SaveAs prompts a yes/no question you can only prevent, when saving under a new filename, so I did that and finally renamed to the original name:

#Define xlExcel8 56
STORE SYS(5)+SYS(2003)+"\"MyReport.xls" TO mfile
EXPORT TO (mfile) TYPE XLS
oxl = CREATEOBJECT("Excel.Application")
OWORKBOOK = oxl.WORKBOOKS.OPEN(mfile)
ORANGE = OWORKBOOK.SHEETS(1).ROWS(1)
ORANGE.DELETE()
* SaveAs with new name
OWORKBOOK.SaveAs(mfile+"temp",xlExcel8)
ERASE (mfile)
* Rename to the original name
RENAME (mfile+"temp") TO (mfile)
oxl.QUIT
RELEASE oxl

Bye, Olaf.

 

[Mike Lewis]

Lee,

Another possibility:

1. Copy the DBF to a comma-delimited text file (COPY TO ... TYPE DELIMITED ...).

2. Rename the text file to a CSV extension.

3. Programmatically open that in Excel, and save it to any Excel format.

The point is that the text file won't have the field names (but be sure to use DELIMITED not TYPE CSV).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

Olaf

I have an error message with your suggested code:

OLE IDispatch exception code 0 from Microsoft Office Excel: Cannot access 'MyReport.xlstemp'

Mike

Good suggestion but the person using this application requires a completed Excel file instead of "Save as..." etc. Whilst doing this is easy for your or I, in some other circumstances this may be difficult if you get my understanding.

Lee

 

[Olaf Doschke]

Try to put in some wait code, excel needs to complete saving, before the file can be renamed of course. It worked for me on a small xls with cusomters of northwind sample code.

Eg you can

...
Erase (mfile)
Do While File(mfile+"temp")
   Try
      Doevents Force
      Rename (mfile+"temp") To (mfile)
   Catch
   Endtry
Enddo

Bye, Olaf.

 

[Mike Lewis]

Lee,

Good suggestion but the person using this application requires a completed Excel file instead of "Save as..." etc.

Not sure I understand that.

What I was suggesting would have resulted in a complete Excel file. The opening and Save As would be invisible to the user.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

Sorry for the delay in posting back.

Olaf
Thank you for your post which made me take a second look at this.

Mike
What I was trying to achieve was a completed xls file without the option to save as... however

I made a slight amendment shown below which as you suggested, which allows the user the option to "Save as" so with this in mind, if you use Excel versions 97-2003 the default will always be .xls and in the case of later versions .xlsx

To some extent, the "Save as" was a better option as the user decided they wanted to save the file in a location of thier choice as an alternative to a specified folder (which is what they originally asked for!)

Issue has been resolved, thanks again for the inspiration guys.

STORE "MyReport" TO mfile
EXPORT TO &mfile TYPE XLS

STORE SYS(5)+SYS(2003)+"\"+TRIM(mfile)+".xls" TO mfileopen
oxl = CREATEOBJECT("Excel.Application")

OWORKBOOK = oxl.WORKBOOKS.OPEN(mfileopen)
ORANGE = OWORKBOOK.SHEETS(1).ROWS(1)
ORANGE.DELETE()

* Show file and give user the option to Save as...

***************
oXL.Visible=.t.
***************

oxl.QUIT
RELEASE oxl

Lee

 

[Mike Lewis]

Lee,

Glad to hear it's resolved.

But I come back to my previous point: I never suggested that the user should save the workbook. Rather, the SaveAs would happen behind the scenes.

But since you've got a satisfactory solution, that's fine.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

Thanks Mike