Excel help!
Author
Discussion

anonymous-user

Original Poster:

85 months

Tuesday 18th December 2018
quotequote all
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

louiebaby

11,022 posts

222 months

Tuesday 18th December 2018
quotequote all
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.


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
Chuck me a PM with the actual workbook,

I'll have a look for you.

utgjon

713 posts

204 months

Tuesday 18th December 2018
quotequote all
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


toon10

7,152 posts

188 months

Tuesday 18th December 2018
quotequote all
Assuming LOCATION is a valid path and you have access to it, as utgjon suggests, you may just need to add the extra slash

anonymous-user

Original Poster:

85 months

Wednesday 19th December 2018
quotequote all
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 smile

louiebaby

11,022 posts

222 months

Wednesday 19th December 2018
quotequote all
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 smile
No problem, I'm glad someone else managed to help sort it out.

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. wink


anonymous-user

Original Poster:

85 months

Wednesday 19th December 2018
quotequote all
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 smile

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.