Stop Excel file from opening 1

[keepingbusy]

Hi

How do I stop the created excel file from opening:

USE MYFILE
STORE "completed" 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.Visible = .T.

If I remove the oXL.Visible = .T. the file is still open in the background, I have tried this by double clicking on it in Windows Explorer which tells me it is alreay open.

I've also tried to remove oWorkbook = oXL.Workbooks.Open(mFileOpen) but then I get file not found.

I just want the file to be created in the background and I'll show a =messagebox when completed.

VFP9

Many thanks
Lee

 

[Mike Lewis]

Lee,

It's not clear what you are trying to achieve.

You say you "just want the file to be created in the background". But you also say "If I remove the oXL.Visible = .T. the file is still open in the background".

So, which do you want: to create it, or not to create it?

In general, to create a new file, you use oXL.Workbooks.Add(). That will create and open the file. If you want to do it in the background (i.e. not make it visible), remove the oXL.Visible = .T.

If this doesn't answer your question, perhaps you could clarify the problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Hi Mike

Good to hear from you.

I simply want to create it without it opening at the end of the process.

So I'm guessing the coding you suggested:

oXL.Workbooks.Add()

must be the answer.

So I made an amendment:

STORE SYS(5)+SYS(2003)+"\"+TRIM(mfile)+".xls" TO mfileopen
oXL = CREATEOBJECT("Excel.Application")
oWorkbook = oXL.Workbooks.Add(mFileOpen)

*oWorkbook = oXL.Workbooks.Open(mFileOpen)

oRange = oWorkbook.Sheets[1].Rows[1]
oRange.Delete()

*oXL.Visible = .T.

=MESSAGEBOX("File "+SYS(5)+SYS(2003)+"\"+TRIM(mfile)+ ;
  ".xls has been created"+space(10),0+64+0,"System Message")

This appeared to work but when you open the Excel file after the process is complete (either via Windows Explorer or Excel itself), the first line is NOT deleted which I need it to do.

Any suggestions would be appreciated Mike
Thank you
Lee

 

[TamarGranor]

When you're done processing, close the file:

oWorkbook.Save()
oWorkbook.Close()

Tamar

 

[keepingbusy]

Hi Tamar

Thank you

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()
oWorkbook.Save()
oWorkbook.Close()

The above now creates the Excel file, deletes the first row and saves it etc.

Worthy of a star Tamar!

Even the easiest things in life can seem so difficult (when you don't know how).

Thanks again to you both (Mike and Tamar).

Lee

 

[RickBorup]

Lee,

If all you want to do is create a file with a given name and an "xls" extension, you don't need to use Excel at all. Given the time it takes to instantiate the Excel appliation object, it would be faster just to use low level file functions to create and then close the file, like this:

lnFileHandle = FCREATE( SYS(5)+SYS(2003)+"\"+TRIM(mfile)+".xls")
FCLOSE( lnFileHandle)

-Rick

 

[keepingbusy]

Rick

If all you want to do is create a file with a given name and an "xls" extension, you don't need to use Excel at all. Given the time it takes to instantiate the Excel appliation object, it would be faster just to use low level file functions to create and then close the file, like this:

Good point but how would your sollution delete the first row in the Excel spreadsheet which is requirement in this scenario?

Lee

 

[RickBorup]

Good point but how would your sollution delete the first row in the Excel spreadsheet which is requirement in this scenario?

It doesn't, but what is the point of deleting the first row of a newly created worksheet? There is no data in those cells, and even after deleting the row you still have 65,536 rows in the worksheet. Of course, if deleting the first row is a requirement for reasons I don't understand then of course you'll need to use Excel automation, as Mike's and Tamar's solutions show.

-Rick

 

[keepingbusy]

Rick

When the Excel spreadsheet is created automatically, for some reason, the column headers in the spreadhseet (e.g. A, B, C, D, E etc...) show in the first line, hence the reason I needed to delete it.

Lee

 

[Mike Lewis]

Lee,

When the Excel spreadsheet is created automatically, for some reason, the column headers in the spreadhseet (e.g. A, B, C, D, E etc...) show in the first line,

Rick's solution creates a completely empty spreadsheet. It doesn't place anything in the first row.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Mike

Rick's solution creates a completely empty spreadsheet. It doesn't place anything in the first row.

Sorry if I am getting the wrong end of the stick here, but are you saying it's a completely empty spreadsheet with Rick's suggestion? If yes, then you have lost me on this one!

Lee

 

[white605]

Lee ,
I was wondering where the Letters are coming from, Is it possible that you are using a template that puts them there every time you create a document?

By default, Excel does not use a particular file to store defaults. However, you can force Excel to start up with a customized 'Default Template' each time Excel starts and each time you open a new workbook.

wjwjr

 

[RickBorup]

Lee,

Sorry if I am getting the wrong end of the stick here, but are you saying it's a completely empty spreadsheet with Rick's suggestion? If yes, then you have lost me on this one!

I may have misunderstood your original post, but I thought what you were trying to accomplish was to create an empty spreadsheet with a specific name in a specific location. The low-level commands I posted (FCREATE and FCLOSE) create a 0-byte file with a .XLS file name extension. When opened in Excel, Excel displays an empty worksheet. When you save the file from Excel, it becomes an actual Excel workbook file. My apologies if this is not what you needed.

-Rick

 

[keepingbusy]

Rick

My apologies if this is not what you needed.

No need to apologise, I find all posts very helpful if not now, maybe the future.

Just to clear this up, the initial question was related to how I could just create an excel spreadsheet having been populated with data from a table without it actaully opening.

Sometime ago, another Tek-Tips user, gave advice on how to create the XLS file, then open it so the user could view it.

We were then able to overcome the issue of deleting the first line in the XLS file which for some unknown reason always shows the column headers.

I needed to take this one step further, as once the file had been created, there was no actual need to open it but I had to ensure that the first row (as above) was in fact deleted.

White605

I was wondering where the Letters are coming from, Is it possible that you are using a template that puts them there every time you create a document?

No. As the code shows, its a straight forward xls file with no added extras. If you open Excel on its own, the column headers are not shown in row 1.

Thank you all for your posts on this thread, and for the advice on the issue which has been resolved.

Lee

 

[JRB-Bldr]

There still seems to be some on-going confusion as to what you wanted to do. Lets try to end that.

how I could just create an excel spreadsheet having been populated with data from a table without it actually opening

The only way to create an Excel spreadsheet using data from a FP/VFP data table without opening the file is to do a simple

SELECT MyDBF
COPY TO NewXLS.xls xl5

That will create an Excel file with your FP/VFP data and not actually open Excel to do so.
Other approaches, which are good if not even better, will require Excel and the file to be opened via Automation.

which for some unknown reason always shows the column headers

It is not for some "unknown reason", instead it is the result that you get when you use the simplistic
"COPY TO <whatever> XL5"
Using this command line approach, FP/VFP will put the field names into the new XLS file as Row #1.

once the file had been created, there was no actual need to open it

In order to Delete the first row from an existing Excel file, you HAD to OPEN Excel and the file via Automation - that is a given.

mfileopen = SYS(5)+SYS(2003)+"\"+TRIM(mfile)+".xls"
* ------
oXL = CREATEOBJECT("Excel.Application") [B]<-- OPENS Excel[/B]
oWorkbook = oXL.Workbooks.Open(mFileOpen) [B]<-- OPENS File Into Excel [/B]
oRange = oWorkbook.Sheets[1].Rows[1]
oRange.Delete()
oWorkbook.Save() [B]<-- Saves Excel File[/B]
oWorkbook.Close() [B]<-- Closes Excel[/B]

It seems as though instead of your goal as stated, what you really wanted was not to leave the file open when done. And that you have accomplished with Tamar's code from above.

It might have been simpler and more expedient to merely ask about how to get your desired end result and have left out the "how to get there" part.

Regardless, you got your answers despite some confusion as to what you really wanted to accomplish - good.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Lee,

Sorry if I am getting the wrong end of the stick here, but are you saying it's a completely empty spreadsheet with Rick's suggestion?

Exactly. As Rick said, his code will create a zero-length file. That has to mean that it is completely empty. Try it and see.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk