Using the SaveAs in Excel not working with Ruby

ruby-talk
1

Hi;
I need to save an Excel Spreadsheet with Ruby. I used the Save AND
SaveAs methods, but each time I get prompted "Do you want to save the
changes you made..." How can I save the workbook without this prompt.
In addition, is there away to add a timestamp to the save file
like.....application12122006.xls or any format that contains a time
stamp?

2

anon1m0us@yahoo.com wrote:

How can I save the workbook without this prompt.

Where xl is your Excel Application object and wb is your Workbook
object...

xl.DisplayAlerts = 0
wb.SaveAs(xlsname)
xl.DisplayAlerts = 1

In addition, is there away to add a timestamp to the save file
like.....application12122006.xls or any format that contains a time
stamp?

Something like this...

xlsname = 'application' + Time.now.strftime("%m%d%y") + '.xls'

Mully

3

anon1m0us@yahoo.com wrote:

Hi;
I need to save an Excel Spreadsheet with Ruby. I used the Save AND
SaveAs methods, but each time I get prompted "Do you want to save the
changes you made..." How can I save the workbook without this prompt.
In addition, is there away to add a timestamp to the save file
like.....application12122006.xls or any format that contains a time
stamp?

Hey

I think you can use

book.close

passing either 1 or 0 (I can't remember which)
Also, I can't remember if you need to call SaveAs first.

This should work:

book = #get workbook
book.SaveAs "SomeFile.xls"
book.close(0)

Hope this helps
Gustav Paul

4

Thanks...that did the trick!

mully wrote:

···

anon1m0us@yahoo.com wrote:
> How can I save the workbook without this prompt.

Where xl is your Excel Application object and wb is your Workbook
object...

xl.DisplayAlerts = 0
wb.SaveAs(xlsname)
xl.DisplayAlerts = 1

> In addition, is there away to add a timestamp to the save file
> like.....application12122006.xls or any format that contains a time
> stamp?

Something like this...

xlsname = 'application' + Time.now.strftime("%m%d%y") + '.xls'

Mully

5

I am guessing the (0) in close(0) refers to the number of the book that is open, which could mean that if you had a book open and you open a new book, then this will fail.

try close("SomeFile.xls") and let me know if that works. I know from the VBA that you could refer to the book by using the filename as the reference.
the all open workbooks lie in a collection hence the need for a reference.

it's been a long time since I have done any VBA, which is a good thing!
ivor

Gustav Paul wrote:

···

anon1m0us@yahoo.com wrote:
  

Hi;
I need to save an Excel Spreadsheet with Ruby. I used the Save AND
SaveAs methods, but each time I get prompted "Do you want to save the
changes you made..." How can I save the workbook without this prompt.
In addition, is there away to add a timestamp to the save file
like.....application12122006.xls or any format that contains a time
stamp?

Hey

I think you can use

book.close

passing either 1 or 0 (I can't remember which)
Also, I can't remember if you need to call SaveAs first.

This should work:

book = #get workbook
book.SaveAs "SomeFile.xls"
book.close(0)

Hope this helps
Gustav Paul

6

The (0) tells it to not save changes, letting you bypass the dialog that
asks you to do so.

The object browser says:

Sub Close([SaveChanges], [Filename], [RouteWorkbook])

So I'm assuming 0 = false and 1 = true. Not sure about the [RouteWorkbook].

VBA isn't so bad when you can do it in Ruby! :wink:

Nate

···

On 12/12/06, Ivor <ivor@rails.co.za> wrote:

I am guessing the (0) in close(0) refers to the number of the book that
is open, which could mean that if you had a book open and you open a new
book, then this will fail.

try close("SomeFile.xls") and let me know if that works. I know from the
VBA that you could refer to the book by using the filename as the
reference.
the all open workbooks lie in a collection hence the need for a reference.

it's been a long time since I have done any VBA, which is a good thing!
ivor

Gustav Paul wrote:
> anon1m0us@yahoo.com wrote:
>
>> Hi;
>> I need to save an Excel Spreadsheet with Ruby. I used the Save AND
>> SaveAs methods, but each time I get prompted "Do you want to save the
>> changes you made..." How can I save the workbook without this prompt.
>> In addition, is there away to add a timestamp to the save file
>> like.....application12122006.xls or any format that contains a time
>> stamp?
>>
> Hey
>
> I think you can use
>
> book.close
>
> passing either 1 or 0 (I can't remember which)
> Also, I can't remember if you need to call SaveAs first.
>
> This should work:
>
> book = #get workbook
> book.SaveAs "SomeFile.xls"
> book.close(0)
>
> Hope this helps
> Gustav Paul
>

7

Ok, this code provides the date..
xlsname = 'application' + Time.now.strftime("%m%d%y") + '.xls'
If i want to add a time stamp to? is it ("%m%d%y%h%mm%s")???

Ivor wrote:

···

I am guessing the (0) in close(0) refers to the number of the book that
is open, which could mean that if you had a book open and you open a new
book, then this will fail.

try close("SomeFile.xls") and let me know if that works. I know from the
VBA that you could refer to the book by using the filename as the reference.
the all open workbooks lie in a collection hence the need for a reference.

it's been a long time since I have done any VBA, which is a good thing!
ivor

Gustav Paul wrote:
> anon1m0us@yahoo.com wrote:
>
>> Hi;
>> I need to save an Excel Spreadsheet with Ruby. I used the Save AND
>> SaveAs methods, but each time I get prompted "Do you want to save the
>> changes you made..." How can I save the workbook without this prompt.
>> In addition, is there away to add a timestamp to the save file
>> like.....application12122006.xls or any format that contains a time
>> stamp?
>>
>>
>>
>>
>>
> Hey
>
> I think you can use
>
> book.close
>
> passing either 1 or 0 (I can't remember which)
> Also, I can't remember if you need to call SaveAs first.
>
> This should work:
>
> book = #get workbook
> book.SaveAs "SomeFile.xls"
> book.close(0)
>
> Hope this helps
> Gustav Paul
>
>
>
>