Avoiding warning messages when saving a file in Excel

[Mike Lewis]

I am using Automation to add passwords to a batch of Excel files. I thought this would be straightforward: just open each workbook in turn, then save it with the password:

SELECT Books && table containing my filenames
SCAN
  loBook = ;
    loExcel.Workbooks.Open(Books.Filename)
  loBook.SaveAs(Books.Filename, , lcPassword)
  loBook.Close(.F.)
ENDSCAN

The problem is the SaveAs method sees that the file already exists, and so prompts for permission to overwrite it. I want to run this code unattendend, so I need to avoid these prompts.

I can't set DisplayAlerts to .F. becuase that would cause Excel to execute the default action, which is not to save the file.

Anyone got any bright ideas about how to avoid this prompt?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

I've found a solution, but it's not ideal: I now save the file to a temporary location, then delete the original file and copy the temp. version back to the original location.

This works OK, but it adds to the overall time of the process. If anyone can suggest a better solution, that would be helpful.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[bborissov]

You explicitly SAVE the file:

loBook.SaveAs(Books.Filename, , lcPassword)

so what is the problem to use DisplayAlerts=.f.?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Mike Lewis]

Borislav,

what is the problem to use DisplayAlerts=.f.

You're right that this would inhibit the prompt, but:

any time a message requires a response, Microsoft Excel chooses the default response.

In the case, the default response is No, which means that the file won't get saved, which is not what I want.

Thanks anyway.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[DSummZZZ]

I don't know of a way to 'save as' without a prompt, but one way to speed things up would be to save the temp, then delete the original and just rename the new one rather than copy.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[bborissov]

Mike,
But you already SAVE the file didn't you?
When you execute SaveAS() the file is saved, so when you execute Close(.f.) and the file is not saved why should you bother? It is saved already.
That works for me:

oexcel = CREATEOBJECT([Excel.Application])
oExcel.Workbooks.Add()
oExcel.ActiveWorkbook.SaveAs([c:\test]) && Just save the blank workbook
oExcel.ActiveWorkbook.Close()
oExcel.Quit()
oExcel = NULL
RELEASE oExcel

***** Let test it back
oexcel = CREATEOBJECT([Excel.Application])
oExcel.Workbooks.Open([c:\test.XLS])
oExcel.Visible = .t.

***** Do some changes in Excel file
***** and then execute the following code
oExcel.DisplayAlerts = .f.
oExcel.ActiveWorkbook.SaveAs([c:\test])
oExcel.ActiveWorkbook.Close()
oExcel.Quit()
oExcel = NULL
RELEASE oExcel

Then open the Excel file and you will see that every changes you made are saved.
Also you could check if the file exists beforehand and issue SAVE() instead of SaveAS() method, but I see no reason to do that.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Mike Lewis]

Borislav,

I appreciate your effort, but I'm not seeing the same result as you.

I agree that your code will close the file without the prompt, but my point is that it won't save the changes. If DisplayAlerts was .T., Excel would display this prompt:

A file named 'c:\test.xls already exists at this location.
Do you want to replace it?

  Yes  [b]No[/b]  Cancel

Making DisplayAlerts .F. will suppress the prompt, but then "Microsoft Excel chooses the default response", which is No. Hence, the changes won't be saved. That's certainly what I am seeing when I run your code.

By the way, I'm not actually making any changes to the file. The reason I need to do SaveAs is to apply a password. But I don't think that affect the point I am making.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Dave,

one way to speed things up would be to save the temp, then delete the original and just rename the new one rather than copy

Good point. In fact, that's what I'm now doing. I'm saving the workbook in the user's Temp folder, then I delete the original, then I rename the temp copy. The new name is the fully-qualified name of the file I just deleted. The effect of that is to move the file from Temp to the correct folder, without physically copying or moving anything.

In fact, it turns out to only add a slight bit of overhead to what is already a lengthy process. It just seems a pity if I can't find a simpler way of doing it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[bborissov]

OK lets try this:

lbSaveAS = .f.
lcFileName = [c:\Test.xls]
oExcel = CREATEOBJECT([Excel.Application])
oExcel.DisplayAlerts = .f.
IF NOT EMPTY(SYS(2000,lcFileName))
   oExcel.WorkBooks.Open(lcFileName)
ELSE
   oExcel.WorkBooks.Add()
   lbSaveAS = .t.
ENDIF
**** Do what you want to do with that file

IF lbSaveAS
  oExcel.WorkBooks.SaveAS(lcFileName)
ELSE
  oExcel.WorkBooks.Save(lcFileName)
ENDIF
oExcel.Quit()

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[white605]

Mike,
look at this page and try this

http://www.mrexcel.com/archive/VBA/6929.html

SET DEFAULT TO c:
cd\
local oExcel, oSheet
oExcel = CreateObject([Excel.Application])
oExcel.Visible = .T.
oExcel.Workbooks.Add()
oSheet = oExcel.ActiveSheet
oSheet.Cells(1,1).Value = [FoxPro Rocks!]
oExcel.displayalerts = .f.
oSheet.SAVEAS("c:\foxprorocks", , "password")
oExcel.quit
RELEASE oexcel,osheet 
*now do it again and change password
local oExcel, oSheet
oExcel = CreateObject([Excel.Application])
oExcel.Visible = .T.
oExcel.Workbooks.Add()
oSheet = oExcel.ActiveSheet
oSheet.Cells(1,1).Value = [FoxPro Rocks!]
oExcel.displayalerts = .f.
oSheet.SAVEAS("c:\foxprorocks", , "newpassword")
oExcel.quit
RELEASE oexcel,osheet

I dont get the prompts

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[Mike Lewis]

William,

Yes, you're right. Your code works, and there is no prompt. It's not quite what I want -- I need to apply the password to an existing file rather than a new one. But I should be able to do that by using oExcel.Workbooks.Open() in place of oExcel.Workbooks.Add().


Borislav,

Your code work as well, but it won't solve the problem. If I understand it right, you are saying that I should do a SaveAs() if it's a new file and a Save()if the file already exists. The problem is that Save() doesn't accept a password. You need to do SaveAs() for that, which is the root of my problem.

By the way, I was interested in your use of SYS(2000). Out of curiosity, does that have any advantage of FILE()?


To all who have replied,

Thanks for all your help. Please don't spend too much time on this, because I know I can solve the problem by creating a temporary file and copying it back to the original. I was worried that that would be slow, but it's negligible compared to how long it takes to do the whole job.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

I'm setting Password on a separate line, not in SaveAs and it works OK for me.

 

[Mike Lewis]

Hi Markros,

What exactly do you mean by "a separate line"? Can you give more info? Thanks.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

IF NOT EMPTY(m.tcPassword)

		loExcel.ActiveWorkbook.PASSWORD = m.tcPassword && Works in Excel 2003
	ENDIF

 

[Mike Lewis]

Markros,

Interesting. But I don't see that property in the object browser or Intellisense. Perhaps it was added in 2003? My clients have 2000.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

Yes, I'm afraid it would not work with 2000, at least my colleague put a comment it works with 2003 only. It would not hurt a quick try, though.

 

[Mike Lewis]

Markros,

I did try it. As expected, it gave an "invalid name" message.

Thanks anyway for the thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business