Run Macro in txt file

edsitzle · Jul 31, 2014

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?

oldbrewer · Jul 31, 2014

why not have a second worksheet where A1 = value(sheet1!A1)

DocAElstein · Jul 31, 2014

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.

parvinmomin · Aug 25, 2014

Hi, my data is separated by| can you plz guide me

DocAElstein · Aug 25, 2014

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

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 ?

DocAElstein · Aug 26, 2014

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),

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

Just	Testing a
Table	while I
am	at it

1	\|	2
3	\|	4
5	\|	2098765 !!!

DocAElstein · Aug 26, 2014

|

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?

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

……So here is a very basic code.

Option Explicit Sub BasicMacroTextFileToExcel() ' Very Simply alternative to the Spreadsheet way using Wizzard, Opening as text File in Excel etc ' 'First Bit can be ommited if fielnames are always known. It basically gets all the File info. 'Note text File And Excel File must not be in the same Directory as you are being asked for it here. On Err GoTo TheEnd ' if anything goes wrong go to the end rather than crasching! ' 'First Bit: TextDataFileName and seperator input Dim Sp As String ' Allocate memory for The seperater used by the Text file Dim DtaFleNm As Variant '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 DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,") 'The Application Method GetOpenFilename Displays the standard Open dialog box. The optional arguments:= writes a name,limits type of files looked for If DtaFleNm = False Then Exit Sub 'Stops Porgram if User Cancels (which gives the boolean False) Let Sp = Application.InputBox("Enter a separator character.", Type:=2) 'prompts user for separator character If Sp = vbNullString Then Exit Sub 'Stops Porgram if User Cancels (which gives an empty string) 'End First Bit TextDataFileName and seperator input ' ' '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: ' Dim DtaFleNm As String ' Let DtaFleNm = ThisWorkbook.Path & "\TextDataFile.txt" ' Dim Sp As String ' Let Sp = "|" ' 'Note: If you do it this way you should put the Active Excel File and Data Text File in the same Directory ' '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 Dim Rw As Byte, StCm As Byte, Cm As Byte 'Before you start You should select cell Let StCm = ActiveCell.Column 'where you want to start data to come in. Column is then reset in loop Let Rw = ActiveCell.Row 'Row increases downwards so the initial and loop caount can be same variable Dim DtaFleLn As String Dim TxtPs As Byte 'text character position in text file looking form left Dim SpPs As Byte 'position of seperater lookinf from left Dim TxtDta As String 'String used temporarily for each Text data value Open DtaFleNm For Input As 3 ' 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.)) Do Until EOF(3) 'Do until going your at the end of the data highway Line Input #3, DtaFleLn '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.) If Right(DtaFleLn, 1) <> Sp Then DtaFleLn = DtaFleLn & Sp 'Glue a seperator on the end of the data file line if there is not one there Let Cm = StCm 'go to first column Let TxtPs = 1 ' Initial Data Text Position is at 1 (Ist Left poisition in text file line Let SpPs = InStr(TxtPs, DtaFleLn, Sp) 'Find position of next seperator While SpPs >= 1 'As long as a next Seperator position is found, we go througth columns looking for data Let TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs) 'this gives the next data value Let ActiveSheet.Cells(Rw, Cm).NumberFormat = "@" 'This is not allways necerssary but can help avoid annoying problems with data turning into times or dates Let ActiveSheet.Cells(Rw, Cm).Value = TxtDta 'Put Text data in excel cell Let ActiveSheet.Cells(Rw, Cm).Value = Trim(ActiveSheet.Cells(Rw, Cm).Value) 'Most not necerssary but can help to clean up data if any extra spaces came in Let TxtPs = SpPs + 1 'Start of next data is just after next seperator Let Cm = Cm + 1 'Increase Excel Column number Let SpPs = InStr(TxtPs, DtaFleLn, Sp) 'Get the next Seperator position by starting at the current data and going right until the next seperator is found Wend 'go to next text data to the right in the data file Line Let Rw = Rw + 1 'Increase row number for Excel(The tecxt file automatically goes to the next row after reading a line Loop 'Go to the next Row in the Data Text file TheEnd: Close 3 '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 End Sub 'BasicMacroTextFileToExcel()

……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.

1	\|	2	\|	3
4	\|	5	\|	6

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

Book1

A

B

C

D

E

1

2

3

4

5

MacroTextToExcel

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

Book1

A

B

C

D

E

1

2

1

2

3

4

5

6

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

oldbrewer · Aug 29, 2014

123\|4567\|89\|98765\|43








123
4567
89					Dim num(100), bar(100)
98765					lenn = Len(Cells(1, 1))
43					Sum = 1
					bar(1) = 0
					For j = 2 To 100
					If j > lenn Then GoTo 100
					If Mid(Cells(1, 1), j, 1) = "\|" Then Sum = Sum + 1: bar(Sum) = j
					Next j
					100 bar(Sum + 1) = lenn + 1
					num(1) = Left(Cells(1, 1), bar(2) - 1)
					For k = 2 To Sum
					num(k) = Mid(Cells(1, 1), bar(k) + 1, bar(k + 1) - bar(k) - 1)
					Next k
					x = 9
					For m = 1 To Sum
					x = x + 1
					Cells(x, 1) = num(m)
					Next m
					End Sub

					this simple macro splits out the numbers in A1

oldbrewer · Aug 29, 2014

123\|4567\|89\|98765\|43







123
4567
89
98765			a = 9
43			lenn = Len(Cells(1, 1))
			For j = 1 To lenn
			If Mid(Cells(1, 1), j, 1) = "\|" Then a = a + 1: GoTo 10
			Cells(a, 1) = Cells(a, 1) & Mid(Cells(1, 1), j, 1)
			10 Next j
			End Sub



			this is shorter and neater

Run Macro in txt file

edsitzle

New Member

Excel Facts

oldbrewer

Well-known Member

DocAElstein

Banned user

parvinmomin

New Member

DocAElstein

Banned user

DocAElstein

Banned user

DocAElstein

Banned user

oldbrewer

Well-known Member

oldbrewer

Well-known Member

Similar threads

Forum statistics

Share this page

Run Macro in txt file

New Member

Excel Facts

Well-known Member

Banned user

New Member

Banned user

Banned user

Banned user

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock