Vba Creating a new excel file from clipboard
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Vba Creating a new excel file from clipboard
I want to create a new excel file with the name coming from the clipboard and save it in Saved folder on desktop.
E.g., I copied a text called 2020 Highest mountain. Instead of doing save as, paste name, and choosing to save as excel book, and save it in the saved folder. Is there an autohotkey code that can do that?
The names will be different every time as it is coming from clipboard.
Maybe let me review the file so I could close it myself. or have the excel program close it for me.
That would be all.
E.g., I copied a text called 2020 Highest mountain. Instead of doing save as, paste name, and choosing to save as excel book, and save it in the saved folder. Is there an autohotkey code that can do that?
The names will be different every time as it is coming from clipboard.
Maybe let me review the file so I could close it myself. or have the excel program close it for me.
That would be all.
Re: Vba Creating a new excel file from clipboard
Code: Select all
dir = %A_Desktop%\Saved
Gui, New
Gui, Font, s12 w500
Gui, Color, F8DC75
Gui, Add, Text, w320 Center, Please wait....
Gui, Show,, Working
If !FileExist(dir) {
FileCreateDir, %dir%
If ErrorLevel {
Gui, Destroy
MsgBox, 48, Error, An error occurred while creating the directory. Aborting.
Return
}
}
FileRecycle, % file := dir "\" Clipboard ".xlsx"
xl := ComObjCreate("Excel.Application"), xl.Workbooks.Add, xl.ActiveWorkbook.SaveAs(file), xl.Quit()
Gui, Destroy
If FileExist(file)
MsgBox, 64, Done, Done!
Else MsgBox, 48, Error, The file could not be created.`n`n%file%
ExitApp
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Re: Vba Creating a new excel file from clipboard
11111
Last edited by ExcelHotKeys on 11 Apr 2021, 13:53, edited 1 time in total.
Re: Vba Creating a new excel file from clipboard
That is a very long file name that you had on your clipboard (until post was deleted). Would start with something shorter. You might also want to move the routine into a hotkey.
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Re: Vba Creating a new excel file from clipboard
If your clipboard contains control characters such as line feeds, you should strip them first. You can debug your script by finding out what is on your clipboard. Make sure that it does not contain characters that would be illegal in a file name.
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Re: Vba Creating a new excel file from clipboard
I'm not sure what you mean. I just copied the cell that contains the text, "Sep" in an excel file, then ran the ahk.
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Re: Vba Creating a new excel file from clipboard
By Clipboard, I mean like when you just do control c, the data will be saved in a clipboard somewhere like in a temporary data hold until I press esc, or do control c on another data.
Re: Vba Creating a new excel file from clipboard
Copying from Excel appends a line feed. Try that and see. You can trim it as follows.
Code: Select all
dir = %A_Desktop%\Saved
Gui, New
Gui, Font, s12 w500
Gui, Color, F8DC75
Gui, Add, Text, w320 Center, Please wait....
Gui, Show,, Working
If !FileExist(dir) {
FileCreateDir, %dir%
If ErrorLevel {
Gui, Destroy
MsgBox, 48, Error, An error occurred while creating the directory. Aborting.
Return
}
}
FileRecycle, % file := dir "\" excelFix(Clipboard) ".xlsx"
xl := ComObjCreate("Excel.Application"), xl.Workbooks.Add, xl.ActiveWorkbook.SaveAs(file), xl.Quit()
Gui, Destroy
If FileExist(file)
MsgBox, 64, Done, Done!
Else MsgBox, 48, Error, The file could not be created.`n`n%file%
ExitApp
excelFix(string) {
new := RTrim(string, "`r`n") ; Remove the trailing CRLF
new := Instr(new, "`n") ? RegExReplace(new, """(.+)""", "$1") : new ; Remove bounding quotation marks
Return StrReplace(new, """""", """") ; Fix double quotation marks
}
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Re: Vba Creating a new excel file from clipboard
This worked, but it only creates a new spreadsheet and data doesn't save any data in the original spreadsheet. And it exits after using the ahk once. Have to run the afk again to save another file.
-
- Posts: 41
- Joined: 21 Dec 2020, 10:27
Re: Vba Creating a new excel file from clipboard
Like an excel file may have 5 tabs, it creates only a new tab.
Re: Vba Creating a new excel file from clipboard
What the script does: "I want to create a new excel file with the name coming from the clipboard and save it in Saved folder on desktop."
It's just a demo for how you can create a spreadsheet file. You can put it into a hotkey routine if you want to run it many times. If you search the forum for "Excel", you will find many examples of code for manipulating spreadsheets. I believe that there is a tutorial as well; it might help you.
It's just a demo for how you can create a spreadsheet file. You can put it into a hotkey routine if you want to run it many times. If you search the forum for "Excel", you will find many examples of code for manipulating spreadsheets. I believe that there is a tutorial as well; it might help you.