Excel VBA save file to desktop & rename based on a cell
Solved
yugu_8551
Posted messages
6
Status
Membre
-
yugu_8551 Posted messages 6 Status Membre -
yugu_8551 Posted messages 6 Status Membre -
Hello
I'm new to macros and I need your help.
Let me explain:
I have an Excel workbook with several sheets, only the first one is important, the others are hidden. For ergonomics, certain cells are merged. On the first sheet, specifically in cell B7, I enter an 8-digit number like "19005695".
After completing my table, I would like to create a button labeled DONE that will save a copy of this workbook on the desktop under the number from cell B7 "19005695", while keeping my original workbook blank (without the data).
I hope to find someone who can help me, thank you in advance and especially for your patience. Thank you in advance for your valuable help.
I'm new to macros and I need your help.
Let me explain:
I have an Excel workbook with several sheets, only the first one is important, the others are hidden. For ergonomics, certain cells are merged. On the first sheet, specifically in cell B7, I enter an 8-digit number like "19005695".
After completing my table, I would like to create a button labeled DONE that will save a copy of this workbook on the desktop under the number from cell B7 "19005695", while keeping my original workbook blank (without the data).
I hope to find someone who can help me, thank you in advance and especially for your patience. Thank you in advance for your valuable help.
1 réponse
Hello,
Press Alt F11 to access the editor, then insert a Module.
Put this code into that module:
To run the macro, add a button by going to Developer, Design Mode:
Insert Form Control Button. Right-click on the button:
Assign a macro, select Save
The button is operational
--
@+ The Woodpecker
Press Alt F11 to access the editor, then insert a Module.
Put this code into that module:
Option Explicit Dim folder As String Dim desktop As String Sub Save() Application.DisplayAlerts = False desktopPath folder = desktop & "\" ActiveWorkbook.SaveAs Filename:=folder & Sheets("Sheet1").Range("B7").Value & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False MsgBox "Workbook saved on the Desktop", vbInformation, "Saving" Application.DisplayAlerts = True End Sub Private Sub desktopPath() 'https://excel-malin.com/codes-sources-vba/trouver-chemin-de-bureau/ On Error GoTo ErrorTest Dim desktopPath As String desktopPath = GetDesktopPath() desktop = desktopPath 'displays the path to the Desktop folder Exit Sub ErrorTest: MsgBox "An error occurred..." End Sub Public Function GetDesktopPath() As String 'by: Excel-Malin.com ( https://excel-malin.com ) On Error GoTo GetDesktopPathError Dim desktopPath As String desktopPath = "" Dim oWSHShell As Object Set oWSHShell = CreateObject("WScript.Shell") desktopPath = oWSHShell.SpecialFolders("Desktop") If (Not (oWSHShell Is Nothing)) Then Set oWSHShell = Nothing GetDesktopPath = desktopPath Exit Function GetDesktopPathError: If (Not (oWSHShell Is Nothing)) Then Set oWSHShell = Nothing GetDesktopPath = "" End Function To run the macro, add a button by going to Developer, Design Mode:
Insert Form Control Button. Right-click on the button:
Assign a macro, select Save
The button is operational
--
@+ The Woodpecker
Thank you first of all for taking the time to respond to me! It seems that it is not working, may I send you the file?
https://www.cjoint.com/
then copy the link here
Does it work?
But with the explanations provided, it is very easy to set up. We need to know where it is getting stuck?
It should be fine!!