UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Split File Path, Office 2010    
 
   
wornout
post May 6 2019, 05:38 PM
Post#1



Posts: 1,300
Joined: 17-November 13
From: Orewa New Zealand


I have file paths in column A of activeSheet
I want to take the last part of the file name and put it in the next column. the last part of the file name will always be after the last \
eg:C:\Users\Michelle\Desktop\ExcelPayroll\Sample Excel\Employee Manager\3 Excel Calendars.xlsm
I dont know how long the file path will be
Then I want to turn the filepaths in column A to hyperlinks
Go to the top of the page
 
wornout
post May 6 2019, 06:44 PM
Post#2



Posts: 1,300
Joined: 17-November 13
From: Orewa New Zealand


OK I have this code which works well
CODE
Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\Users\Michelle\Desktop\ExcelPayroll\Sample Excel\Employee Manager")
i = 1
'loops through each file in the directory
For Each objFile In objFolder.Files
'select cell
Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile
End Sub
Go to the top of the page
 
WildBird
post May 6 2019, 09:17 PM
Post#3


UtterAccess VIP
Posts: 3,560
Joined: 19-August 03
From: Auckland, Little Australia


So in the example, you want just
3 Excel Calendars.xlsm
?

Edit - Typo

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post May 6 2019, 09:23 PM
Post#4


UtterAccess VIP
Posts: 3,560
Joined: 19-August 03
From: Auckland, Little Australia


Could try something like this.

CODE
Function getFileName(ByVal strFullPath As String) As String

If InStr(1, strFullPath, "\") > 1 Then
    getFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
End If

End Function



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
dflak
post May 7 2019, 01:57 PM
Post#5


Utter Access VIP
Posts: 6,283
Joined: 22-June 04
From: North Carolina


My candidate solution is
CODE
Function RtnFileName(Filename As String) As String
Dim FullName() As String

FullName = Split(Filename, "\")

RtnFileName = FullName(UBound(FullName))
End Function

Just to prove that in Excel there is usually more than one way to do things.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 04:30 PM