Click here to Skip to main content
15,793,442 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In a macro I have opened a new text file and copying data from excel sheet to that text file.It is working fine and i am getting data in the text file also.But........
The format is not in Good order,like

FirstName LastName
Bob John
Qwak crystal

But i want in this manner

FirstName LastName
Bob John
Qwak crystal

Even if i try to insert spaces also (Eg 5 spaces)between words,The format of one row to other row is different,Plz Give suggestions for this...........
Updated 23-Jun-11 2:38am
Prerak Patel 24-Jun-11 1:17am    
Check now. It was just a wrong variable name.

Try this function in your macro, before adding your data to text file.

'sValue=text of your cell
'sPad_Text=padding character - " " in most cases
'iMaxSize=desired width of field (greater than maximum size)
Public Function pad(svalue, spad_text, stype, imaxsize)
Dim length
length = Len(svalue)
If stype = "Left" Then
pad = String(imaxsize - length, spad_text) & svalue
pad = svalue & String(imaxsize - length, spad_text)
End If
End Function
Share this answer
Member 7940184 24-Jun-11 2:47am    
it is giving error at:String(imaxxsize-length,spad_text)
Whats to add instead of this
Prerak Patel 24-Jun-11 2:49am    
why do you use imaxxsize while name of variable is iMaxSize?
Prerak Patel 24-Jun-11 2:50am    
Moreover, show me how do you call this function.
Member 7940184 24-Jun-11 2:59am    
Call pad(Sheet6.Cells(j, i).Value, "", "Left", 10)

Sub pad(svalue, spad_text, stype As String, imaxsize)
Dim length
length = Len(svalue)
If stype = "Left" Then
svalue = String(imaxsize - length, spad_text) & svalue
svalue = Value & String(imaxsize - length, spad_text)
End If
End Sub

Error:Invalid procedure call or argument
Baldur 24-Jun-11 4:57am    
Just saw your question. Could you please tell me the value of svalue when the error occurs?
Instead on Spaces put Tabs between the two columns
Share this answer
Prerak Patel 23-Jun-11 7:42am    
It won't necessarily solve the problem. Consider last name 1)Shah 2)Challapakulla
[no name] 23-Jun-11 7:52am    
is it is like the last name is of two words???

That why i said to use tab char between 2 words. But your answer is correct. It will show the proper output.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900