VBA save file with path and name defined by cell value

[sasar]

Hi all,

I searched around but could not find an answer to my question.
Looking for a VBA that will save my file with a file name defined by cell A1 to a path defined by specific network path and folder name in cell A2.

Tried several versions of codes I found online, but cannot find all the conditions in one place. When I try to merge all the conditions, I keep getting errors.

Could you please help?

Private Sub filename_cellvalue()
Dim Path1 As String
Dim Path2 As String
Dim filename As String
Path1 = "X:\test1\test2\test3"
Path2 = Range("A2")
filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path1 & Path2 & filename & ".xls", FileFormat:=xlNormal
End Sub


Thanks,
Alex

 

[Dryver14]

Because you have removed this line from the code

fpathname = Path1 & "" & Path2 & "" & myfilename & ".xls"

 

[sasar]

Thanks you are a lifesaver.
My excel is good, but VBA skills are non existent...

 

[Joe4]

Thanks you are a lifesaver.
My excel is good, but VBA skills are non existent...

You are welcome.

Note that the "trick" I showed you with MsgBox is a good debugging tool that you can use.

Also using F8 to step through your code line-by-line is another good debugging tool (especially if you re-size it so you can see your sheet and code at the same time).
That wouldn't have helped much here with this problem, but is often helpful when working with different ranges, sheets, or files, and you aren't sure why things don't seem to be happening like you want.

 

[sasar]

Just one final thing.
If I change the code to save the file as .xslx instead of .xls I get the run time error again.

Should it not be as simple as changing the extension in the code?

 

[Joe4]

If I change the code to save the file as .xslx instead of .xls I get the run time error again.

Should it not be as simple as changing the extension in the code?

I think you need a different FileFormat argument.

This leads to another great tool for learning VBA - the Macro Recorder.
Open up a blank Excel file, and then record yourself doing the step you want (like doing a "SaveAs" to and "xlsx" file).
Then stop the Macro Recorder, and view the code you just recorded. This will show you how the code needs to be structured to do what you want.
This is a great way to get VBA code snippets without creating the code from scratch yourself.

 

[sasar]

Great, thanks for the tip!
I tried that now, but realised that it is better I save files as xlsm and changing code worked without issues.

Thanks again for your help, tips and speedy responses.

 

[Compensation_Sensation]

Hi All,
I have a similar situation where I'm trying to have Excel/Word save a mail merge PDF in a folder defined by the contents of a cell. For example, I have a few records that would go to a folder named Steve, and another few that would go to one named Bill. They won't be sent directly from the file, just stored in a folder. Here's what I have so far:

Sub MakeMyFolder()
    Dim fdObj As Object
    Application.ScreenUpdating = False
    Set fdObj = CreateObject("Scripting.FileSystemObject")
    If fdObj.FolderExists("C\:Worktable Files\Leader Folders\2019 TR Statements\Steve\") Then
    Else
        fdObj.CreateFolder ("C\:Worktable Files\Leader Folders\2019 TR Statements\Steve\")
        End If
    Application.ScreenUpdating = True
End Sub
Option Explicit

Const FOLDER_SAVED As String = "C:\Worktable Files\Leader Folders\2019 TR Statements\"
Const SOURCE_FILE_PATH As String = "C:\Worktable Files\Leader Folders\2019 TR Statements\Mail Merge Source Data.xlsx"


Sub MailMerge2PDF()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long

Set MainDoc = ActiveDocument
With MainDoc.MailMerge
    
        '// if you want to specify your data, insert a WHERE clause in the SQL statement
        .OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [Sheet1$]"
            
        totalRecord = .DataSource.RecordCount

        For recordNumber = 1 To totalRecord
        
            With .DataSource
                .ActiveRecord = recordNumber
                .FirstRecord = recordNumber
                .LastRecord = recordNumber
            End With
            
            .Destination = wdSendToNewDocument
            .Execute False
            
            Set TargetDoc = ActiveDocument

            TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Employee_Name").Value & ".docx", wdFormatDocumentDefault
            TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("Employee_Name").Value & ".pdf", exportformat:=wdExportFormatPDF
            
            TargetDoc.Close False
            
            Set TargetDoc = Nothing
                    
        Next recordNumber

End With

Set MainDoc = Nothing
End Sub

So, I'm thinking that I need to define the people that will be classified as "folders" but I'm not sure how or where to put that. I can create the files, I can create the folders (kinda), but not all at once and cleanly.