6/14/2023 0 Comments Vba get file path from filename![]() The formula logic is the same as explained above, but the CELL function is used just once. With LET, the formula above can be streamlined somewhat by declaring and defining a "path" just one time like this: =LET(path,CELL("filename",A1),LEFT(path,FIND("[",path)-1)) In Excel 365, the LET function makes it possible to declare and assign variables inside a formula. The CELL function is called twice in the formula because we need the path twice, once for the FIND function to locate the opening square bracket ("["), and once for the LEFT function to extract all text before the "[". The LEFT function returns the first 12 characters of text as the final result: C:\examples\ The text argument is again provided by the CELL function as described above: =LEFT(CELL("filename",A1),12) This number is returned directly to the LEFT function as the num_chars argument. In the previous step, we located the "]" at character 27, then stepped back to 12. Or, to put it the other way, we want to extract all text up to the "[". We subtract 1 because we want to remove all text starting with the "[" that precedes the workbook name. The FIND function returns the location of "[" (13) from which 1 is subtracted to get 12. The location of the opening square bracket ("",CELL("filename",A1))-1 // returns 12 Note the sheet name (Sheet1) appears at the end, and workbook name appears inclosed in square brackets, . In the example shown, the formula in E5 is: LEFT(CELL(filename,A1),FIND(,CELL(filename,A1))-1) The result is a path without the filename like this. The result is a full path like this as text: C:\examples\Sheet1 The cell reference is arbitrary and can be any cell in the worksheet. The info_type argument is "filename" and reference is A1. To get the path and file name, we use the CELL function like this: CELL("filename",A1) // get path and filename Extract all text up to the opening square bracket ("[").Locate the opening square bracket ("[").The formula used to perform this task appears in cell E5: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)Īt a high level, this formula works in 3 steps: I notice that you use StartRow:=2 in the import (which will exclude the first row/line of a file) but I'd suggest StartRow:=1 so you get the whole file (stated in A1) unless all your files have common headers.In this example, the goal is to get the workbook path without workbook name. To print the workbooks full path, use the following code in VBA: ActiveWorkbook.Path This command will return the full path of the active workbook. ![]() I've used string manipulation to extract just the actual file name (but the adjacent commented-out line gives the full path, if that's what you want). 'wsMaster.Range("A1").Value = fileToOpen 'OR use the full path and filenameĮnd SubThe application requestor GetOpenFilename actually delivers your string variable fileToOpen as the full path to the chosen file. WsMaster.Range("A1").Value = Right(fileToOpen, Len(fileToOpen) - InStrRev(fileToOpen, "\")) ' add filename Set wsMaster = ThisWorkbook.Worksheets("Raw") There are just a few changes to your code (in bold- with alternatives for A1 contents): Sub ImportCSVorTXT()įileFilterPattern = "Text Files (*.txt *.csv), *.txt *.csv"įileToOpen = Application.GetOpenFilename(fileFilterPattern) ![]() Your sub is not named above (and please use the " Add Link" button above in future when supplying code) so I have used the modified code below to add a file name to A1 of worksheet Raw and paste the file contents from A2 down (as per your question).
0 Comments
Leave a Reply. |