Problem with writing Excel data to Txt file

Question

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

Posted 23-Jun-11 1:35am

Member 7940184

Updated 23-Jun-11 1:38am

v2

Add a Solution

Comments

Prerak Patel 24-Jun-11 1:17am

Check now. It was just a wrong variable name.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prerak Patel · Answer 1 · 2011-06-23T19:16:00

Solution 3

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
'sType="Left"|"Right"
'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
Else
pad = svalue & String(imaxsize - length, spad_text)
End If
End Function

Posted 23-Jun-11 19:16pm

Prerak Patel

Updated 24-Jun-11 0:01am

v4

Comments

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
else
svalue = Value & String(imaxsize - length, spad_text)
End If
End Sub

Error:Invalid procedure call or argument

Baldur 24-Jun-11 4:57am

Hi.
Just saw your question. Could you please tell me the value of svalue when the error occurs?

Member 7940184 24-Jun-11 3:04am

Error is coming at String() not at pad()

Prerak Patel 24-Jun-11 6:01am

Did you try it as function?
Cells(1,1) = Pad(Cells(1,1), " ", "Left", 10)

Prerak Patel 24-Jun-11 6:02am

I have changed it as function.

Member 7940184 27-Jun-11 2:41am

Thanks for ur replies prerak,its working fine for some extent.
But i am having cells content>10 for some cells and<10 for some cells.I want whole name in the cell and format also should be correct.Is it possible to do it??great thanks for ur reply

Member 7940184 27-Jun-11 2:23am

Thanks for ur replies prerak,Its working fine for some extent.
But i am having cells content>10 for some cells and <10 for some cells.I want whole name in the cell and format also should be correct.Is it possible to do it??great thanks for ur reply

Prerak Patel 27-Jun-11 2:46am

You are welcome.
You should pad more than the longest value in the column. If the problem is solved, mark it as answer.

2irfanshaikh · Answer 2 · 2011-06-23T01:41:00

Solution 1

Instead on Spaces put Tabs between the two columns

Posted 23-Jun-11 1:41am

2irfanshaikh

Comments

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.