Need VBA help - "preselect" file to save to

[sparkytech]

I am getting closer every day to my goal, but am I never satisfied (continuous improvement mindset). My code below copies data from a "Master" sheet, and then prompts the user to select a file within a specific folder to export / paste the data into. Is there a way to "preselect" the file, but wait until the user clicks "Open" to continue? There are (3) files in this directory (named such as West 2022 - Export, Central 2022 - Export, and East 2022 - Export), and while the same export file is usually selected, I also want to give the end user a chance to choose another file, or even different directory. The master file and the export file are named the same, with the addition of " - Export" on the end of the filename. What would really be cool is for the code to look at the current filename, and then preselect the associated "Export" file. For example: the user opens the workbook named "West 2022". When they run the export code, it looks for the matching filename, in this case "West 2022 - Export", and then preselects that file from the folder contents, and waits for the user to continue. Can someone help me with these goals, and could this code be simplified / cleaned up? Code below, thanks in advance!

Sub ExportCleanExcel(control As IRibbonControl)

' +-+-+-+-+-+-+ +-+-+-+-+-+ +-+-+-+-+-+
' |E|x|p|o|r|t| |C|l|e|a|n| |E|x|c|e|l|
' +-+-+-+-+-+-+ +-+-+-+-+-+ +-+-+-+-+-+

'Exports the information in the master sheet to a chosen excel file;
'will just move the data without macros or other extra features

Dim FileToOpen As Variant
Dim DestWkb As Workbook
Dim MasBotRow As Long
Dim CurrentDir As String

'Turn off screen updates to improve performance
With Application
.ScreenUpdating = False: .DisplayAlerts = False
End With

CurrentDir = CurDir
ChDrive "T:\Project Spreadsheets\2022"
ChDir "T:\Project Spreadsheets\2022"

'Allows user to select a file to export to using the traditional open file window

FileToOpen = Application.GetOpenFilename("All Excel Files (*.xls?), *.xls?", , "Please export file")

'If the user selects cancel when choosing a file to open FileToOpen is set to FALSE
'Using this we can check if a file was actually selected before continuing

If FileToOpen <> False Then
' Set MasBotRow variable to row 2500
MasBotRow = 2500

'Open the file that was selected and then set that workbook as DestWkb
Set DestWkb = Application.Workbooks.Open(FileToOpen)

'Copies rows A5 to W to last row and also Z5 to last row on sheet {skips "X:Y"}

ThisWorkbook.Sheets("Master").Range("A5:W" & MasBotRow).Copy
DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues

ThisWorkbook.Sheets("Master").Range("Z5:Z" & MasBotRow).Copy
DestWkb.Sheets(1).Range("X5:X" & MasBotRow).PasteSpecial xlPasteValues

'Close the data file

DestWkb.Close True
MsgBox "Export Complete", 64, "Export Complete"
End If

ChDir CurrentDir

'Turn back on screen updates
'Turn back on alerts after closing the file

With Application
.ScreenUpdating = True: .DisplayAlerts = True: .CutCopyMode = False
End With

End Sub

 

[Micron]

I've had no experience with GetOpenFileName save for the odd time helping out here. I've always used msoFileDialogFolderPicker or msoFileDialogFilePicker and I do believe that if you use the latter and set the InitialFileNameProperty to the complete file path, it will preselect that file in the file name combo. I like that you can change the button name to make it more clear to your users what the intent is.

 

[sparkytech]

I've had no experience with GetOpenFileName save for the odd time helping out here. I've always used msoFileDialogFolderPicker or msoFileDialogFilePicker and I do believe that if you use the latter and set the InitialFileNameProperty to the complete file path, it will preselect that file in the file name combo. I like that you can change the button name to make it more clear to your users what the intent is.

Thanks, that helps. After some trial and error, I wound up with the code below. It opens the dialog box with the file selected, but its in the directory of the "Master" file, not the export file (need to open to T:\Project Spreadsheets\2022). Any thoughts how to get it to open this directory?

Sub FileOpenDialogBox()

strFileFullName = ThisWorkbook.FullName
MyPath = "T:\Project Spreadsheets\2022"
ChDir MyPath

With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = strFileFullName & "- Export"
.AllowMultiSelect = False
.Filters.Add "Excel ", "*.xlsx*; *.xlsm, 1"
.Show
MyFullPath = .SelectedItems.Item(1)
End With
Workbooks.Open Filename:=MyFullPath
MyWbName = ActiveWorkbook.Name
MyAnswer = vbYes

End Sub

 

[Micron]

It's 9:30 PM here in Florida hotel and I've been up for 29 hours now without sleep (drove for 12 hours myself, then navigated after that). 23.5 hours on the road & the bags under my eyes have bags. Going to have a nightcap to kill my second wind and hit the hay. Hope someone chimes in to help - if not, perhaps I'll see this after July 5 when I get off the ship. Sorry, & good luck.