This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I'm looking for a macro to save data that exists in a specific excel sheet into a .txt file with specific layout.
There are no headers in the file and specifications should be as per below.
| Content | Position | Length | Content Format | EXAMPLE 1 | EXAMPLE 2 |
|---|---|---|---|---|---|
| FIELD 1 | 1 | 4 | Text always consist of 4 characters: Need a prompt to determine if FFSU or MCOU goes here. | FFSU | MCOU |
| FIELD 2 | 5 | 2 | Text always consist of 2 characters: Need a prompt to add correct State | NY | TX |
| FIELD 3 | 7 | 11 | Number; leading spaces should contain a zeros to make sure field length is exactly 11; hyphens if dividing the numbers need to be removed | 186000431 | 00186-0004-31 |
| FIELD 4 | 18 | 1 | Number; representing quarter 1-4 | 1 | 3 |
| FIELD 5 | 19 | 4 | Number year | 2017 | 2015 |
| FIELD 6 | 23 | 11 | Text but length can vary; spaces should be added afterwards to have a field length of 11 | SYMBICORT | TOPROL XL |
| FIELD 7 | 34 | 12 | Decimal number (style #####.####### leading spaces should be zeros to make sure field length is exactly 12 so spaces are variable) | 00002.550200 | 00216.510000 |
| FIELD 8 | 46 | 14 | Decimal number (style ###########.## leading spaces should be zeros to make sure field length is exactly 14 so spaces are variable) | 00000000954.00 | 00000000064.00 |
| FIELD 9 | 60 | 13 | Decimal number (style ##########.## leading spaces should be zeros to make sure field length is exactly 13 so spaces are variable) | 0000006719.59 | 0000000163.21 |
| FIELD 10 | 73 | 8 | =>number and leading spaces should be zeros to make sure field length is exactly 8 so spaces are variable | 00000032 | 00000001 |
| FIELD 11 | 81 | 13 | Decimal number (style ##########.## leading spaces should be zeros to make sure field length is exactly 13 so spaces are variable) | 0000010316.32 | 0000000207.19 |
| FIELD 12 | 94 | 13 | Decimal number (style ##########.## leading spaces should be zeros to make sure field length is exactly 13 so spaces are variable) | 0000000000.00 | 0000000000.00 |
| FIELD 13 | 107 | 14 | Decimal number (style ##########.## leading spaces should be zeros to make sure field length is exactly 13 so spaces are variable) | 00000010316.32 | 00000000880.41 |
Input from excel file looks like below but I can change this if needed
Output in txt format should look like below:
Thanks in advance for any help offered.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Bernie thanks for your patience. I am new to this. OK so I am stuck in a loop now and it will not let quit. How do I get out of the loop. The data for the code is the mapping of Column 'A' with the number being the place in which to start the new field on the line.
Put my code into a standard codemodule, and don't use a selection change event. You don't need to do this more than once.
And looking at the picture of your code, "1,9" is not a valid column address - either use letters in quotes, or use integer values: "B" or 2, "C" or 3, etc.
This is the error message I am getting
So I have modified the code and changes added were the data should be mapped and nothing is happening I get an inside error.
I am not sure what column is going to which field, but use a macro like this: replace the column letters in the code with the correct column letter for the information you want exported. Also, I was not sure if you wanted to be prompted for each record or for each file, so I did it for each record.
Option Explicit
Sub ExportToTX()
Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim StartRow As Long
Dim EndRow As Long
fName = ThisWorkbook.Path & "\Exported Values.txt"
On Error GoTo EndMacro:
FNum = FreeFile
StartRow = 1
EndRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Open fName For Output Access Write As #FNum
For RowNdx = StartRow To EndRow
WholeLine = ""
WholeLine = WholeLine & IIf(MsgBox("FFSU = Yes, MCOU = No", vbYesNo) = vbYes, "FFSU", "MCOU") 'Field 1
WholeLine = WholeLine & UCase(InputBox("2 letter State name?")) 'Field 2
WholeLine = WholeLine & Right("00000000000" & Replace(Cells(RowNdx, "A").Text, "-", ""), 11) 'Field 3
WholeLine = WholeLine & Cells(RowNdx, "B").Text 'Field 4 - 1 digit quarter
WholeLine = WholeLine & Cells(RowNdx, "C").Text 'Field 5 - 4 digit year
WholeLine = WholeLine & Right(" " & Cells(RowNdx, "D").Text, 11) 'Field 6 Text like SYMBICORT TOPROL XL
WholeLine = WholeLine & Format(Cells(RowNdx, "E").Value, "00000.0000000") 'Field 7 Text like 00002.550200 00216.510000
WholeLine = WholeLine & Format(Cells(RowNdx, "F").Value, "00000000000.00") 'Field 8 Text like 00000000954.00 00000000064.00
WholeLine = WholeLine & Format(Cells(RowNdx, "G").Value, "0000000000.00") 'Field 9 Text like 0000006719.59 0000000163.21
WholeLine = WholeLine & Format(Cells(RowNdx, "H").Value, "00000000") 'Field 10 Text like 00000032 00000001
WholeLine = WholeLine & Format(Cells(RowNdx, "I").Value, "0000000000.00") 'Field 11 Text like 0000010316.32 0000000207.19
WholeLine = WholeLine & Format(Cells(RowNdx, "J").Value, "0000000000.00") 'Field 12 Text like 0000000000.00 0000000000.00
WholeLine = WholeLine & Format(Cells(RowNdx, "K").Value, "0000000000.00") 'Field 13 Text like 00000010316.32 00000000880.41
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Close #FNum
End Sub