Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable File Name
We are using -
FileOpenName = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Open IFCAP Text File", , False) to prompt the user for the file to be opened. Later in our macro we want to switch from another workbook to this workbook, but can't figure out how to get the workbook name as a variable from the FileOpenName variable. The FileOpenName could be "C:\FOLDER1\FOLDER2\FNAME.xls." It could have multiple folders and the filename can vary. We can not see how to get the FNAME.xls extracted. We looked at Instr but couldn't see how to use it to get what we want. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable File Name
You can use the Split97 function for this if it must work in Excel 97-2003
See the example I use in this Zip sub to get the file name http://www.rondebruin.nl/zip.htm See the VBA help for Split if you use 2000 or higher -- Regards Ron de Bruin http://www.rondebruin.nl "Trevor" wrote in message ... We are using - FileOpenName = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Open IFCAP Text File", , False) to prompt the user for the file to be opened. Later in our macro we want to switch from another workbook to this workbook, but can't figure out how to get the workbook name as a variable from the FileOpenName variable. The FileOpenName could be "C:\FOLDER1\FOLDER2\FNAME.xls." It could have multiple folders and the filename can vary. We can not see how to get the FNAME.xls extracted. We looked at Instr but couldn't see how to use it to get what we want. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable File Name
What about:
newString = Mid(yourString, InStrRev(yourString, "\", , vbTextCompare) + 1) This will get everything after the last "\" or everything in the string, if no backslash is present. -Dan "Ron de Bruin" wrote: You can use the Split97 function for this if it must work in Excel 97-2003 See the example I use in this Zip sub to get the file name http://www.rondebruin.nl/zip.htm See the VBA help for Split if you use 2000 or higher -- Regards Ron de Bruin http://www.rondebruin.nl "Trevor" wrote in message ... We are using - FileOpenName = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Open IFCAP Text File", , False) to prompt the user for the file to be opened. Later in our macro we want to switch from another workbook to this workbook, but can't figure out how to get the workbook name as a variable from the FileOpenName variable. The FileOpenName could be "C:\FOLDER1\FOLDER2\FNAME.xls." It could have multiple folders and the filename can vary. We can not see how to get the FNAME.xls extracted. We looked at Instr but couldn't see how to use it to get what we want. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable File Name
Sure you can (in excel 2000 and later).
Another way is Public Function GetName(sFname As String) Dim i As Long, sFname1 As String, s As String Dim j as Long If InStr(sFname, "\") Then sFname1 = "" i = Len(sFname) j = 0 Do s = Mid(sFname, i, 1) If s < "\" Then j = j + 1 End If i = i - 1 Loop Until s = "\" sFname1 = Right(sFname, j) Else sFname1 = sFname End If GetName = sFname1 End Function This is slower, but should work in all versions. "DanOFin" wrote in message ... What about: newString = Mid(yourString, InStrRev(yourString, "\", , vbTextCompare) + 1) This will get everything after the last "\" or everything in the string, if no backslash is present. -Dan "Ron de Bruin" wrote: You can use the Split97 function for this if it must work in Excel 97-2003 See the example I use in this Zip sub to get the file name http://www.rondebruin.nl/zip.htm See the VBA help for Split if you use 2000 or higher -- Regards Ron de Bruin http://www.rondebruin.nl "Trevor" wrote in message ... We are using - FileOpenName = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Open IFCAP Text File", , False) to prompt the user for the file to be opened. Later in our macro we want to switch from another workbook to this workbook, but can't figure out how to get the workbook name as a variable from the FileOpenName variable. The FileOpenName could be "C:\FOLDER1\FOLDER2\FNAME.xls." It could have multiple folders and the filename can vary. We can not see how to get the FNAME.xls extracted. We looked at Instr but couldn't see how to use it to get what we want. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File name and Sheet name a variable. | Excel Discussion (Misc queries) | |||
Variable File Name | Excel Discussion (Misc queries) | |||
calling a value from another file using a variable in the file nam | Excel Worksheet Functions | |||
calling a value from another file using a variable in the file nam | Excel Discussion (Misc queries) | |||
Variable File Name | Excel Worksheet Functions |