VBA File Save As and pull file name from Cell

[tmpollard]

Hello All,
I'm trying to get VBA code that will do a file save as and the name of the file will be pulled from a specific cell on the spreadsheet, however the extension will be changed from .xlsm to .xlsx so that I don't mess up my template file.
This file is on a network location.
Thank you.

 

[Vogateer]

Something like this?

Sub save_to_xlsx()
    Dim folder As String, filename As String, fullpath As String
    
    folder = "S:\NetworkDrive\Folder\" ' Change to the network location
    filename = Range("A1").Value2 ' Change "A1" to the cell with the file name
    fullpath = folder & filename & ".xlsx"
    Debug.Print fullpath ' If you want this in immediate window
    
    ' Use this code below to stop the pop-up that asks whether you want to save
    Application.DisplayAlerts = False

    ' Finally save the copy 
    ThisWorkbook.SaveCopyAs fullpath

    End Sub

 

[tmpollard]

This is how I edited the code based on your recommendations, I don't get any errors but I don't see the filed saved in this location.
Thanks


Sub save_to_xlsx()
Dim folder As String, filename As String, fullpath As String

folder = "\\ucapsn01\share\Plant Information\Raw Data\Shift Details" ' Change to the network location
filename = Range("A10").Value2 ' Change "A1" to the cell with the file name
fullpath = folder & filename & ".xlsx"
Debug.Print fullpath ' If you want this in immediate window

' Use this code below to stop the pop-up that asks whether you want to save
Application.DisplayAlerts = False

' Finally save the copy
ThisWorkbook.SaveCopyAs fullpath


End Sub

 

[Vogateer]

I'm not familiar with your setup, but I know I always need a drive letter in front of the folder.

 

[Vogateer]

Oh, and you also need to end the folder with the backslash. I should have put an If statement to add that trailing backslash at the end if it wasn't there already.

 

[Vogateer]

If you can figure out the drive letter, replace [DRIVE_LETTER] below with it, and I think you'll have better luck.

Sub save_to_xlsx()
    Dim folder As String, filename As String, fullpath As String
    
    ' Change below to the network location
    folder = "[DRIVE_LETTER]:\ucapsn01\share\Plant Information\Raw Data\Shift Details"
    filename = Range("A10").Value2 ' Change "A10" to the cell with the file name
    
    If Right(folder, 1) <> "\" Then
        fullpath = folder & "\" & filename & ".xlsx"
    Else
        fullpath = folder & filename & ".xlsx"
        End If
        
    Debug.Print fullpath
    
    ' Use this code below to stop the pop-up that asks whether you want to save
    Application.DisplayAlerts = True
    
    ThisWorkbook.SaveCopyAs fullpath

    End Sub

 

[tmpollard]

Thanks everyone for your help, I had to put a trailing \ and it worked. Thank you all.

 

[Vogateer]

Glad to hear it worked, and you're welcome!

Is there an option to mark the post as answered? It helps me out when looking through my answered questions.

 

[tmpollard]

Awesome, got it.