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 RepliesI'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.
Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)
Useful information are included !!!
I'll give an example of a record...
Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "
Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...
Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:
Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function
Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs
40m20secs
2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
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.
Is it possible to find the location of different characters in a string (using VBA)? Normally I would use InStr of InStrRev option in VBA but my situation is a little more complicated.
The character I am looking for is not always the same, sometimes it is a , or a . or a : or a "blank" etc
I tried to do it like:
dim strChar as string
strChar = "[,.? /]"
SearchPosition = InStr ("cell location", strChar) (searchposition is the name of the function I am trying to make)
This works if I define only 1 character, this way it gives me a 0 as outcome
I have tried to change it to strChar = "[,]" or strChar = "[.]" or strChar = "[ ]" but this doesn't work.
How can I delete characters unallowed in file names in cell text all in one go instead of below long macro. (i.e. can you minimize the below vba)
These characters that I wanna delete
"/", "", ":", "*", "?", "< ", ">", "|"
Code:
Range("A1").Select
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
[Code].....
I had VBA run a third-party program ("HYD") using data listed in my spreadsheet. HYD produces a textfile and I want to extract a number from that log (line 58, columns 6-10 to be exact).
I started with
Open "C:TempHYDLOG.TXT" For Input As #1
but I don't know how to have it skip down to line 58 or which function to use to extract the number I want. I've read through tons of help files and forums but none of them treat anything as simple as scrolling down to specific lines.
The entire subroutine up to that point is as follows: ....
I have a .txt file (comma separated) that updates daily, which I need to import into Excel. The .txt file contains data from many years, but I only need the data from 2012. Is there a way to only import rows where the data in column "yearID" equals 2012? Since this is a daily operation, I'd rather not import all the rows and then sort/delete rows every time.
View 4 Replies View RelatedI have an Excel workbook with 2 worksheets in it. One of the worksheets imports a txt file and then the other worksheet displays data that formulas I have written have collected.
I usually import the txt file by running through the many steps with the "import data" option. This is good but I want it to be faster.
I used the macro recorder and ended up with the following code
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\DesktopImport FolderTest.txt" _
, Destination:= Range("A1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
I'm trying to do this in VBA.
I open a text file for input, and I want to jump to a certain line # in that text file. The difficult part is that each line has variable length, so I can't use the Seek function.
The data look like this:
1,2,3,4
555,666,777,888
99,00,11,22
... etc.
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
View 2 Replies View RelatedI have a text file with no discernable format ( can't import into excel) that is too large to put all the data into an excel worksheet. This file is made to print out on a network printer.
I don't need all the info in the file, I would like to specify a variable, search the text file for the variable, then specify the amount of rows down to look for the data value and input the result into an excel spreadsheet.
Part of my code adds a Worksheet to the selected Workbook and then re-orders them alphabetically, but one of the Worksheets (Called home) should always remain at position 1. Is there a way to dictate exactly which postion it should be in so that I can place it there after the sort is complete?
View 7 Replies View RelatedI need help urgently for parsing a text file to have a specific format. The text file is of format mentioned below: ...
View 9 Replies View RelatedThat does allow me to filter the output in Master, but ideally what I would want to do is not have to set aside 300 lines in the Master file for each of the Staff files. Conceptually, I'd like to have the spreadsheets Staff A, Staff B, etc. look for and export only lines which have data in them and then have Master bring those lines in automatically. This may not be possible, in which case your suggested solution is the best approach.
View 9 Replies View RelatedHow to find and show position number? I try merged two function, but doesn't work
1º=FIND("0";B2)
2º=FIND(CHAR(1);SUBSTITUTE(B2;"0";CHAR(1);2))
Number
Position
[Code].....
Set FSO = New FileSystemObject
Set FSOFile = FSO.OpenTextFile(textfullname, 2, True)
FSOFile.Write ("xx")
FSOFile.Write ("02022012")
FSOFile.Writeline ("Next line")
I am getting this in the text file:- xx02022012Next line
I want this: xx02022012 Next line
How to change line when writing to a text file?? the writeline is not changing line.
I use excel to read in an ASCII data file in which every line beginning with H is a header line and lines beginning with S are data line.
I want excel to filter out the header lines and output the file with the data lines only then outputs these in a new file.
I also want it to read the values from column 72 to 75 and subtract 6 from them then output them again to the same new file.
Sub read()
Open "d:/intouch/0502SPSS.LOG" For Input As #1
Dim i As Integer
Dim lines(9999) As String
i = 0
Do While Not EOF(1)
Line Input #1, z
i = i + 1
If Mid(z, 1, 1) = "H" Then
i = i - 1
ElseIf Mid(z, 1, 1) = "S" Then.................................
the problem is that the new file is generated but it is always empty
Formula/code to change the position of where the subtotals are placed. I don't want them appearing at the beginning or end of the data set but in a separate column beside each data set. how to access the code so I can try and alter it myself.
View 14 Replies View RelatedI have a large database with names down the left and numbers across the top. I want to "tilt" the entire table so that the names run across the top and the numbers run down the left side while keeping all the data together.
Example:
from this:
a b c d e f
1
2
3
4
5
to this:
1 2 3 4 5
a
b
c
d
e
f
I have a chart that needs to be positioned at the bottom of a table of data. (The table of data is created by some VBA which extracts data from another sheet based on user input). The chart is a pivot chart built on a pivot table from another worksheet. I've read the suggestion that the chart should be positioned above the table of data which I would do, however as there is already a series of tables and charts above, it would be too confusing for the user to see the chart out of sequence.
As I already have a fair bit of VBA to generate my existing sheet, I’d like to have something very simple that I can insert in various places depending when the user makes a change, which requires the chart to be repositioned. Something like:
Change chart position To Range ("B65536").End(xlUp).Row
I have a variable list. Each column will be 250 digits and numbers of rows will be variable. I want a macro to import a text file from a specific folder into a sheet without split it to columns. So we will work only in column A
Then macro will find B1002 wording in A1. if it is exist, it will copy the next 36 digits after B1002 wording if not then it will search A2 row. The next step will be to search and find another wording "B1001" if it finds it will replace copied 36 digits text. If it can not find B1001 wording it will go to upper row and search B1001 wording here and paste the text. This will go on till row shows #END. This means it reached the end of the list. And then macro will save this file as text file to another folder.
So macro will go to beginning to open other file in the folder and this will go on till last file in the folder.
This is the logic of the macro. Here is the sample of what I want.
Original Data: ....
I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.
I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.
The index column is used in some conditional formatting rules, so I would like to keep it anyway.
I have one Popup Menus in Userform, But i want to to Open Popup menu in Specific Position in userform, In Fixed Position,i want to specific a position which opens Popup Menus in Userform.
Look In Example in Attach.
[Code]....
menu_1.xls‎
I have an autoshape on an sheet, and use this code (it's a circle), to rezise it....
[Code] ....
However, it does rezise it, but the shape doesn't keep it's central position. Is there a bit of code I can add to stop the shape from moving? In other words, have the shape rezise around it's center of location?
I have written a macro to export many bmp image files to different PPT slides. Everything works fine, but for few slides I am not able to change the co-ordinates. I am using the below code to position the image files. But no matter how much I change the values, the images sits in the same place. And I dont get any error.
PPSlide.Shapes.AddPicture Filename:=slide4pic2, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=240, Width:=245, Height:=100
PPSlide.Shapes.AddPicture Filename:=slide4pic3, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=340, Width:=245, Height:=100
PPSlide.Shapes.AddPicture Filename:=slide4pic4, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=440, Width:=245, Height:=100
I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.
I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.
Multiple commodities and multiple engineers should be separated by definite structure.
find worksheet with input and output (manual) as expected.
When a userform textbox value is changed, if the vertical scrollbars were shown previously (due to more text than displayed size) and is still required for new value, how do you reset the scrollbar/textbox value back to the top (if it was previously scrolled down by the user). If teh scrool bar is no longer required for new value (due to sufficient space to display new value without scrollbars), how do you remove/hide the scrollbar again until needed.
View 2 Replies View RelatedIt seems that you can change position when you editing comment and its remembered, but when its showing its alway's on default position.
Is there way to change position of displayed (when you mouse over cell and coment pops up) comment position?