save and close Excel2010

[Jose-phil]

The following code first saves an Excel spreadsheet, then closes the excel program :

Code: Select all

; Microsoft Excel - Book1.xlsx
;

SetTitleMatchMode 2
WinActivate, ahk_class XLMAIN

send ^s

sleep 1000

send !{F4}
return

[Mod edit: Replaced i- and b-tags with code tags. Please use them yourself when posting code!]

I would like a version that saves a spreadsheet without closing Excel, but nothing in the code gives me a clue as to what needs modifying.
Actually, I want it to save the SS to its default location then also to a USB stick.

[boiler]

I would like a version that saves a spreadsheet without closing Excel, but nothing in the code gives me a clue as to what needs modifying.

That's what sending !{F4} (Alt+F4) does.

Actually, I want it to save the SS to its default location then also to a USB stick.

The easiest and most effective way to do that if you know the source file path would be to use FileCopy.

[Jose-phil]

So the Autohotkey code for saving the open spreadsheet is !{F4} (Alt+F4)
What is the code for the next step - calling Filecopy (and instructing it to save to another location)

Is there in Autohotkey language an equivalent of REM-in-batch-file - if so what is it ?

[ShatterCoder]

Might be easier at this point just to do what you are requesting via COM:

Code: Select all

;This macro is launched by pressing ctrl + alt + s
^!s::
xl := ComObjActive("Excel.Application")
;xl.DisplayAlerts := False ;Uncomment this line to disable alerts (you will not see a message box asking if you want to save over an existing file)
OrigPath := xl.activeworkbook.fullname
SheetName := xl.activeworkbook.Name
USB_Path := "D:\Subfolder\" SheetName
xl.activeworkbook.saveas(OrigPath)
xl.activeworkbook.saveas(USB_Path)
xl.DisplayAlerts := True

You would of course need to edit the 7th line to reflect the proper path to your USB drive and where on it you want to save the document. Note: If you choose to uncomment line 4 it will automatically save over the original file without prompting the user and will happen silently in the background.
Also be aware that the above script works by saving the "Active" workbook meaning if you have more than one excel sheet open, it will act on which ever was most recently active/'In use'.

[boiler]

So the Autohotkey code for saving the open spreadsheet is !{F4} (Alt+F4)

No, that’s the general Windows shortcut for closing an application.

What is the code for the next step - calling Filecopy (and instructing it to save to another location)

See the documentation at the link provided.

Is there in Autohotkey language an equivalent of REM-in-batch-file - if so what is it ?

You mean to add a remark/comment in an AHK script? See Comments.

[flyingDman]

Try:

Code: Select all

xl := ComObjActive("excel.application")
xl.activeWorkbook.SaveCopyAs("E:\" xl.activeWorkbook.name)  ; E:\ or whatever path
xl.activeWorkbook.close(1)									; close(1) saves under current path\name then closes 

[Jose-phil]

Boiler
So semicolon in Autohotkey is the equivalent of REM in batch files (....?)

I had edited my post to remove the 'copy to USB' part, since I already have that figured out 'my own' way'.
and that I tried the "!{F4} (Alt+F4)" code and it did not achieve SAVING.
The edit did not take in the thread.

That just leaves the question :
What code will save an open SS (to its default location) ?
Apparently there was a misunderstanding about
!{F4} (Alt+F4)
and closing Excel is what I do NOT want to happen;
I apologise for the TITLE of my post suggesting that need
but I meant that the code I present was supposed to do that

flyingDman's suggestion is so much simpler and therefore prefereable
but I am not sure what it is supposed to achieve,
since the reference to 'close' makes me think there is another misunderstanding.
I am dumbfounded by "Select all - Download - Toggle Line numbers"
Download ????? My request has nothing to do with internet !
Moreover SAVECOPYAS seems to require the name of the SS
whereas the code in my original post acted on whatever SS was open
and I would like that feature to be reproduced in my new code.

ShatterCoder
"do what you are requesting via COM:"
NO idea what COM is - are we still using Autohotkeys ?
"This macro is launched by pressing ctrl + alt + s"
I do NOT want a keystroke solution - that requires remembering them.
I want to be able to click on an AHK to achieve my desired result.
I think it incorporates saving to a USB stick - which was no longer in my request, but would come in handy.
I have tried your code and it does NOT save an open SS by double-clicking the AHK.
Pressing 'ctrl + alt + s' triggers a lengthy error code, which I can reproduce if you ask for it.
Repeatedly pressing 'ctrl + alt + s' keeps triggering the same or very similar error message - eventually that stops.

I apologise for not entering the code correctly - I understand WHAT is wrong, but despite the instructions, do not know how to fix it.

[RussF]

So, if we are to understand you correctly,

Copying to a USB drive is no longer a priority because you are doing it 'your own way'.

All you really want to do is save an open Excel spreadsheet - and double-clicking a desktop icon is easier to you than clicking File->Save in Excel or pressing Ctrl-S (which is the standard "save" command in 99% of all windows applications), because it's too hard to remember.

Am I correct?

Then:

Code: Select all

; Microsoft Excel - Book1.xlsx

SetTitleMatchMode 2
WinActivate, ahk_class XLMAIN

send ^s
return

Russ

[boiler]

So semicolon in Autohotkey is the equivalent of REM in batch files (....?)

Is it not clear from the documentation?

and that I tried the "!{F4} (Alt+F4)" code and it did not achieve SAVING.

I said it’s the shortcut for closing the application, not for saving. Not sure why you would expect it to save.

What code will save an open SS (to its default location) ?

I don’t know what opening a spreadsheet to a location means. You open it from whatever location it is in. Files don’t open to a location.

Apparently there was a misunderstanding about
!{F4} (Alt+F4)
and closing Excel is what I do NOT want to happen;
I apologise for the TITLE of my post suggesting that need

That was what was in the code you posted, and I explained what it was for because you asked. No one equated that to saving except you.

I am dumbfounded by "Select all - Download - Toggle Line numbers"
Download ????? My request has nothing to do with internet !

Calm down. That isn’t part of the code. Those are features of the code box that display the code in the forum, not the code itself.

I’m going to bow out of the thread and let others handle it from here. Good luck.

[Jose-phil]

Boiler
You have bowed out, but I will reply anyway.

"Is it not clear from the documentation?" It may well be, but I had not consulted that when I asked the forum.

"I don’t know what opening a spreadsheet to a location means." neither do I. The intention of the statement was SAVE TO A LOCATION.

RussF
"if we are to understand you correctly, Copying to a USB drive is no longer a priority because you are doing it 'your own way'.
You do understand correctly, but doing it within an AHK may well be more 'elegant';
I still NEED to know how to save, but not NEED to be told how to get a copy on a USB stick.

"double-clicking a desktop icon is easier to you than...." since I intend to combine it with the second step - saving to a USB - that is indeed true.

"because it's too hard to remember." I was referring to another key-combination that had been suggested (CTRL ALT S or something). In any case I prefer to click a shortcut rather than do ANY keystroke.

I will now try your code, and edit this post to state the results - if it succeeds, then I will try to incorporate the earlier suggestion of saving to USB through Autohotkey.

[Jose-phil]

I can't find an EDIT option, so here is a new post

RussF
That code does in deed do what I want - AND responds to a mouse click.
Thanks a million.

Now to see if I can incorporate the 'save to USB' code ....
***********************************************************************************
It seems too daunting, so although I don't NEED to do it within Autohotkeys,
I would really appreciate it if you could give me the entire code
that includes saving to a USB (NOT to a folder on it) as well.

Interestingly, this time I could find the EDIT option for this post - previously I could not.

[RussF]

Now to see if I can incorporate the 'save to USB' code ....

We simply need to incorporate @flyingDman's code above without the line that closes Excel.

Code: Select all

#Requires AutoHotkey v1.1.33+

; Microsoft Excel - Book1.xlsx

SetTitleMatchMode 2
WinActivate, ahk_class XLMAIN

send ^s
sleep 1000	; adjust as necessary

xl := ComObjActive("excel.application")
xl.activeWorkbook.SaveCopyAs("E:\" xl.activeWorkbook.name)  ; E:\ or whatever path
return

Note: I added the "sleep" statement because Excel can't save to two places at the same time, so the script has to wait until the first save is finished before commencing the second one. If you get the following error:

image.png (13.4 KiB) Viewed 1470 times

it means that your system is slower than mine, so just increase the value of the sleep. (1000 is 1000 milliseconds, or 1 second)

Note: If your flash drive uses a different letter than "E:", you will need to change the target location in the second-to-last line. Just change "E:\" to "F:\" or whatever the correct drive letter is. You can also add a path such as "F:\MyExcelFiles\". Be sure to leave the trailing \ character in the path.

Note: Not all the code fits in the code window above, so you have to scroll down within the code window to see all of it. That is also what the "Select all" link is for - clicking it will select all the code in the code box and you can then copy and paste it into your editor.

Russ

[flyingDman]

When dealing with Excel simulated keystrokes are unnecessary and unwanted. xl.close(1) saves and closes no need for ^s. There is also no need for any sleeps. There is also no need for WinActivate, ahk_class XLMAIN since we establish a unique link to excel. The 0x80010001 error pops up when you try to establish a link with Excel but Excel is still in edit mode. Use Excel_get() if that remains an issue.

[RussF]

@flyingDman I don't disagree with you at all. While I am nowhere near your level of expertise with COM and may struggle coming up with the command structure I need, I CAN infer from context what a given command does and can usually edit it to my needs.

Reading the room, the OP seemed clearly intimidated with the COM code for a couple of reasons, I think. First, I don't think he has the experience to modify a given script from context and second, he clearly stated that he does NOT want to close Excel - something that your original code does as well as your latest statement.

My test spreadsheet was not ever in edit mode, but I consistently received the error until I simply added the sleep statement. My assumption was that it was still in the process of saving locally when the call to save to the flash drive was sent and it rejected it.

I know that everything the OP desired could be done with COM, but I was simply trying to build on what he was familiar with. Sometimes simple is better.

Russ

[Jose-phil]

Thank you RussF and flyingDman for sticking with it.

To start at the end - COM
I don't think I had heard of it till it was mentioned in this thread.
I did ask 'are we still in Autohotkeys ?'.

I will try RussF's code and if it works, will progressively remove the elements flyingDman considers superfluous, continually testing to see if it still works.

RussF, the error code you quoted, looks very similar to the one I was getting. I really appreciate your explanation of the details.

***************************************************************
After I removed 'send ^s', running it, triggered an error code, so I restored it.
Removing the 'sleep' leaves it functional.
By contrast. removing 'WinActivate, ahk_class XLMAIN' rendered it useless.

Interestingly, but irrelevant to my quest, the file on the USB stick updates without changing its datestamp.

[flyingDman]

If you search the forum on Excel or Word, you'll see many post that include COM (googling site:autohotkey.com Excel COM shows > 15K results). Yes, we are very definitively still in Autohotkey! AHK natively includes the ability to interface with applications that include COM. I have yet to find an example that requires sending of keystrokes to Excel rather than using COM. One of the benefits is that you do not need to activate the Excel window in order to interface with Excel. Hence WinActivate, ahk_class XLMAIN is not needed. When you send keystrokes - like ^s - the window needs to be activated first.

To set the the timestamp of a file use FileSetTime: https://www.autohotkey.com/docs/v1/lib/FileSetTime.htm

One of several Tutorials for COM with Office Apps: viewtopic.php?f=7&t=8978

[rockitdontstopit]

@flyingDman

I'm using Excel 2003. I get an error when trying to interface with Excel 2003 using COM if Excel is the last active window. If I activate another process (such as my brave browser) before attempting the COM interface, it works. Is this a error specific to Excel 2003?

This works:

Code: Select all

winactivate, ahk_exe brave.exe
winminimize, ahk_exe brave.exe

xl := comobjactive("excel.application")

xl.activeWorkbook.close(1)	

process, close, excel.exe

This does not work:

Code: Select all

xl := comobjactive("excel.application")

xl.activeWorkbook.close(1)	

process, close, excel.exe

excel error.png (8.79 KiB) Viewed 1255 times

[flyingDman]

I don't know.