Transfer fixed width data from excel to text file

[deepakgoyal2005]

Hi,

I have an excel sheet with data of variable lengths in different column.
I need an aligned data in a text file. So I tried turning the data into fixed width with following formula in Column D4,

TEXT(A4,"00000") & B4 & REPT(" ",40-LEN(B4)) & TEXT(C4,"0000000.00")

Where,
Column A is a number
Column B is Text of maximum 40 characters
Cloumn C is amount field

Then, manually copy and paste the column with formula into a text file for final output. I want to avoid these steps being done by other teammates manually and avoid manual errors. So, want to do it through VBA Macro. Also since my data starts with Row 4 in excel sheet, I want to skip first 3 rows.

I, being not much comfortable with VBA, Can anyone please suggest something in this regards.
Thanks in advance

 

[bensonsearch]

hi,
is D always the same amount of rows or changing?

do you want a auto text file creation as well?

 

[deepakgoyal2005]

Thanks for the reply Benson.

No, its not the same number of rows. Code may have a check to transfer till a row has data in it.
A text file needs to be created in the same folder where the actual excel sheet is present. The file name could be kept anything hardcoded.

Let me know if any more information is required.

 

[bensonsearch]

is the file always in the same location? ie c:\

 

[bensonsearch]

Hi,
something like the below would work (however VBA is not liking the formula, someone may need to edit to fix it as i dont do formula much)

Dim file1 As String
Dim file2 As String
Dim writer As String
Dim count As Long
Dim counta As Long
count = Sheets("Sheet1").Range("A3").CurrentRegion.Rows.count
counta = 3
Do Until counta = count + 1
sheets("Sheet1").range("D" & counta).formula = "TEXT(A4,"00000") & B4 & REPT(" ",40-LEN(B4)) & TEXT(C4,"0000000.00")"

counta = counta + 1
Loop
writer = Sheets("Sheet1").Range("D1:D" & count).Value
file1 = "C:\"
file2 = "test" & ".txt"

myfile = file1 & file2
fnum = FreeFile()
Open myfile For Output As fnum
Print #fnum, writer
Close #fnum

 

[deepakgoyal2005]

I tried with different permutations and combinations and looked out for probable solution on net but no success
1 thing which I changed was from "Sheets("Sheet1")." to "ActiveSheet." to process the request for the opened sheet.

 

[bensonsearch]

ok found something that should help. try adjusting the below line

sheets("Sheet1").range("D" & counta).formula = "TEXT(A4,""00000"") & B4 & REPT("" "",40-LEN(B4)) & TEXT(C4,""0000000.00"")"

 

[deepakgoyal2005]

Hi Benson,

The new VBA to insert formula works fine.
A few more changes in the rest of the code gave me the desired results.

My final code which is working good is,

Sub Transfer2text()
 
Dim Output As String
Dim LastRow As Long
Dim StartRow As Long
 
myfile = "C:\Testfile.txt"
fnum = FreeFile()
Open myfile For Output As fnum
StartRow = 4
LastRow = ActiveSheet.Range("A4").CurrentRegion.Rows.count
 
Do Until StartRow = LastRow + 1
    ActiveSheet.Range("D4:D" & StartRow).Formula = "=TEXT(A4,""00000"") & B4 & REPT("" "",40-LEN(B4)) & TEXT(C4,""0000000.00"")"
    Output = ActiveSheet.Range("D" & StartRow).Value
    Print #fnum, Output
    StartRow = StartRow + 1
Loop
Close #fnum
 
End Sub

Million thanks for your time and support

 

[Biz]

Hi Benson,

The new VBA to insert formula works fine.
A few more changes in the rest of the code gave me the desired results.

My final code which is working good is,

Sub Transfer2text()
 
Dim Output As String
Dim LastRow As Long
Dim StartRow As Long
 
myfile = "C:\Testfile.txt"
fnum = FreeFile()
Open myfile For Output As fnum
StartRow = 4
LastRow = ActiveSheet.Range("A4").CurrentRegion.Rows.count
 
Do Until StartRow = LastRow + 1
    ActiveSheet.Range("D4:D" & StartRow).Formula = "=TEXT(A4,""00000"") & B4 & REPT("" "",40-LEN(B4)) & TEXT(C4,""0000000.00"")"
    Output = ActiveSheet.Range("D" & StartRow).Value
    Print #fnum, Output
    StartRow = StartRow + 1
Loop
Close #fnum
 
End Sub

Million thanks for your time and support

Why do we use #fnum? What # mean?

Biz

 

[deepakgoyal2005]

#fnum is the pointer to the output file linked to 'myfile' in Open method.