| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ----------------------------------------------------------------- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------------- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------------- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| File Path Too Long? Not Anymore! Check out Long Path Tool | Excel Discussion (Misc queries) | |||
| http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
| Creating Excel file that points to relative path .cub file | Excel Programming | |||
| Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
| How set file open path to filepath of file opened with Explorer ? | Excel Programming | |||
Linear Mode
