VBA to overwrite an existing excel file (SaveAs) when you only know the File Path?

[dougmarkham]

Hi Folks,

I have a VBA model (*.xltx in draft stage) in which I import a specific excel file (containing multiple ws) from a specific file path. I am modifying some of the data. Once modified, I want to SaveAs to overwrite the file I originally imported. The file I'm importing will be called something new each time the model is run (on account of it being an export that contains a time-stamp) but the filePath will only ever contain one file.

I have come across various methods of using VBA to open a dialog box to a specified filePath preset to save as *.xlsx. All of them seem to require you to provide a file name in advance.

Does anybody know of VBA code to overwrite an existing excel file stored within a specific filePath?
i.e., for VBA to look for the excel file located within a specified file path; take file name; place file name into the "File name: " box in the SaveAs dialogue box---so the user can press save?

Kind regards,

Doug.

 

[JLGWhiz]

When you use SaveAs Excel is expecting a different name for the file than the one currently assigned to the file being saved. SaveAs is designed to prevent overwriting, not to facilitate it. If you want to overwrite the existing file with different data, then open that file, make changes and then click the save button. Using a template to make changes to an existing file is doing it the hard way.

 

[dougmarkham]

When you use SaveAs Excel is expecting a different name for the file than the one currently assigned to the file being saved. SaveAs is designed to prevent overwriting, not to facilitate it. If you want to overwrite the existing file with different data, then open that file, make changes and then click the save button. Using a template to make changes to an existing file is doing it the hard way.

Hi JLGWhiz,

Thanks for your reply.

Apologies, I've not explained myself too well---hopefully I am more precise this morning.


I have compiled modelA.xltx that imports FileA.xlsx from LocationA---when modelA.xltx is opened, it becomes modelA1 as I've opened a macro-enabled template file, which can then overwrite an FileA.xlsx without destroying modelA.xltx.

Prior to over-writing FileA.xlsx, modelA1 amends the data it has imported from FileA on several worksheets.
I.e., what I'm trying to do take the modified data in ModelA1 and SaveAs as FileA so that the modified data overwrites the originally imported data in FileA.


What I am then looking for is VBA code that opens the SaveAs dialog box that selects defaults and does the following:

1) Opens by default to the filepath LocationA where FileA.xlsx is stored.
2) Automatically selects *.xlsx as the default file type
3) Looks for the excel file in LocationA and grabs the file name e.g., FileA.xlsx is found and put into the 'File Name' box of the SaveAs dialog box.


In reality, FileA it is an export from another system---because the file-name has a date/time stamp, it is never the same filename.
Only point 3 is not commonly available on Google, so I wondered if it is possible?

Kind regards,

Doug.

 

[JLGWhiz]

Then the simple way to do it would be to modify your code that does the first SaveAs changing the file name to ModelA1 so that it retains the original file name while saving it to the directory you want it in. But since you have not posted that code, it would be difficult to offer any modification to it.

I think you mignt be making the process more complicated that it really is. Or maybe I am not understanding your real issue.

 

[dougmarkham]

Then the simple way to do it would be to modify your code that does the first SaveAs changing the file name to ModelA1 so that it retains the original file name while saving it to the directory you want it in. But since you have not posted that code, it would be difficult to offer any modification to it.

I think you mignt be making the process more complicated that it really is. Or maybe I am not understanding your real issue.

Hi JLGWhiz,

This afternoon I've managed to find code to mostly achieve what I was looking for by modelling the filename format that exports from our system.

Public Sub:

Public Sub SaveFileAs()
    Dim TodaysDate As String
    Dim RawDataFileName As Variant
    Dim SaveFileName As Variant
    Dim SavePath As Variant
    Dim SavePathAndFileName As Variant
    
    TodaysDate = GetTodaysDate
    
    SaveFileName = "[B]FileA[/B]_" & TodaysDate & " export file"
        
    SavePath = "C:\LocationA"
    
    SavePathAndFileName = SavePath & "\" & SaveFileName
    On Error GoTo AA
    SaveWorkbookAs (SavePathAndFileName)
AA:
End Sub 'SaveFileAs

Private Function:

Private Function GetTodaysDate()


    GetTodaysDate = Format(Now(), "yyyymmdd-hhmm")


End Function 'GetTodaysDate

Private Sub

Private Sub SaveWorkbookAs(SaveFileName As String)
    
    Dim FileExtension As String
    
    Dim SaveFile As String


    FileExtension = "Excel Workbook (*.xlsx), *.xlsx," & _
               "All files (*.*), *.*"
 
    SaveFile = Application.GetSaveAsFilename( _
                InitialFileName:=SaveFileName, _
                FileFilter:=FileExtension)
                
    MsgBox "Save File is called " & SaveFile
                
    ActiveWorkbook.SaveAs SaveFile, xlWorkbookNormal
        
End Sub 'SaveWorkbookAs

This will probably do the job alright. I'm still wondering if it is possible modify the above to get VBA to look into a Folder location, find the one excel filename in that folder and grab that filename to use in the SaveAs dialog box as the file name? I think this VBA will suffice. I had to use a different search approach to find it.

Kind regards,

Doug.

 

[JLGWhiz]

If you already know the file name you want to use, why would you want to look in a directory to retrieve it? You already have it, so just use it, as you have in the code above, as the Initial File Name in the GetSaveAs method. Logic, like water, follows the least line of resistance. Try to avoid overdoing the use of variables, redundant code execution and extravagant data manipulation when it can be done with simple execution of logical process.

 

[dougmarkham]

If you already know the file name you want to use, why would you want to look in a directory to retrieve it? You already have it, so just use it, as you have in the code above, as the Initial File Name in the GetSaveAs method. Logic, like water, follows the least line of resistance. Try to avoid overdoing the use of variables, redundant code execution and extravagant data manipulation when it can be done with simple execution of logical process.

Hi JLGWhiz,

If you already know the file name you want to use, why would you want to look in a directory to retrieve it?

This is a reasonable question. In general, I was interested in knowing if the SaveAs Dialog box could be programmed to retrieve the filename from the directory because our web-systems are often changed without our department being notified.

Field names get changed, so why not the filenames of database exports etc? Therefore I reasoned that if I could get the filename box to fill by default using a filename within a specified directory, any changes to exported file's filename wouldn't require the modification of the SaveAs code.

Due to this, I'm constantly trying to think how to safeguard my models against changes made to systems controlled by other departments lol.

Logic, like water, follows the least line of resistance. Try to avoid overdoing the use of variables, redundant code execution and extravagant data manipulation when it can be done with simple execution of logical process.

Thanks for this advice, I do tend focus on finding a more complex solution (sometimes partly out of curiosity). I don't know if this is a common issue for coders but I often a simpler method pops into my mind days later after I've over-complicated a process.

Kind regards,

Doug.