CODEWITHSUNDEEP
excelvbafilesave
user2890510
user2890510

Reputation: 25

Dynamic file saving using cells values in vba

I would like to achieve dynamic file saving to correspondant folder name using cells values in vba like so:

Sub Envoie_Formulaire()
        Dim MyFile1 As String
        Dim MyFile2 As String
        Dim MyFile3 As String
        Dim MyFile4 As String
        Dim MyFileDir As String
        Dim MyFile As String
        MyFile1 = Range("D2").Text
        MyFile2 = Range("E2").Text
        MyFile3 = Range("F2").Text
        MyFile4 = Range("G2").Text
        MyFileDir = MyFile1 + MyFile2
        MyFile = MyFile1 + MyFile2 + MyFile3 + MyFile4
       ' Do not display the message about overwriting the existing file.
     
       ' Save the active workbook with the name of the
       ' active workbook. Save it on the E drive to a folder called
       ' "User" with a subfolder called "JoeDoe."
       ActiveWorkbook.SaveAs Filename:"\\localAdress\folder1\folder2\folder3\" & MyFileDir "\" & MyFile" "\"
       ' Close the workbook by using the following.
       Application.DisplayAlerts = False
End Sub

*** N.B:

D2 = the word  "BLOC-"
E2 = A letter from "A" to "N"
F2 =the word "BUREAU"
G2=a number of office from "1 to 1000".

So i was saving the dynamic filename "BLOC-XXX BUREAU-XXX" to a static network directory (\\localAdress\folder1\folder2\folder3\BLOC-A) Now i just want to call " BLOC-xxx" from user output in the first corresponding cells.

But the issue is a syntax error produced in the filename path ligne, when i added " & MyFileDir ""`. Any suggestions to achieve that?

Upvotes: 0

Views: 79

Answers (1)

user3598756
user3598756

Reputation: 29421

for sure a syntax error lies in that you have to change:

ActiveWorkbook.SaveAs Filename:

to:

ActiveWorkbook.SaveAs Filename:=

and from your variable names I'd say that you should change:

MyFile = MyFile1 + MyFile2 + MyFile3 + MyFile4

to:

MyFile = MyFile3 & MyFile4

and therefore the final statement should be:

ActiveWorkbook.SaveAs Filename:="\\localAdress\folder1\folder2\folder3\" & MyFileDir & "\" & MyFile

Upvotes: 1

Related Questions