how to save az excel book?

[kenguru]

Hi,

I would like to do the following: create a new excel file write codes in it and after that save it with a given name but without being asked to saved it.
I wrote the following code, but at the line xlbook.save() it gives me an error that this method is not an option.
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add

xlSheet.Cells(1, 1).Value = lbnr.Caption
....

'Save the Worksheet.
'xlSheet.SaveAs ("C:\" & lbnr.Caption)

xlBook.Saved = True
xlBook.Save ("C:\" & lbnr.Caption)

' Close the Workbook
xlBook.Close
' Close Microsoft Excel with the Quit method.
xlApp.Quit

' Release the objects.
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

Please responde if you have any suggestion.
Thank's
Kenguru

 

[johnwm]

It's SaveAS that you're looking for, not Save!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[kenguru]

Hi!
Yes I tried "Save" but if there is that file already than VB asks me if I want to replace the already existing file. And there is three answers: Yes, No, Cancel. The first two answer is working properly, BUT is the user chooses "cancel" the program gives back an error: "Run time error 1004 The method SaveAs failed".
How can i rezolve this problem?

Thank's
Kenguru

 

[jjames]

To overwrite without the prompt...

xlApp.AlertBeforeOverwriting = False

 

[kenguru]

Thank's but there is a problem the following one:
the xlsheet doesn't have the property "AlertBeforeOverwriting"

and

if i use it as you have written the application promps me for a file name, even if i used the following code: xlApp.SaveWorkspace "c:\" & lbnr.Caption. And if i responde "cancel" an error occures.

Can you help me?
Kenguru

 

[johnwm]

I think you're looking for DisplayAlerts property of the Application object:

Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs ("C:\" & lbnr.Caption)
  Application.DisplayAlerts = True

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[kenguru]

Yes I tried this one to, but the following is the problem: this code you wrote has the meaning for visual basic NOT to save the changes made at az excel file
Kenguru

 

[johnwm]

The code as shown will save the file in its current state, including any changes, but will not display a warning if an existing file with the same name is to be overwritten. You will, of course, need to be sure that lbnr.Caption contains a legal filename string!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[Bhavna13]

have you tried this when u have these statements
"
' Close the Workbook
xlBook.Close
' Close Microsoft Excel with the Quit method.
xlApp.Quit
"

try
"
xlBook.close true,"filename.xls"
xlApp.Quit
"

where filename.xls is the full path and filename ..
eg. "c:\report\myReport.xls"

this usually works for me.