[Resolved]How to get save excel file

[vbPoet]

I have an excel file with some data already entered into it.
now i want to mention row and column of the sheet and want to save whatever in text box ..
how to do it ..?

[vbPoet]

Like i want to save data in textbox Text1 into c:\anyname.xls Row 25 Column C.......

[trisuglow]

ActiveSheet.Cells(2, 2).Formula = InputBox("Enter text")

[trisuglow]

Are you coding this using VBA in Excel, or is it a separate VB executable?

[vbPoet]

Are you coding this using VBA in Excel, or is it a separate VB executable?

It is separate Vb executable ..
but i want some more assistance how to open excel file with already saved some data and how to save it altogether after adding more data...

[trisuglow]

This should write the entered text into C25 on Sheet1.

Option Explicit
 
Private Sub Form_Load()
  Dim xl As New Excel.Application
  xl.Workbooks.Open "C:\anyname.xls"
  xl.Worksheets(1).Cells(25, 3).Formula = InputBox("Enter Text")
  xl.Save
  xl.Quit
End Sub

[trisuglow]

Forgot to mention - you have to go to Project\References and add a reference to "Microsoft Excel Object Library"

[vbPoet]

But there is one problem with this code..
it wants to replace original file .
but i just want to add more data ...
what to do ..?

[trisuglow]

If you want to add more data then you will have to overwrite the exisiting file. Even if you knew which byte(s) in the file you wanted to change and just wrote over them you would still have to open and close the file (effectively overwriting it).

[vbPoet]

Previous problem is solved ..
but here is new one..
i have some Text data in Text Box when i m saving it to ( 25,1) in excel file only "#VALUE!" is displaying ...
How to solve this last problem ..?

[doofusboy]

You could try changing:

xl.Worksheets(1).Cells(25, 3).Formula = InputBox("Enter Text")

to:

xl.Worksheets(1).Cells(25, 3).Value = InputBox("Enter Text")

[trisuglow]

vbPoet, It sounds like the cell type is set up as Numeric rather then General. You can either change the cell type to General, or make sure you only write numeric values into it. From memory, it may also be to do with the value that Excel is trying to display being too wide (ie too many significant digits) to fit in the cell.

[vbPoet]

You could try changing:

xl.Worksheets(1).Cells(25, 3).Formula = InputBox("Enter Text")

to:

xl.Worksheets(1).Cells(25, 3).Value = InputBox("Enter Text")

of no use ...
my exact line of code is

xl.Worksheets(1).Cells(25, 1).Value = Text2

in Text2 there is nearly 700 words long text ...
instead of displaying text
in excel file there is just #VALUE! written.

[vbPoet]

vbPoet, It sounds like the cell type is set up as Numeric rather then General. You can either change the cell type to General, or make sure you only write numeric values into it. From memory, it may also be to do with the value that Excel is trying to display being too wide (ie too many significant digits) to fit in the cell.

Yes this is wide data ...
How to solve this issue ..?

[trisuglow]

Read the Excel help - there is a section under the "#VALUE!" keyword.

[vbPoet]

Read the Excel help - there is a section under the "#VALUE!" keyword.

The #VALUE! error value occurs when the wrong type of argument or operand is used

but there is some text only TEXT in text box Text2 .. then what could be the problem ...
I have checked by inserting small amount of text like 400 characters it is working well but when i insert more than 600 characters it starts to display #VALUE!.

[trisuglow]

Try creating a new spreadsheet and see what happens with that. I've just done exactly this and it works fine with long strings (>600 characters) of text. Let me know the result - if it works then try to work out what the difference is between the spreadsheets.

[vbPoet]

Try creating a new spreadsheet and see what happens with that. I've just done exactly this and it works fine with long strings (>600 characters) of text. Let me know the result - if it works then try to work out what the difference is between the spreadsheets.

same problem
#VALUE!

[vbPoet]

how long data Excel can support in one cell ..?

[trisuglow]

OK.

I opened Excel and created a new, blank spreadsheet and saved it as C:\Book1.xls.

Then I ran this code. None of the cells said #VALUE!

Can you do exactly the same thing? If you don't get the same result as me then Email me the spreadsheet and I'll try to see what is different.

Option Explicit
 
Private Sub Form_Load()
  Dim xl As New Excel.Application
  Dim vLoop As Long
  Dim vStr As String
  xl.Workbooks.Open "C:\book1.xls"
  For vLoop = 1 To 1000
    vStr = vStr & "word "
    xl.Worksheets(1).Cells(vLoop, 3).Formula = vStr
  Next
  xl.Save
  xl.Quit
End Sub

[vbPoet]

OK.

I opened Excel and created a new, blank spreadsheet and saved it as C:\Book1.xls.

Then I ran this code. None of the cells said #VALUE!

Can you do exactly the same thing? If you don't get the same result as me then Email me the spreadsheet and I'll try to see what is different.

Option Explicit
 
Private Sub Form_Load()
  Dim xl As New Excel.Application
  Dim vLoop As Long
  Dim vStr As String
  xl.Workbooks.Open "C:\book1.xls"
  For vLoop = 1 To 1000
    vStr = vStr & "word "
    xl.Worksheets(1).Cells(vLoop, 3).Formula = vStr
  Next
  xl.Save
  xl.Quit
End Sub

OOPS
Your code is showing that u r changing rows as well
but i want to store 1000+characters in one cell...
i think that is the difference ...

[trisuglow]

But by the time the code I posted gets to row 1000 vStr will have built up to be 5000 characters long.

What happened when you tried what I suggested? Were you still getting #VALUE! ?

[vbPoet]

Your code is working very well ....in Book1.xls
but #Value! in previous file is still unsolved.

[trisuglow]

So what are the differences between Book1.xls and the other spreadsheet you are using? If you want to post both spreadsheets I'll have a look for you if you can't see a difference.

[vbPoet]

exactly nothing..
i cannot post spreadsheets becoz these are sensitive.

[trisuglow]

Well, there must be some difference - what happens when you run my code from post number 21 against your real spreadsheet - do you still get the #VALUE! ?

What happens if you delete all the data from your sensitive spreadsheet - do you still get the same problem? If so, can you post the spreadsheet with the data deleted?

[vbPoet]

#21 code was tested with Book1.xls and it was quite good without any error.
but my original problem is with anyname.xls where data from text2 is going into column "A".
Text2 contains nearly 25 lines each line contain nearly 5-20 characters.
and line is separated with Chr(13) & Chr(10).
I don't know where is problem...
When i tested by reducing lines of Text2 to 10-13 it works good.but when i increase to 14-25...exactly same error returns back "#VALUE!"
Really Gettin Mad

[dannymking]

The maximum number of characters a cell in Excel is 32,767... so it does not sound as though the problem is the contents in the cell as it should show this..

What are the first few charaacters you are sending? If this begins with "=" then that could be the problem, as excel is trying to evaluate a formula.

[vbPoet]

No text2 contains
text data only

[dannymking]

Ok then could you post a sample of the text data in text2?

[vbPoet]

Thank You All
specially Trisuglow
it was trimming problem simply Bull****