Excel VBA (part4): VBA File Dialog Box Saves File Name with Naming Conventions
Автор: Mike YU (BI&Analytics)
Загружено: 2021-12-15
Просмотров: 1854
In the video I demonstrate the steps on how to create a VBA procedure to save file with naming conventions on it's name according to the inputs given by users.
In the file we have applied VBA FileDialog Object with it's SaveAs DialogType argument. The FileDialog properties AllowMultiSelect,FilterIndex,InitialFileName,InitialViewand methods Show and Execute are utilized in the procedure.
The relevant explainations links of the Object and Propertites are list here for your refrences:
FileDialog object (Office) | Microsoft Docs: https://docs.microsoft.com/en-us/offi...
FileDialog.AllowMultiSelect property (Office) | Microsoft Docs: https://docs.microsoft.com/en-us/offi...
FileDialog.FilterIndex property (Office) | Microsoft Docs:
https://docs.microsoft.com/en-us/offi...
FileDialog.InitialView property (Office) | Microsoft Docs:
https://docs.microsoft.com/en-us/offi...
FileDialog.Show method (Office) | Microsoft Docs:
https://docs.microsoft.com/en-us/offi...
FileDialog.Execute method (Office) | Microsoft Docs
https://docs.microsoft.com/en-us/offi...
FileDialog.SelectedItems property (Office) | Microsoft Docs:
https://docs.microsoft.com/en-us/offi...
You may copy VBA codelines here or find the VBA codes from my blog site:
https://mikeyu.lovestoblog.com/projects/
VBA Procedure:
Sub CmdSaveFile_Click()
Dim strFolder, Filename As String
'select the front pgae tab
Sheets("Front Page").Activate
'get the region name
Filename = ActiveSheet.Cells(10, 7)
'get the account name
Filename = Filename & "_" & ActiveSheet.Cells(11, 7)
'get the contact name
Filename = Filename & "_" & ActiveSheet.Cells(19, 7)
'get Sales rep name
Filename = Filename & "_" & ActiveSheet.Cells(20, 7)
'Add time stamp and file extenstion
Filename = Filename & "_" & Format(Now(), "yyyy-mm-dd_hh_mm") & ".xlsm"
'Open SaveAs dialog to a default with default file name
With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False '
.FilterIndex = 2 '2 is xlsm
.InitialFileName = ActiveSheet.Cells(15, 7) & Filename
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
.Execute
End With
End Sub
For more info of my BI & Analytics, please refer to the following website links:
My BI & Analytics Website:
https://sites.google.com/view/mikegua...
My blog website :
https://mikeyubianalytics.blogspot.com/
Доступные форматы для скачивания:
Скачать видео mp4
-
Информация по загрузке:
