how to get the directory (file save) when the macro is run the first time in the day

[Tarumar7]

Hello everyone; I wonder if you can help me with a specific issue:


On the first run (of the day):

newfilename = Application.GetSaveAsFilename(InitialFileName:="xyz001.xlsx", FileFilter:="Excel Workbook (*.xlsx), *.xlsx", Title:="Save As")
If newfilename = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=newfilename

I would like to get the directory path so that in all the next saves (say till xyz999.xlsx) I can bring up the same directory path and the next file number for
convenience without having the user select the path every time the macro is run. (Not just for convenience but I need the directory to figure out the next file number)

Thank you.

[SamT]

In a standard Module

Option Explicit

Dim mPath As String
Dim mNameNumber As String

Public Function NameNumber() As String
If mNameNumber = "" Then mNameNumber = "001"
NameNumber = mNameNumber
mNameNumber = Format(CLng(mNameNumber) + 1, "000")
End Function

Modify your code like this

Sub t()
Dim newfilename
If mPath = "" Then
newfilename = Application.GetSaveAsFilename(InitialFileName:="xyz" & NameNumber & ".xlsx", FileFilter:="Excel Workbook (*.xlsx), *.xlsx", Title:="Save As")
mPath = Left(newfilename, Len(newfilename) - InStrRev(newfilename, "\"))
Else
newfilename = Application.GetSaveAsFilename(InitialFileName:=mPath & "xyz" & NameNumber & ".xlsx", FileFilter:="Excel Workbook (*.xlsx), *.xlsx", Title:="Save As")
End If
End Sub

[Tarumar7]

SamT thank you so much for your help and clear code. Global declaration of the variables dawned on me 6 hours or so after my post! The global variables keep their values until the application is closed which is great. When user starts Excel the next day it will suggest file number 001 again but I guess there's not much one can do to overcome that. I changed the first part of the code a bit to accommodate for the Cancel button:

Option Explicit
 
Dim mPath As String
Dim nextFilename As String
Dim newfilename
Sub t()    
    If mPath = "" Then
        newfilename = Application.GetSaveAsFilename(InitialFileName:="xyz001.xlsx", FileFilter:="Excel Workbook (*.xlsx), *.xlsx", Title:="Save As")
        mPath = Left(newfilename, InStrRev(newfilename, "\"))
        If newfilename = False Then Exit Sub
        ActiveWorkbook.SaveAs Filename:=newfilename
    Else
        Call NextFile
        newfilename = Application.GetSaveAsFilename(InitialFileName:=nextFilename, FileFilter:="Excel Workbook (*.xlsx), *.xlsx", Title:="Save As")
        If newfilename = False Then Exit Sub
        ActiveWorkbook.SaveAs Filename:=newfilename
    End If
End Sub

I had made the NextFile subroutine for figuring out the next file number based on 1)most recently saved/modified file in that format in that folder or 2) the highest number of the file in that format in that folder.
That way teher is no chance of overwriting an already saved file.

All I need now is a way to auto click save after 3 seconds of inactivity when the Save as dialog box is displayed (with the suggested next filename) and of course when the user clicks the filename area the timer should stop.

Thank you.

[Paul_Hossler]

When user starts Excel the next day it will suggest file number 001 again but I guess there's not much one can do to overcome that.

You could save the last sequence number and the folder in the registry

[SamT]

Put the "Saving" code in a UserForm with a timer. When the SaveAs Dialog is shown Load, but don't show the UserForm. When the Dialog is clicked run the SaveAs Sub in the UserForm and Unload the form.

I thimk.

When user starts Excel the next day it will suggest file number 001 again but I guess there's not much one can do to overcome that

Create a Project.ini file in the Excel Folder and use it like a CSV file

FileNamePart, LastPath, LastNum
xyz, C;\MyDir, 001

See this thread for speedy handling of CSVs: Process All CSV Files In SubFolders