Save .xlsm using PC name if file does not exist

[Spectrum0311]

Hello All,

This will be my 1st posting and I would greatly appreciate some advise on the best way to handle what I am attempting to accomplish. Basically, I would like to open an excel workbook file (.xlsm) and rename the file using the PC name ("Book1" & PC_Name).

Obviously, I do not want the file to be overwritten if it already exists. In which case, if the file name already exists, then I would just need the file to open.

• Find PC Name
• Check for existing file using the PC Name and open if already exists
• Save the current workbook using the PC Name ("Book1" & PC_Name) if no file match found.

I should probably mention that the path will be the static and will not change. Example: (c:\\home\excel_files\Book1" & PC_Name).

Thank you for your greatly appreciated help.


-- removed inline image ---

 

[Blade Hunter]

You will need to make some minor changes but this should give you the basic structure.

Sub FileExists1()
Dim FSO As Object
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FileExists("C:\" & Environ("COMPUTERNAME") & ".XLSM") Then
    Workbooks.Open ("C:\" & Environ("COMPUTERNAME") & ".XLSM")
Else
    ActiveWorkbook.SaveAs ("C:\" & Environ("COMPUTERNAME") & ".XLSM")
End If
End Sub

Cheers

Dan

 

[Spectrum0311]

Wow, that was fast! Thank you, the code you provided worked effectively and efficiently!

-- removed inline image ---

 

[Blade Hunter]

Cool, happy to help.

Dan