Deleting Characters From A Word File
Nov 10, 2006
I'm trying to automate a redundant task. I have several Word files in one central folder. I need to open each file, remove all paragraph marks (via find/replace all), copy the result, and paste it into an Excel sheet. This process will be repeated for each file in the folder.
The code I have is almost there, however, I can't get it to do the find/replace. Below is an extract of my code for the Word application part. The code for the copy and paste will be inserted after the replace action works:
Dim Wapp As Object
Dim i As Integer
WorksheetsLocation = TextBox1.Value ' this is the folder location brought in from an input box
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number 0 Then 'Word isn't already running
Set wApp = CreateObject("Word.Application")
End If
On Error GoTo 0
View 9 Replies
ADVERTISEMENT
Jan 2, 2009
I have a column that has all the digits in the phone number. What I would like to do is remove the first 8 characters example 111-222-3333. I want to remove all the ones dash twos and dash. Can someone help me please ?
View 7 Replies
View Related
May 15, 2012
Im trying to write a macro to delete the last 5 characters of a cell,
Its the whole column to which i need to apply this to
View 8 Replies
View Related
Apr 8, 2014
Let's say I have these 4 rows
1231-123 Lady Gaga - Applause
3123 123 -- Marvin Gaye - SEduction
1-20389@---12 Beyonce - Crazy in Love
-123 -- Andrea Bocelli - whatever
Is there any way to make Excel delete every character that is not found withing the alphabet until it find the first letter and STOP there ?
View 11 Replies
View Related
Dec 16, 2009
cell A1 contains:
001001 28 EA 12 CATALOG #: 9358171
I would like to get all the characters in between the word EA and CATALOG and put them in B1 and so on....
View 2 Replies
View Related
Mar 21, 2007
1. I am trying to remove some unwanted characters in the cells. I have in column A a list of stock counters preceeded by their tickers e.g.
1D42 - CM TEL
1G47 - CSE GLOBAL
Prior to exploring VBA, i was manually doing mid(A1,8,20) to remove those characetrs before the actual name starts but as my list goes on indefinitely, the numbers grow and now i have a few thousand stocks in my portfolio. Also, for some names i cannot use mid(A1,8,20) anymore if the tickers are longer than 5 or 6 characters or are simply shorter.
So now I am trying to see what code will enable me to remove all characters before the actual name i.e one character space right after the Hypen. The hypen only appears ONCE in the cell as the names will not contain them.
2. In addition, after all is removed, what is the code that will be needed to do an automatic Sort of the data corresponding to the stocks (From column A to K) based on alphabetical order?
View 6 Replies
View Related
Dec 12, 2012
I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.
Sub test()
Dim r As Range
With ActiveSheet[code]....
The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.
How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?
View 3 Replies
View Related
May 29, 2006
I had alot of cells that has replication in the same cell. I would like to find out if the cell has any replication. If there is, delete the repeated data if not go to next cell
Example,
Before
Peter, John
Peter, John, John
Peter, John, Peter
John, John, John
Peter, Peter, Peter
After
Peter, John
Peter, John
Peter, John
John
Peter
[edit] The example above is 1 cell per line [/edit]
View 9 Replies
View Related
Jun 11, 2012
I'm scanning data into a spreadsheet using a barcode scanner, but I want the starting character and trailing characters to be removed after I hit enter.
For example...let's say I'm scanning the following text in cell A1:
=W05281212345600
When I hit enter (or the barcode scanner does auto-enter), I want cell A1 to read:
W052812123456
I will always want the "=" and the last two zeros removed from the 16 character number, leaving the 13-character number in its place.
I tried using Excel's various truncate functions (LEFT,RIGHT,MID, etc.), but that only places the corrected text in another cell. That would be fine if I could copy that truncated text back over the original text...but that created a loop problem.
View 1 Replies
View Related
Apr 2, 2014
I have a excel file with more then 10 sheets..Some of the sheets contains this word in some random cells" #DIV/0! " I want a macro which can find it in every sheet except parent sheet and can remove it.
View 5 Replies
View Related
Jun 21, 2007
I am trying to create a search function in a database I have made. I have a userform with drop down boxes that people can choose what they want to search for. Once they have chosen and clicked the search button I want it to search only in the relevant column, i.e. search for the word in the dropdown for "engine" only in the "engine" column of the database. Once it has found the appropriate cells with the answer, I want it to delete all other rows that don't include the answer.
Once it's done this it needs to do it again for the other fields, unless they are empty. I have used 'If engine_type <> "" Then' as the starting point so that it only searches chosen fields.
View 4 Replies
View Related
Sep 17, 2012
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
View 1 Replies
View Related
Jun 14, 2007
I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.
View 3 Replies
View Related
Jul 29, 2009
if you can remove the word total from each cell without deleting the numbers after you copy from a subtotal list?
View 9 Replies
View Related
Mar 25, 2014
I have to create a word file that has about 300 pages containing stats for about 400 different statistical indicators. It has to be updated on a monthly basis
The file is populated using data from an excel spreadsheet.
Is it possible to put links to the spreadsheet in the word document so that the I can update the spreadsheet and have the word document automatically update itself?
View 1 Replies
View Related
May 30, 2007
I have and xls list that needs to be saved in a text format without the use of "". The data needs to be read as data not as "data". The only way I have been able to do this is to save the file as a .prn and then rename the extension to .txt. My problem is that in order to rename the prn file i need to exit the file. If I do that then the macro ends and it will not rename. So I have worked around that by saving a second copy and then renaming the first copy. But I do not want to have a second copy. any ideas on how to get around this or to delete the scond copy from within the macro?
View 9 Replies
View Related
Apr 10, 2007
I have a file about 13 Megs. I deleted a lot of data and saved it. It was 1 Meg bigger. I closed it, reopened, saved again. 700 k smaller than before, but still bigger than original. That is strange. There must be a way to get the dirt out.
View 9 Replies
View Related
Jan 11, 2008
I have a template that when it is opened it will automatically save the file to a specific location with the date in the file name.
Currently my code is
Private Sub Workbook_Open()
ActiveWorkbook.SaveAs "G:Shared"IT_New " & Format(Date, "mm-dd-yy") & ".xls", , , , , , xlShared
End Sub
Does anyone know a way to have this code disabled (either by deleting or making it all a comment) on the saved file (xls) so the speadsheet does not try and overwrite itself when I go back and open a previous days spreadsheet?
View 9 Replies
View Related
May 17, 2014
get the name of the File in a Vlookup VBA using wildcard characters. I have to do a VLOOKUP from a specific file which is stored in the same folder as the file in which I need to get this VLOOKUP done, however the name can change anytime keep a word common only for this specific file...
Ex:
I have a File called "Mapping as on 8th May" from where i need to lookup some information for my file and I intend to do this using VLOOKUP in VBA in a Module but since the name can change to anything such as"Mapping as on 16th May" I cant use it in the VBA code, so is there a way around this where If I have this file stored in the same folder then I keep the word "Mapping" specific and unique for all other files in the same folder that the search can pull this file name to be further used in my lookup..
how to use the wildcards in VLOOKUP lookup range for the file name as the columns are going to remain the same...
View 13 Replies
View Related
Nov 26, 2008
I have a number of project files that are maintained on the network.
These files are named "XXXXXX Project Name.XLS".
XXXXXX is the project number. This numbers are unique, so there is only one file per number within the directory. Project names are mostly unique, but sometimes they could be same for a specific site.
We recieve financial data in excel file format from home office. These reports reference only the project numbers, not the name.
I have created an excel program that extracts project numbers and related financial data from the report [from home office].
I would like to create a button or check box next to each project number in my program and link it to the respective file on our network. Once I access the local file on the network I could copy the financial data in its right place.
The problem is opening the file based on project number only, while the file names contain Project Name as well.
So, can I look up a file by only searching for first six characters of the name?
Or can the Hyperlink function work with some sort of wildcard? I can write the formulas to create the hyperlink and then write a macro to look for that file, but I need to be able to find and open the file by looking up only the first six characters.
View 9 Replies
View Related
Aug 11, 2006
I'm trying to find a way to save a file based on the contents of cell A2. I've modified the code to Create Worksheets for Each Item in an Excel Table of Data, so that it creates a new workbook for each item. I am also trying to get it to save the master workbook based on the first 2 characters in the cell. Here is the code I have, at the moment (the FAIL.xls is what I put in for testing)
If Range("A2").Value = "YE*" Then
ActiveWorkbook.SaveAs "YE_Master.xls"
ElseIf Range("A2").Value = "NE*" Then
ActiveWorkbook.SaveAs "NE_Master.xls"
Else: ActiveWorkbook.SaveAs "FAIL.xls"
End If
I've tried this, string arrays and everything else I can think of, and it's failing every time.
View 3 Replies
View Related
Dec 7, 2006
Remove Illegal Characters From File Name ...
View 5 Replies
View Related
Jan 9, 2008
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
View 9 Replies
View Related
May 30, 2006
I am trying to figure out how to create a Macro code in Excel that will delete text file page breaks. Each page break starts with a square in column A and ends with the word continued in column D. I've tried several times, but when I test the code and I highlight the section to delete, it only deletes those specific rows next time. How can I get it to delete the rows from the square to "Continued" throughout the document without making reference to specific cell numbers?
View 3 Replies
View Related
May 28, 2008
I have a Text-File with characters coded in simple chinese (gb2312) rsp. codepage 20936 and I want to read this file, paste the content in my worksheet. Later (after a chinese edited the text) I've to write this back to a text file.
The only way I found to make this, is by copy/paste e.g. from WordPad, but I want to automate it with VBA.
View 6 Replies
View Related
Feb 26, 2007
What is the script in order for me to link a MS Word file to excel. What I intend to do is to customise the commandbutton, so that once a user click on it, the linked MS Word file (stored in the local drive) will pop-up and he/she can do the necessary amendments to the file?
View 3 Replies
View Related
Jun 7, 2007
I would like to open a word file in my Excel VBA code. The word document has its own VBA code that will run when Excel opens it. I do not need to move any data around I just want Excel to open my word doc.
View 4 Replies
View Related
Apr 30, 2014
As you can see, in D5, I have the charactor chain SRG-DC01-RA-xxxxxxx, and in D6 I have the charactor chain SRG-DC43-RA-xxxxx.
With the formula that I have entered, I would have thought that E5 would have generated "Lawnton". I don't want the formula to pick up the SRG, the RA, or the number, just the DCXX component.
This is what I have now:
=IF(OR(ISNUMBER(SEARCH({"DC43","DC01"},D5))),"Erskine Park","Lawnton")
which has not changed a thing from:
=IF(OR(ISNUMBER(SEARCH({"DC43"},D5))),"Erskine Park")
Surely I am not far off the mark??
View 1 Replies
View Related
Dec 5, 2012
I run excel 2010... I have xls file (see file attached) with both English and Non English characters.
When I save this file as xls or xlsx - everything is good, but when I save the file as CSV and try to open it later - I see that the English characters stays the same but the non English characters become gibberish.
How can I save a file (that include some non English characters) as CSV without loosing the non English characters? Is there a way to do that from within the excel 2010 menus? Or maybe there is an external tool?
Attached file : 913365454523.xls
View 1 Replies
View Related
Aug 6, 2008
I have a very large file of data, over 500,000 rows, opening in one sheet in Excel is not an option with my current version. In each row I need to change the characters in positions 41-44 from whatever they current are, to '9999'. I'm sure there has to be a way I can do this using vba, does anyone have a sample snippet of code, or another post they can point me too?
View 9 Replies
View Related