Run Macro in txt file

E

edsitzle

New Member
Joined
Feb 18, 2014
Messages
6
Our accounting system exports data into Excel as a text file. Is there a way to be able to run a macro out of my Personal Macro Workbook without saving and reopening the data as an .xlsx file?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi,
. Are you asking how to import a text file into an Existing Excel File using VBA? This is an alternative method to importing / that is to say, opening the file in Excel (where you get lots of different saved Files etc.).
. I use this method frequently. The code is fairly simple (It has to be - I am a beginner! ). It would be useful to know the format of your text file, - for example how data is separated etc.
 
Upvote 0
parvinmomin said:
Hi, my data is separated by| can you plz guide me
Click to expand...

Hi,
. I think it is still not clear to anyone trying to help you exactly what you want.

. As always, “A (Good!) Picture paints a thousand words”

. Can you provide a clear Table or tables showing some text file example data but also importantly exactly how the final output should look like in the Excel File based on your actual example data.

There are various ways to do this. The first is preferred by this Forum for excel files as then everyone can see wot is going on quickly. (But It is an excel Add-in so it does not help for a text File). The Third method I prefer. - Then one can get on straight away with writing a code for you in the file you provide. (I can then also do a screen shot so everyone in the Forum can see it without downloading the File)

. 1 If you can, try uploading this, https://onedrive.live.com/?cid=8cffd...CE27E813%21189 instructions here MrExcel HTML Maker . This free Excel add-In is good for screen shots here of spreadsheets. Then everyone can quickly see what is going on and follow the Thread easily.
Or
. 2 Up left in the Thread editor is a table icon. Click that, create an appropriately sized table and fill it in. (This may not be available in the Reply Editor: Then at least separate your columns with a constant separator so we have half a chance of copying and separating the data in an excel file. )
Or
. 3 supply us with an example text file (Can of course be shortened, or Made up data in case any info is sensitive) and an excel file that looks like the final result using the method you currently use.
. For example send over these 2 free things: FileSnack | Easy file sharing or Box Net,
Remember to select Share after uploading and give us the link they provide.

. In your case as you are dealing with text file, and because option 2 may not be available in the reply editor (Not sure about that – I must ask the Board about that), Option 3 is probably the best to go with

P.s. The | separator I have never used. Just looking on my Key board…. Here is something that looks similar..
|
I Hope that is it
It was CTRL and ALT and second up and second across from the left hand bottom corner of my keyboard ?
 
Upvote 0
DocAElstein said:
...... In your case as you are dealing with text file, and because option 2 may not be available in the reply editor (Not sure about that – I must ask the Board about that),
Click to expand...


Hi, parvinmomin

. ....To get this icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button.


P.s. Welcome to the Board. - I did not see that it was your first time here. (It is unusual to make a Reply to an existing Thread as your first Post. But good that you did not start a new Thread for an existing Theme

Alan



[TABLE="width: 500"]
<tbody>[TR]
[TD]Just [/TD]
[TD]Testing
a[/TD]
[/TR]
[TR]
[TD]Table[/TD]
[TD]while I[/TD]
[/TR]
[TR]
[TD]am [/TD]
[TD]at it

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD] |[/TD]
[TD]2

[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]|[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]|[/TD]
[TD]2098765 !!![/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
|
edsitzle said:
Our accounting system exports data into Excel as a text file. Is there a way to be able to run a macro out of my Personal Macro Workbook without saving and reopening the data as an .xlsx file?
Click to expand...

parvinmomin said:
Hi, my data is separated by| can you plz guide me
Click to expand...

……So here is a very basic code.

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> BasicMacroTextFileToExcel()<br><SPAN style="color:#007F00">' Very Simply alternative to the Spreadsheet way using Wizzard, Opening as text File in Excel  etc</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'First Bit  can  be ommited if fielnames are always known. It basically gets all the File info.</SPAN><br><SPAN style="color:#007F00">'Note text File And Excel File must not be in the same Directory as you are being asked for it here.</SPAN><br><SPAN style="color:#00007F">On</SPAN> Err <SPAN style="color:#00007F">GoTo</SPAN> TheEnd <SPAN style="color:#007F00">' if anything goes wrong go to the end rather than crasching!</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'First Bit: TextDataFileName and seperator input</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Sp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">' Allocate memory for The seperater used by the Text file</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DtaFleNm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN> <SPAN style="color:#007F00">'The DataFilename itself is a string. But a vasriant is specified as you may have a Boolean input in the Dialogue boy if the user cancels</SPAN><br>  DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,") <SPAN style="color:#007F00">'The Application Method GetOpenFilename Displays the standard <SPAN style="color:#00007F">Open</SPAN> dialog box. The optional arguments:= writes a name,limits type of files looked for</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> DtaFleNm = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Stops Porgram if User Cancels (which gives the boolean False)</SPAN><br>  <SPAN style="color:#00007F">Let</SPAN> Sp = Application.InputBox("Enter a separator character.", Type:=2) <SPAN style="color:#007F00">'prompts user for separator character</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Sp = vbNullString <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Stops Porgram if User Cancels (which gives an empty string)</SPAN><br><SPAN style="color:#007F00">'End First Bit TextDataFileName and seperator input</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'    'If you want to skip the first bit, delete it or comment it out and type in the text File path and seperator in lines like this:</SPAN><br><SPAN style="color:#007F00">'    Dim DtaFleNm As String</SPAN><br><SPAN style="color:#007F00">'    Let DtaFleNm = ThisWorkbook.Path & "\TextDataFile.txt"</SPAN><br><SPAN style="color:#007F00">'    Dim Sp As String</SPAN><br><SPAN style="color:#007F00">'    Let Sp = "|"</SPAN><br><SPAN style="color:#007F00">'    'Note: If you do it this way you should put the Active Excel File and Data Text File in the same Directory</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'Start Main Bit: Get data from text file  line by line, then for each line go through every Column. As each data bit is found it is put in the approriate place in the Excel File</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Rw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, StCm  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, Cm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Before you start You should select cell</SPAN><br><SPAN style="color:#00007F">Let</SPAN> StCm = ActiveCell.Column <SPAN style="color:#007F00">'where you want to start data to come in. Column is then reset in loop</SPAN><br><SPAN style="color:#00007F">Let</SPAN> Rw = ActiveCell.Row <SPAN style="color:#007F00">'Row increases downwards so the initial and loop caount can be same variable</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DtaFleLn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TxtPs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'text character position in text file looking form left</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> SpPs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'position of seperater lookinf from left</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TxtDta <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">'String used temporarily for each Text data value</SPAN><br>  Open DtaFleNm <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Input</SPAN> <SPAN style="color:#00007F">As</SPAN> 3 <SPAN style="color:#007F00">' Sort of opens up a data highway and gives it route number (here 3) to distinguisch it from any others currently open. (If you do not know how many Highways are already open and have a number, then use instead of a number someting like #HgWyNm here, and HgWyNm everyhwere where the number is. This gives it the next available number (Remember to Dim it at the start also.))</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> EOF(3) <SPAN style="color:#007F00">'Do until going your at the end of the data highway</SPAN><br>    Line <SPAN style="color:#00007F">Input</SPAN> #3, DtaFleLn <SPAN style="color:#007F00">'Bring in a line from text file(Automatically goes to next line or next part of highway after this command. So we need mo row count for the data file.)</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Right(DtaFleLn, 1) <> Sp <SPAN style="color:#00007F">Then</SPAN> DtaFleLn = DtaFleLn & Sp <SPAN style="color:#007F00">'Glue a seperator on the end of the data file line if there is not one there</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> Cm = StCm <SPAN style="color:#007F00">'go to first column</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> TxtPs = 1 <SPAN style="color:#007F00">' Initial Data Text Position is at 1 (Ist Left poisition in text file line</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> SpPs = InStr(TxtPs, DtaFleLn, Sp) <SPAN style="color:#007F00">'Find position of next seperator</SPAN><br>      <SPAN style="color:#00007F">While</SPAN> SpPs >= 1 <SPAN style="color:#007F00">'As long as a next Seperator position is found, we go througth columns looking for data</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs) <SPAN style="color:#007F00">'this gives the next data value</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> ActiveSheet.Cells(Rw, Cm).NumberFormat = "@" <SPAN style="color:#007F00">'This is not allways necerssary but can help avoid annoying problems with data turning into times or dates</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> ActiveSheet.Cells(Rw, Cm).Value = TxtDta <SPAN style="color:#007F00">'Put Text data in excel cell</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> ActiveSheet.Cells(Rw, Cm).Value = Trim(ActiveSheet.Cells(Rw, Cm).Value) <SPAN style="color:#007F00">'Most not necerssary but can help to clean up data if any extra spaces came in</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> TxtPs = SpPs + 1 <SPAN style="color:#007F00">'Start of next data is just after next seperator</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> Cm = Cm + 1 <SPAN style="color:#007F00">'Increase Excel Column number</SPAN><br>      <SPAN style="color:#00007F">Let</SPAN> SpPs = InStr(TxtPs, DtaFleLn, Sp) <SPAN style="color:#007F00">'Get the next Seperator position by starting at the current data and going right until the next seperator is found</SPAN><br>      <SPAN style="color:#00007F">Wend</SPAN> <SPAN style="color:#007F00">'go to next text data to the right in the data file Line</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> Rw = Rw + 1 <SPAN style="color:#007F00">'Increase row number for Excel(The tecxt file automatically goes to the next row after reading a line</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#007F00">'Go to the next Row in the Data Text file</SPAN><br>TheEnd:<br>  <SPAN style="color:#00007F">Close</SPAN> 3 <SPAN style="color:#007F00">'Very important to do this. Shuts highway Off. There can be strange errors if you do not do that. (It is here so that even after an error it will be done</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'BasicMacroTextFileToExcel()</SPAN></FONT>


……And here again simplified a bit.

Code: 
Sub SimplifiedBasicMacroTextToExcel()

On Err GoTo TheEnd
  DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,")
    If DtaFleNm = False Then Exit Sub
   Sp = Application.InputBox("Enter a separator character.", Type:=2)
    If Sp = vbNullString Then Exit Sub
 StCm = ActiveCell.Column
 Rw = ActiveCell.Row
  Open DtaFleNm For Input As 3
    Do Until EOF(3)
    Line Input #3, DtaFleLn
    If Right(DtaFleLn, 1) <> Sp Then DtaFleLn = DtaFleLn & Sp
     Cm = StCm
     TxtPs = 1
     SpPs = InStr(TxtPs, DtaFleLn, Sp)
      While SpPs >= 1
       TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs)
       ActiveSheet.Cells(Rw, Cm).Value = TxtDta
       TxtPs = SpPs + 1
       Cm = Cm + 1
       SpPs = InStr(TxtPs, DtaFleLn, Sp)
      Wend
     Rw = Rw + 1
    Loop
TheEnd:
  Close 3
End Sub

It calls up a Text file that looks a bit like this.


[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]|[/TD]
[TD]2 [/TD]
[TD]|[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]|[/TD]
[TD] 5[/TD]
[TD]|[/TD]
[TD] 6[/TD]
[/TR]
</tbody>[/TABLE]


Then using that text file, it applies it to an Excel File that initially looks like this (With Cell “B2” arbitrarily selected)



Book1
ABCDE
1
2
3
4
5
MacroTextToExcel


And after running the macro the Excel File looks like this:


Book1
ABCDE
1
2123
3456
4
MacroTextToExcel


. Here is the Excel and the text File
FileSnack | Easy file sharing
FileSnack | Easy file sharing

. If that is anything like you are interested in, and then if you have any questions, then come back. Someone should help.

. Alan Elston
 
Upvote 0
[TABLE="width: 745"]
<colgroup><col span="2"><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]123|4567|89|98765|43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dim num(100), bar(100)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]98765[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] lenn = Len(Cells(1, 1))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Sum = 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] bar(1) = 0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For j = 2 To 100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] If j > lenn Then GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] If Mid(Cells(1, 1), j, 1) = "|" Then Sum = Sum + 1: bar(Sum) = j[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100 bar(Sum + 1) = lenn + 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]num(1) = Left(Cells(1, 1), bar(2) - 1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For k = 2 To Sum[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] num(k) = Mid(Cells(1, 1), bar(k) + 1, bar(k + 1) - bar(k) - 1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] x = 9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For m = 1 To Sum[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] x = x + 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Cells(x, 1) = num(m)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next m[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] End Sub[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]this simple macro splits out the numbers in A1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 620"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]123|4567|89|98765|43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]98765[/TD]
[TD][/TD]
[TD][/TD]
[TD]a = 9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[TD] lenn = Len(Cells(1, 1))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] For j = 1 To lenn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] If Mid(Cells(1, 1), j, 1) = "|" Then a = a + 1: GoTo 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Cells(a, 1) = Cells(a, 1) & Mid(Cells(1, 1), j, 1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10 Next j[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] End Sub[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]this is shorter and neater[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
File Save As
Replies
1
Views
195
6StringJazzer
6StringJazzer
A
  • Question Question
Saving dialog box hanging after running macro
2
Replies
16
Views
381
Alex Blakenburg
A
H
  • Question Question
I need an excel macro to open a word .docm and run a macro
Replies
0
Views
85
Hansulet
H
D
  • Question Question
Merge sheets of a workbook macro
2
Replies
19
Views
360
sleek12
sleek12
A
  • Question Question
Macro to count the number times the excel/powerpoint has been opened
2
Replies
14
Views
296
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,802
Messages
6,181,048
Members
453,014
Latest member
Chris258

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top