Excel VBA save file to desktop & rename based on a cell
Solved
yugu_8551
Posted messages
6
Status
Member
-
yugu_8551 Posted messages 6 Status Member -
yugu_8551 Posted messages 6 Status Member -
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 answer
-
Hello,
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
