Place newly updated file into specific folder on network drive

[skidda420]

Hello all,
I have done much digging in order to get this far but I'm still in little bit of a pickle. I am trying to make it so if a cell I have has a specific number form that it will save to a specific folder. In the Cell (L1) I have the date set as "mm-dd-yyyy hh-mm" and machine number ("11D, 12D, 13D"). When I save the file it looks like this "8-9-2021 9-38 11D", which is perfect. However I am trying to find a way to direct the file depending on the machine (11D, 12D, 13D) into their own separate folders. This is the VBA I am using through a button click at the moment. Ignore the path, I'm just using it for testing purposes. Any suggestions or help is much appreciated. Thank you in advance.


Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
path = "\\DESKTOP-9IK993S\2TB Download Drive\Torrents\"
filename1 = Range("L1").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
Application.Quit
End Sub

 

[earthworm]

Hello all,
I have done much digging in order to get this far but I'm still in little bit of a pickle. I am trying to make it so if a cell I have has a specific number form that it will save to a specific folder. In the Cell (L1) I have the date set as "mm-dd-yyyy hh-mm" and machine number ("11D, 12D, 13D"). When I save the file it looks like this "8-9-2021 9-38 11D", which is perfect. However I am trying to find a way to direct the file depending on the machine (11D, 12D, 13D) into their own separate folders. This is the VBA I am using through a button click at the moment. Ignore the path, I'm just using it for testing purposes. Any suggestions or help is much appreciated. Thank you in advance.


Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
path = "\\DESKTOP-9IK993S\2TB Download Drive\Torrents\"
filename1 = Range("L1").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
Application.Quit
End Sub

Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
[B]Dim Folder as String
Folder = Range("A1").value  ' Select the Cell where you want the folder to appear[/B]

path = "\\DESKTOP-9IK993S\2TB Download Drive\Torrents\" [& Folder & "\" ' This will drop the file as per Folder name . Make minor changes
filename1 = Range("L1").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook  
Application.DisplayAlerts = True
Application.Quit
End Sub

 

[skidda420]

I'm not sure if I'm understanding correctly, with the Folder string (A1). value .

 

[earthworm]

I'm not sure if I'm understanding correctly, with the Folder string (A1). value .

Inputting value in this cell will direct macro to patch as per value present in cell A1 . Now its up to you how can you use A1 to give path .

Example . Since I haven't tested your macro , i cannot say further . I have given an idea as to how you can make the location dynamic

"C"\Test"\"

 

[skidda420]

Inputting value in this cell will direct macro to patch as per value present in cell A1 . Now its up to you how can you use A1 to give path .

Example . Since I haven't tested your macro , i cannot say further . I have given an idea as to how you can make the location dynamic

"C"\Test"\"

So if I'm understanding correctly (A1) would be the full folder path then (\\DESKTOP-9IK993S\2TB Download Drive\Torrents\) then followed by the filename that's generated by what's in L1? I'll have to play around with it and give it a run later today. Thank you for the input. =)

 

[earthworm]

So if I'm understanding correctly (A1) would be the full folder path then (\\DESKTOP-9IK993S\2TB Download Drive\Torrents\) then followed by the filename that's generated by what's in L1? I'll have to play around with it and give it a run later today. Thank you for the input. =)

Yup .

 

[skidda420]

yeppers, just tested and it works!!! Awesome, thank you so much, have been banging my head on my desk for a couple weeks now. I knew there had to be a way to do it. Thank you again, you rock!

 

[skidda420]

odd question and i know its asking alot, but is there a way to do this by month? instead of the 11D, 12D, 13D. and using three letter month abbreviations (Sep, Oct, Nov) that are automatically generated using the date format. I try using the date format as the file name and instantly get thrown a 400 error due to the number formatting. If not then I'll come up with a clever way. Apologies.

 

[earthworm]

odd question and i know its asking alot, but is there a way to do this by month? instead of the 11D, 12D, 13D. and using three letter month abbreviations (Sep, Oct, Nov) that are automatically generated using the date format. I try using the date format as the file name and instantly get thrown a 400 error due to the number formatting. If not then I'll come up with a clever way. Apologies.

I am sharing the below Code from my macro to give you idea . You can manipulate as per your requirement.

Sub MainFolderwithsubfolder8A()

'Create Main Folder

' (Remove below if you want to create subfolder)
' MkDir "C:\Users\earthworm\Desktop\Test2\"

'Create Sub Folder with Date

Dim valuedate As String
valuedate = (Format(Date, "dd-mm-yyyy"))

MkDir Worksheets("Location").Range("B2").Value
MkDir Worksheets("Location").Range("B3").Value
MkDir Worksheets("Location").Range("B4") & valuedate

End Sub

 

[skidda420]

when you say location I'm assuming that's the sheets name correct? or the folder location? Now I'm a little confused lol. So it would be making its own separate directory within the file system?