I have searched through several threads already posted and some of them "touch" upon what I have trying to do, I try out some of the suggested solutions, but nothing seems to work the way I need it to. So let me start off by explaining what I am "trying" to do.
I have to track employee statistics and what they did on the equipment for each day within a given month. The statistics are entered into a protected spreadsheets by an individual from another department. I have set up a spreadsheet that obtains these values from these spreadsheets that are saved on the network server. The file path location of these spreadsheets are:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\ CHANGES
In the file path above I have typed "CHANGES" due to the fact that this will constantly change. For instance, values from the month of January of 2014 would look like this:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\2014\[01 Jan 2014.xlsm]2 Drill'!B6
Now obviously I could simply go into the code and manually change the folder name from 2014 to 2015, and then change each file name from [01 Jan 2014.xlsm] to [04 Apr 2015.xlsm]. However, I would have to manually change the code for each of the 12 months, and then I would have to change the year for the tab name from "2 Drill" and "3 Drill" and all the way up to the "15 Drill". There has to be an easier way to change the year within the file path location rather than manually changing the code for 180 different cells.
The spreadsheet is setup in such a way that I really only need to change the year. I have attempted to create a VBA macro that has an input box which prompts the user to enter a year, with the hopes that the entered year will be used to update the file path. However, the code that I have created removes the old file path and then leaves the cell blank.
HOW CAN I HAVE A USER ENTER A YEAR INTO AN INPUT BOX, WHICH WILL USE THE ENTRY TO UPDATE THE FILE PATH THAT IS ALREADY LOCATED WITHIN THE SELECTED CELL?
NOTE:
There are a few issue that I have created/discovered on my own.
1) I have no way of ensuring that the user will enter a year that is within the range of 2000 and 2024
2) I have set up the Input Box so that the value "20" is already present so that the user just has to add in the last 2 digits. However, if the user accidentally does not change anything and just hits ENTER, I had to create "dummy" spreadsheets with the year as "20" so that there will be no errors.
3) The person entering the statistics into the spreadsheet only creates spreadsheets as they need them. So today is March 17th, 2015. Therefore, spreadsheets for April, May, June... December 2015 do not exist yet. As a result, if I initially enter in the year "2015", when the macro reaches April, there will be an error since the spreadsheet does not yet exist. I would like the macro to default back to my "dummy" spreadsheets with the year, so for instance, since the following file path does not exist:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\2015\[04 Apr 2015.xlsm]2 Drill'!B6
It would be nice if the macro recognized this and defaulted back to:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\20\[04 Apr 20.xlsm]2 Drill'!B6
But this gets a little complicated due to the fact that it would entirely depend upon the month, as well as the equipment number.
Below is the code that I have put together, I have not idea if I have been going about this the correct way. But as of right now, this macro removes the current file path from the selected cell and then does not place anything back into the cell, it just leaves it blank.
Sub Raw_New_Year()
'
'-------------------- DEFINING VARIABLE -----------------------------------------------------------------------------------------
'
'File path is the link to the engineering drive
Dim FilePath As String
FilePath = "'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\"
'Establishing a default file path
Dim Path20 As String
Path20 = "'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\20\"
'Establishing a default year
Dim Year20 As String
Year20 = "20"
'Year is specific to folder location and file names
Dim Year1 As String
'Specifying the month that will be used in the file name
Dim Jan1 As String
Jan1 = "01 Jan"
Dim Feb1 As String
Feb1 = "02 Feb"
Dim Mar1 As String
Mar1 = "03 Mar"
Dim Apr1 As String
Apr1 = "04 Apr"
Dim May1 As String
May1 = "05 May"
Dim Jun1 As String
Jun1 = "06 Jun"
Dim Jul1 As String
Jul1 = "07 Jul"
Dim Aug1 As String
Aug1 = "08 Aug"
Dim Sep1 As String
Sep1 = "09 Aug"
Dim Oct1 As String
Oct1 = "10 Oct"
Dim Nov1 As String
Nov1 = "11 Nov"
Dim Dec1 As String
Dec1 = "12 Dec"
'Specifying the drill associated with the path
Dim DRILL2 As String
DRILL2 = "2 Drill'!B6"
Dim DRILL3 As String
DRILL2 = "3 Drill'!B6"
Dim DRILL4 As String
DRILL2 = "4 Drill'!B6"
'This will be the entire path name put together as a string
Dim PathName1 As String
'--------------------------------------------------------------------------------------------------------------------------------
'XXXXXXXXXXXXXXX DRILL 2 XXXXXXXXXXXXXXX
'Input Box prompting user to choose the year
Year1 = Application.InputBox(prompt:="Enter the year in which you would like to obtain data", Title:="ENTER A YEAR BETWEEN 2014 and 2024", Default:=Year20)
If Year1 = "" Then
Range("A1").Select
Range("B7").Select
MsgBox "You have clicked CANCEL, no changes were made"
Exit Sub
End If
If Not IsNumeric(Year) Then
Range("A1").Select
Range("B7").Select
MsgBox "You have entered an invalid YEAR"
Exit Sub
End If
If IsNumeric(Year) Or Year1 = 20 Then
'JANUARY
Range("B7").Select
ActiveSheet.Range("B7") = PathName1
PathName1 = FilePath & Year1 & "\[" & Jan1 & " " & Year1 & ".xlsm]" & DRILL2
'Then the code selects the next appropriate cell and adjusts that file path for each month of the year for all 15 pieces of equipment
End Sub
I have to track employee statistics and what they did on the equipment for each day within a given month. The statistics are entered into a protected spreadsheets by an individual from another department. I have set up a spreadsheet that obtains these values from these spreadsheets that are saved on the network server. The file path location of these spreadsheets are:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\ CHANGES
In the file path above I have typed "CHANGES" due to the fact that this will constantly change. For instance, values from the month of January of 2014 would look like this:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\2014\[01 Jan 2014.xlsm]2 Drill'!B6
Now obviously I could simply go into the code and manually change the folder name from 2014 to 2015, and then change each file name from [01 Jan 2014.xlsm] to [04 Apr 2015.xlsm]. However, I would have to manually change the code for each of the 12 months, and then I would have to change the year for the tab name from "2 Drill" and "3 Drill" and all the way up to the "15 Drill". There has to be an easier way to change the year within the file path location rather than manually changing the code for 180 different cells.
The spreadsheet is setup in such a way that I really only need to change the year. I have attempted to create a VBA macro that has an input box which prompts the user to enter a year, with the hopes that the entered year will be used to update the file path. However, the code that I have created removes the old file path and then leaves the cell blank.
HOW CAN I HAVE A USER ENTER A YEAR INTO AN INPUT BOX, WHICH WILL USE THE ENTRY TO UPDATE THE FILE PATH THAT IS ALREADY LOCATED WITHIN THE SELECTED CELL?
NOTE:
There are a few issue that I have created/discovered on my own.
1) I have no way of ensuring that the user will enter a year that is within the range of 2000 and 2024
2) I have set up the Input Box so that the value "20" is already present so that the user just has to add in the last 2 digits. However, if the user accidentally does not change anything and just hits ENTER, I had to create "dummy" spreadsheets with the year as "20" so that there will be no errors.
3) The person entering the statistics into the spreadsheet only creates spreadsheets as they need them. So today is March 17th, 2015. Therefore, spreadsheets for April, May, June... December 2015 do not exist yet. As a result, if I initially enter in the year "2015", when the macro reaches April, there will be an error since the spreadsheet does not yet exist. I would like the macro to default back to my "dummy" spreadsheets with the year, so for instance, since the following file path does not exist:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\2015\[04 Apr 2015.xlsm]2 Drill'!B6
It would be nice if the macro recognized this and defaulted back to:
'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\20\[04 Apr 20.xlsm]2 Drill'!B6
But this gets a little complicated due to the fact that it would entirely depend upon the month, as well as the equipment number.
Below is the code that I have put together, I have not idea if I have been going about this the correct way. But as of right now, this macro removes the current file path from the selected cell and then does not place anything back into the cell, it just leaves it blank.
Sub Raw_New_Year()
'
'-------------------- DEFINING VARIABLE -----------------------------------------------------------------------------------------
'
'File path is the link to the engineering drive
Dim FilePath As String
FilePath = "'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\"
'Establishing a default file path
Dim Path20 As String
Path20 = "'\\nmop22wsfsx01\Engineering1\Mine Planning\_Blast Planning\Monthly Blast Plan\Monthly D&B Report\20\"
'Establishing a default year
Dim Year20 As String
Year20 = "20"
'Year is specific to folder location and file names
Dim Year1 As String
'Specifying the month that will be used in the file name
Dim Jan1 As String
Jan1 = "01 Jan"
Dim Feb1 As String
Feb1 = "02 Feb"
Dim Mar1 As String
Mar1 = "03 Mar"
Dim Apr1 As String
Apr1 = "04 Apr"
Dim May1 As String
May1 = "05 May"
Dim Jun1 As String
Jun1 = "06 Jun"
Dim Jul1 As String
Jul1 = "07 Jul"
Dim Aug1 As String
Aug1 = "08 Aug"
Dim Sep1 As String
Sep1 = "09 Aug"
Dim Oct1 As String
Oct1 = "10 Oct"
Dim Nov1 As String
Nov1 = "11 Nov"
Dim Dec1 As String
Dec1 = "12 Dec"
'Specifying the drill associated with the path
Dim DRILL2 As String
DRILL2 = "2 Drill'!B6"
Dim DRILL3 As String
DRILL2 = "3 Drill'!B6"
Dim DRILL4 As String
DRILL2 = "4 Drill'!B6"
'This will be the entire path name put together as a string
Dim PathName1 As String
'--------------------------------------------------------------------------------------------------------------------------------
'XXXXXXXXXXXXXXX DRILL 2 XXXXXXXXXXXXXXX
'Input Box prompting user to choose the year
Year1 = Application.InputBox(prompt:="Enter the year in which you would like to obtain data", Title:="ENTER A YEAR BETWEEN 2014 and 2024", Default:=Year20)
If Year1 = "" Then
Range("A1").Select
Range("B7").Select
MsgBox "You have clicked CANCEL, no changes were made"
Exit Sub
End If
If Not IsNumeric(Year) Then
Range("A1").Select
Range("B7").Select
MsgBox "You have entered an invalid YEAR"
Exit Sub
End If
If IsNumeric(Year) Or Year1 = 20 Then
'JANUARY
Range("B7").Select
ActiveSheet.Range("B7") = PathName1
PathName1 = FilePath & Year1 & "\[" & Jan1 & " " & Year1 & ".xlsm]" & DRILL2
'Then the code selects the next appropriate cell and adjusts that file path for each month of the year for all 15 pieces of equipment
End Sub
