Vba Creating a new excel file from clipboard

[SebastianHuang]

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 a vba 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.

 

[severynm]

A good explanation of reading from the clipboard in this article:

You can access and retrieve the data in the clipboard into a variable in Excel VBA, using the Microsoft Forms library. On top of copy and paste the code below, you must follow the these steps and activate Microsoft Forms 2.0 Object Library for the code to work. You only need to do it once as long as this macro stays in the same file.

1. On the Macro editor menu bar on the top, click on “Tools”
2. In the menu, click on “References”
3. Click the “Microsoft Forms 2.0 Object Library”
4. Press OK

If you don’t follow these steps, you will get a “Compile error: User-defined type not defined” error… Lots of people have been stumped on this part.


Public Clipboard As New MSForms.DataObject

Sub readClipboard()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'of you will get a "Compile error: user-defined type not defined"
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
Debug.Print S 'print code in the Intermediate box in the Macro editor
End Sub

In this example, the clipboard is saved to variable s, so then all you would have to to is

ThisWorkbook.SaveAs "C:\...FolderPathToSave...\" & s & ".xlsx"

, or whatever file extension you need. This would just perform a SaveAs operation and you are free to do whatever you want in the workbook after. If you'd like it to close, just perform Workbook.Close on the newly created workbook.