Help in Getting the File Name from a path
I have created the following macros and function to import a file and then
extract just the path name. Problem is that the function returns the entire path name and not just the file mane. The importation of the file works fine. Below is the code I wrote, any suggestions for a fix? Dim myFileName As Variant Sub ImportRunData() ImportDisplayRDFile stFullName = myFileName GetFileName (stFullName) End Sub ---------------------------------------------- Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") If myFileName = False Then Exit Sub 'user hit cancle End If Sheets("Sheet1").Select With ActiveSheet With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) .Name = "Pick Place for JRB001078B DDU" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("E3").Select End With End Sub ---------------------------------------- Function GetFileName(stFullName As String) As String 'GetFileName returns the file name from the end of a full path namme 'stFullName is returned if no path seperator is found Dim stPathSep As String 'Path Separator Character Dim iFNLength As Integer 'Length of stFullName Dim i As Integer stPathSep = Application.PathSeparator iFNLength = Len(stFullName) stFullName = myFileName 'Find last path separator character, if there is one For i = iFNLength To 1 Step -1 If Mid(stFullName, i, 1) = stPathSep Then Exit For Next i GetFileName = Right(stFullName, iFNLength - 1) Range("C5") = GetFileName End Function ----------------------------------------------------------------- |
Help in Getting the File Name from a path
Function GetFileName(stFullName As String) As String
GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function -- __________________________________ HTH Bob "Tom" wrote in message ... I have created the following macros and function to import a file and then extract just the path name. Problem is that the function returns the entire path name and not just the file mane. The importation of the file works fine. Below is the code I wrote, any suggestions for a fix? Dim myFileName As Variant Sub ImportRunData() ImportDisplayRDFile stFullName = myFileName GetFileName (stFullName) End Sub ---------------------------------------------- Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") If myFileName = False Then Exit Sub 'user hit cancle End If Sheets("Sheet1").Select With ActiveSheet With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) .Name = "Pick Place for JRB001078B DDU" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("E3").Select End With End Sub ---------------------------------------- Function GetFileName(stFullName As String) As String 'GetFileName returns the file name from the end of a full path namme 'stFullName is returned if no path seperator is found Dim stPathSep As String 'Path Separator Character Dim iFNLength As Integer 'Length of stFullName Dim i As Integer stPathSep = Application.PathSeparator iFNLength = Len(stFullName) stFullName = myFileName 'Find last path separator character, if there is one For i = iFNLength To 1 Step -1 If Mid(stFullName, i, 1) = stPathSep Then Exit For Next i GetFileName = Right(stFullName, iFNLength - 1) Range("C5") = GetFileName End Function ----------------------------------------------------------------- |
Help in Getting the File Name from a path
Function GetFileName(strFullName As String) As String
GetFileName = Mid(strFullName, InStrRev(strFullName, Application.PathSeparator) + 1) End Function -- If this post helps click Yes --------------- Jacob Skaria "Tom" wrote: I have created the following macros and function to import a file and then extract just the path name. Problem is that the function returns the entire path name and not just the file mane. The importation of the file works fine. Below is the code I wrote, any suggestions for a fix? Dim myFileName As Variant Sub ImportRunData() ImportDisplayRDFile stFullName = myFileName GetFileName (stFullName) End Sub ---------------------------------------------- Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") If myFileName = False Then Exit Sub 'user hit cancle End If Sheets("Sheet1").Select With ActiveSheet With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) .Name = "Pick Place for JRB001078B DDU" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("E3").Select End With End Sub ---------------------------------------- Function GetFileName(stFullName As String) As String 'GetFileName returns the file name from the end of a full path namme 'stFullName is returned if no path seperator is found Dim stPathSep As String 'Path Separator Character Dim iFNLength As Integer 'Length of stFullName Dim i As Integer stPathSep = Application.PathSeparator iFNLength = Len(stFullName) stFullName = myFileName 'Find last path separator character, if there is one For i = iFNLength To 1 Step -1 If Mid(stFullName, i, 1) = stPathSep Then Exit For Next i GetFileName = Right(stFullName, iFNLength - 1) Range("C5") = GetFileName End Function ----------------------------------------------------------------- |
Help in Getting the File Name from a path
Bob,
Thank you for the help, it worked just fine. "Bob Phillips" wrote: Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function -- __________________________________ HTH Bob "Tom" wrote in message ... I have created the following macros and function to import a file and then extract just the path name. Problem is that the function returns the entire path name and not just the file mane. The importation of the file works fine. Below is the code I wrote, any suggestions for a fix? Dim myFileName As Variant Sub ImportRunData() ImportDisplayRDFile stFullName = myFileName GetFileName (stFullName) End Sub ---------------------------------------------- Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") If myFileName = False Then Exit Sub 'user hit cancle End If Sheets("Sheet1").Select With ActiveSheet With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) .Name = "Pick Place for JRB001078B DDU" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("E3").Select End With End Sub ---------------------------------------- Function GetFileName(stFullName As String) As String 'GetFileName returns the file name from the end of a full path namme 'stFullName is returned if no path seperator is found Dim stPathSep As String 'Path Separator Character Dim iFNLength As Integer 'Length of stFullName Dim i As Integer stPathSep = Application.PathSeparator iFNLength = Len(stFullName) stFullName = myFileName 'Find last path separator character, if there is one For i = iFNLength To 1 Step -1 If Mid(stFullName, i, 1) = stPathSep Then Exit For Next i GetFileName = Right(stFullName, iFNLength - 1) Range("C5") = GetFileName End Function ----------------------------------------------------------------- |
All times are GMT +1. The time now is 06:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com