i'm struggling with a formula to extract the folder names from a file path string i have in col A. I want to take the path value and for each "node" in the path place that string value in cols
Example string:
"pathfolder01subfolder2folder level 3level 4 folderanother folder for 5sublevel 06 folder"
In my example there are 6 folder levels in the path. I want a formula to "strip out" each level of the path string and put it in a seperate column.
In the attached excel, I will be putting a string (Which will be a result of another formula) and there will be a table in column A & B(Length of the table will vary).
What I need is a formula to search entire column B and get the file names which is having that string and also the corresponding path
Table E6 to F9 contains the desired result (The result wont be more than 3 at any point of time
I am creating an excel file for a mail merge following this process. I need to take the three columns below and enter the path to the corresponding chart. I have all the charts created in a directory. The naming convention for all of the files is XX-XX-XX.gif. The X's represent the numbers below without the decimal places. Therefore the example below would be 44-43-27.gif. Is there anyway to create a formula to drag down instead of entering the filename's manually? In the end, I want to have the full file path as shown in the XLS.
q_3_abc q_3_teamq_3_yscore 4.4 4.3 2.7
I have 700+ rows to do this for, therefore I want a formula or VBA solution instead of the manual donkey work.
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be; S:BryanFor KenGulf ConstructionST0609014-t.xls (the file name length may vary) What code can I use to consistently trim it back to; S:BryanFor KenGulf Construction as the "DestFldr" variable?
I would like to break the string apart so I have only account # in column "a" and the right most sub account name in column "b". So if I use my example from above, I would have 70160 in "a" & Travel & Ent.-Travel & Lodging in "b". Note all the account name & sub accounts are separated by ":"
The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
I have a spreadsheet that has a column of text that is always 10 characters long. There are 10 rows of text so there could be 100 text characters if all rows are filled. The rows usually will not all be filled. There will probably be blank rows between used rows. I have been able to capture the text and put it into one cell as one long text which is ok but I want to be able to break it up into the 10 character strings again, separated with a comma and space between each 10 characters.
This is the code I used to collect the 10 character text strings and put them all together as the variable "result". I used & ", " after ... Cells(r,17) which worked fine unless there was a blank row. If the row was blank it put in a comma and space anyway so I ended up with duplicate(triplicate) commas and spaces.
Private Sub test_Click() result = "" For r = 5 To 32 Step 3 'If Cells(r, 17) "" Then result = result & Cells(r, 17) Next r Range("r5") = result End Sub
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath 'controle = Dir(pad$ + "FactuurNummer7.txt") 'If controle = "" Then GoTo EerstAanmaken 'Open pad$ + "Factuurnummer7.txt" For Input As #10 'Input #10, Nummer1 'Close #10......................
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1() Dim fs As Object Dim oldPath As String, newPath As String oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save Set fs = CreateObject("Scripting.FileSystemObject") fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls" Set fs = Nothing End Sub
I just found a code to copy file path and file contents. However it is copying folder path and folder contents.here is my requirement.I will specify a path, macro has to copy that file name in a particular cell, then it has to copy all its contents. ex:
file name 1 has 3 sub files in it, file name 2 has 2 sub files in it--
I need to set the page break in my excel file. Basically what I need is for vba to check if the page breaks are a certain value (in this case Range("A150"). If they are not, then the code will set them to what I need.
The problem is the code is not working.
Code:
If ActiveSheet.HPageBreaks(3).Location Range("A150") Then Set ActiveSheet.HPageBreaks(3).Location = Range("A150") End If
I copied 2 worksheets from one file to another and went in to "Edit Links" and changed the source to itself rather than the original file which took away those links in the cells that the original file was present in, but it still gives me a dialogue when opening the file that links to the original file. I go back to "Edit Links" and press "Break Link" but it doesn't seem to remove the link to the original file. If I click on "Connections", none are present. I clicked on "Startup Prompt" and set it so that it wouldn't annoy me with the prompt any more, but I'd ideally like to not have any reference to the original file.
Last week I copied a worksheet from one spreadsheet into another, and have since been working from the new one. When I open the file I get a message box asking if I want to Update links from the other workbook. But I've deleted all named ranges, formulae, shapes, everything that could possibly link to the other workbook. I've also (in my frustration) completely deleted the sheet that was originally copied and re-made it from scratch. But it STILL comes up with this update box. In Excel's Help it says to go to Edit/Links and press Break Link, which I have tried but it doesn't do a thing. The link is still sitting there in the display box in the Links window - it says that the Type is Worksheet, I don't know if this makes any difference that it's not a formula or anything?
Also, in complete desperation when it wasn't deleting the link, I changed the source to a random file, so it now comes up with an error in updating (which it obviously would) however this doesn't have any effect on my file because as far as I can tell it no longer has links to the other file! I'm tempted to just go to the option that says don't show the Update prompt on opening (the file is to be handed in as coursework on Wednesday) but this doesn't solve the problem, only bypasses it! Each time I made a major change (e.g. recreating the copied worksheet, changing the source) I saved the file under a different name, so I have all old copies just incase I've completely messed it up!
I am herewith enclosing the sample data, which is one row, containing the branch name, branch code, itemcode, rate, qty, amount . Each branch has several items with different quantity of various rates, which has come in the horizontal line. Now I want that in one by one, for converting that data into oracle.
BR.NAME BR.CODEITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTxx1104.5100450153250750263.25158513.5394.5180810506.55003250result should be like thisxx1104.5100450xx2153250750xx3263.25158513.5xx4394.5180810xx5506.55003250
Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).
For example (Quater 1 is the document name) R:ManagementJohn2008SalesSectionAQuater1
I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.
I am trying to separate a filename from a filepath in Excel VBA 2003.
I am using the Application. GetOpenFilename command to get the user to select the correct file. However, this also seems to record the filepath.
I am trying to separate the filename out of this so that each file( name) activates a different sub procedure. I am storing the result of the getopenfilename as a Variant.
At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.
I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.
Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know.
How can I get the name of a workbook WITHOUT the path when I have the name WITH the path?
To be more specific. When I use "Application. GetOpenFilename" the result is a file name for say "my_book" including the path. With this I can open the file using "Workbooks.Open" and Excel will show me "my_book". But when I want to reference this workbook later on in say "Workbooks(my_book)", then I need the file name without the path. How can I get that one in VBA?
Till now I was using "CurDir" to find out the path after opening the file and just subtracted it from the full file name. This seamed to be fine. But now I had a situation where CurDir gave me a path with abbreviations (~),
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I have a WinForm, which I'm calling via VBA's Shell command:
Code: Sub Button1_Click() Shell "C:TemplateFuncitons.exe " + ActiveWorkbook.FullName, 1 End Sub
I made this module an Excel Add-in and added it as a button in the quick access toolbar. Now, the idea is for the WinForm.exe and the Excel.xla to always be in the same directory (go hand in hand). I want to make a relative path for the .exe which will be the path to the folder where the .xla is + the string "TemplateFunctions.exe" in the end.
I am having trouble with a macro of mine. Essentially it opens a file, based on user input (Full path is stored in variable FilePath), but then I want to select that workbook, and therefore need just the .xls filename. I figured I would create a new variable, FileName, to pull out the filename.
How can I edit the string in my macro to extract just the filename.xls from the full path?