Place newly updated file into specific folder on network drive

skidda420

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
621
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
VBA Code:
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
 
Solution

skidda420

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm not sure if I'm understanding correctly, with the Folder string (A1). value .
 

earthworm

Well-known Member
Joined
May 19, 2009
Messages
621
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2009
Messages
621
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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 . :cool:
 

skidda420

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2009
Messages
621
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,147,733
Messages
5,742,859
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top