Discussion
I am using this macro to link a button on a spreadsheet to auto create a hyperlink and file location, I cant for the life of me figure out why I am getting a path not found error on the highlighted line, it then moves down each line and the final message runs.
Sub New_Track_Number()
'
' New_Track_Number Macro
'
' 1st step: check that Track numbering is up to date
Sheets("Track numbering").Select
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:A1000")
' 2nd step: remove filter
Sheets("Issues").Select
Selection.Autofilter
Rows("4:4").Select
Selection.Autofilter
' 3rd step: positioning at the bottom of the list
Range("A3").Select
Selection.End(xlDown).Offset(1, 0).Select
' 4th step: copy paste (in value) of the current track number
Sheets("Track numbering").Select
Range("E2").Select
Selection.Copy
Sheets("Issues").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'5th step: add link and hide it
Worksheets(1).Hyperlinks.Add Anchor:=Selection, Address:="LOCATION" & ActiveCell
'6th step: create topic folder and subfolder for email hytory
Dim Ord1 As String
Dim Ord2 As String
Dim Ord3 As String
Ord1 = "LOCATION" & ActiveCell
If Dir(Ord1, vbDirectory) <> "" Then GoTo 1
MkDir Ord1
Ord2 = Ord1 & "\Important Emails"
MkDir Ord2
Ord3 = Ord1 & "\Files"
MkDir Ord3
1 MsgBox "A Folder structure has been created. Please click on the link in the tracker to open your topic folder!"
End Sub
Sub New_Track_Number()
'
' New_Track_Number Macro
'
' 1st step: check that Track numbering is up to date
Sheets("Track numbering").Select
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:A1000")
' 2nd step: remove filter
Sheets("Issues").Select
Selection.Autofilter
Rows("4:4").Select
Selection.Autofilter
' 3rd step: positioning at the bottom of the list
Range("A3").Select
Selection.End(xlDown).Offset(1, 0).Select
' 4th step: copy paste (in value) of the current track number
Sheets("Track numbering").Select
Range("E2").Select
Selection.Copy
Sheets("Issues").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'5th step: add link and hide it
Worksheets(1).Hyperlinks.Add Anchor:=Selection, Address:="LOCATION" & ActiveCell
'6th step: create topic folder and subfolder for email hytory
Dim Ord1 As String
Dim Ord2 As String
Dim Ord3 As String
Ord1 = "LOCATION" & ActiveCell
If Dir(Ord1, vbDirectory) <> "" Then GoTo 1
MkDir Ord1
Ord2 = Ord1 & "\Important Emails"
MkDir Ord2
Ord3 = Ord1 & "\Files"
MkDir Ord3
1 MsgBox "A Folder structure has been created. Please click on the link in the tracker to open your topic folder!"
End Sub
Lord.Vader said:
I am using this macro to link a button on a spreadsheet to auto create a hyperlink and file location, I cant for the life of me figure out why I am getting a path not found error on the highlighted line, it then moves down each line and the final message runs.
Chuck me a PM with the actual workbook,Sub New_Track_Number()
'
' New_Track_Number Macro
'
' 1st step: check that Track numbering is up to date
Sheets("Track numbering").Select
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:A1000")
' 2nd step: remove filter
Sheets("Issues").Select
Selection.Autofilter
Rows("4:4").Select
Selection.Autofilter
' 3rd step: positioning at the bottom of the list
Range("A3").Select
Selection.End(xlDown).Offset(1, 0).Select
' 4th step: copy paste (in value) of the current track number
Sheets("Track numbering").Select
Range("E2").Select
Selection.Copy
Sheets("Issues").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'5th step: add link and hide it
Worksheets(1).Hyperlinks.Add Anchor:=Selection, Address:="LOCATION" & ActiveCell
'6th step: create topic folder and subfolder for email hytory
Dim Ord1 As String
Dim Ord2 As String
Dim Ord3 As String
Ord1 = "LOCATION" & ActiveCell
If Dir(Ord1, vbDirectory) <> "" Then GoTo 1
[i]MkDir Ord1[/i]
Ord2 = Ord1 & "\Important Emails"
MkDir Ord2
Ord3 = Ord1 & "\Files"
MkDir Ord3
MsgBox "A Folder structure has been created. Please click on the link in the tracker to open your topic folder!"
End Sub
I'll have a look for you.
Using the following changes to the filepaths, I managed to get it to work.
Sub New_Track_Number()
'
' New_Track_Number Macro
'
' 1st step: check that Track numbering is up to date
Sheets("Track numbering").Select
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:A1000")
' 2nd step: remove filter
Sheets("Issues").Select
Selection.AutoFilter
Rows("4:4").Select
Selection.AutoFilter
' 3rd step: positioning at the bottom of the list
Range("A3").Select
Selection.End(xlDown).Offset(1, 0).Select
' 4th step: copy paste (in value) of the current track number
Sheets("Track numbering").Select
Range("E2").Select
Selection.Copy
Sheets("Issues").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'5th step: add link and hide it
Worksheets(1).Hyperlinks.Add Anchor:=Selection, Address:="C:\users\***MYUSERNAME***\Documents\Test" & ActiveCell & "\"
'6th step: create topic folder and subfolder for email hytory
Dim Ord1 As String
Dim Ord2 As String
Dim Ord3 As String
Ord1 = "C:\users\***MYUSERNAME***\Documents\" & ActiveCell & "\"
If Dir(Ord1, vbDirectory) <> "" Then GoTo 1
MkDir Ord1
Ord2 = Ord1 & "\Important Emails"
MkDir Ord2
Ord3 = Ord1 & "\Files"
MkDir Ord3
1 MsgBox "A Folder structure has been created. Please click on the link in the tracker to open your topic folder!"
End Sub
Sub New_Track_Number()
'
' New_Track_Number Macro
'
' 1st step: check that Track numbering is up to date
Sheets("Track numbering").Select
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:A1000")
' 2nd step: remove filter
Sheets("Issues").Select
Selection.AutoFilter
Rows("4:4").Select
Selection.AutoFilter
' 3rd step: positioning at the bottom of the list
Range("A3").Select
Selection.End(xlDown).Offset(1, 0).Select
' 4th step: copy paste (in value) of the current track number
Sheets("Track numbering").Select
Range("E2").Select
Selection.Copy
Sheets("Issues").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'5th step: add link and hide it
Worksheets(1).Hyperlinks.Add Anchor:=Selection, Address:="C:\users\***MYUSERNAME***\Documents\Test" & ActiveCell & "\"
'6th step: create topic folder and subfolder for email hytory
Dim Ord1 As String
Dim Ord2 As String
Dim Ord3 As String
Ord1 = "C:\users\***MYUSERNAME***\Documents\" & ActiveCell & "\"
If Dir(Ord1, vbDirectory) <> "" Then GoTo 1
MkDir Ord1
Ord2 = Ord1 & "\Important Emails"
MkDir Ord2
Ord3 = Ord1 & "\Files"
MkDir Ord3
1 MsgBox "A Folder structure has been created. Please click on the link in the tracker to open your topic folder!"
End Sub
Lord.Vader said:
Here, I wonder why it suddenly stopped working though?!
Anyways thanks for the help and thanks for the offer louie but unfortunately it is full of military / space data and cant be shared
No problem, I'm glad someone else managed to help sort it out.Anyways thanks for the help and thanks for the offer louie but unfortunately it is full of military / space data and cant be shared

There are a number of reasons why the VBA code you have might fail. It has a whole load of "Select" and "Selection" related bits that are symptomatic of using the recorder to make the Macro. There are a lot of hard coded cell references that will fail if the material of the spreadsheet changes, such as adding a row or column. It is also written to only have capacity for about 1000 lines of data as well.
As a mildly competent VBA user, the thought that military / space data is handled in such a way is mildly frightening. I would be happy to come in and help sort this out, on standard government contractor rates, obvs.

This is just something I borrowed from one of our other teams.
It is only an internal tracker (which I hope never hits the 1000 lines) to record links to investigations where the files, data, emails, etc are stored on a secure server then they are also also stored securely online, etc, where the "proper" investigation is recorded. Nothing for the government
I am useless at excel and now I have buggered up the insert a new line aspect, so it creates a folder at location but doesn't insert a line! Never mind, it doesn't really need it anymore.
It is only an internal tracker (which I hope never hits the 1000 lines) to record links to investigations where the files, data, emails, etc are stored on a secure server then they are also also stored securely online, etc, where the "proper" investigation is recorded. Nothing for the government

I am useless at excel and now I have buggered up the insert a new line aspect, so it creates a folder at location but doesn't insert a line! Never mind, it doesn't really need it anymore.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


