Writing to Excel

[samtaylor08]

Hi all
I would like to write to excel, i know how to do the basics like the actual writing. But I do no want to specify a cell to write to I want it to go to the next row with no text in the cells can someone help me on this. I would also like it to be done behind the scenes, like when you use stream writer. But I suppose it would have to be opened to find the available row to write in. Help would be much appreciated.

Sam

[RobDog888]

There is a built in function in Excel that will do that but then you would have to be using the Excel Object Model. How about if you use a SQL INSERT INTO statement to add a row? Will it place it at the end of your rows?

[samtaylor08]

Could you show me an example where you click a button, it a number from a text box and puts it into to column A and the next available row.

Dim objExcel As New Excel.Application
 
        objExcel.Visible = True
 
        objExcel.Workbooks.Add()
 
        objExcel.Range("A1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "75"
        objExcel.Range("B1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "125"
        objExcel.Range("C1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "255"
        objExcel.Range("D1").Select()
        objExcel.ActiveCell.FormulaR1C1 = "295"
 
       
        objExcel = Nothing

I have leant ho to do this with a book I have, but what I want to do is open an existing excel file and add to it. It would be a great help if you can give me a very simple example.

Sam

[RobDog888]

Dim lRow As Long
 
lRow = objExcel.Sheets("Sheet1").Range("A1").SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
Messagebox.Show("Last Row: " & lRow.ToString, "VB Forums - Excel")

[samtaylor08]

Thanks Alot this work perfectly, just add one to the lRow so it writes to the next line.

Sam

[samtaylor08]

One more thing how would I close Excel and save what I have put in there.
This is my code:

Dim objExcel As Microsoft.Office.Interop.Excel.Application
        Dim lRow As Long
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
 
        Try
 
            objExcel = New Microsoft.Office.Interop.Excel.Application
            wb = objExcel.Workbooks.Open("c:\test.xls")
            objExcel.Visible = True
            lRow = objExcel.Sheets("Sheet1").Range("A1").SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
            MessageBox.Show("Last Row: " & lRow.ToString, "VB Forums - Excel")
 
            lRow = lRow + 1
            objExcel.Range("A" & lRow.ToString).Select()
            objExcel.ActiveCell.FormulaR1C1 = "75"
            
        Catch ex As Runtime.InteropServices.COMException
            MessageBox.Show("Error accessing Excel: " + ex.ToString())
 
        Catch ex As Exception
            MessageBox.Show("Error: " + ex.ToString())
 
        End Try

Sam

[RobDog888]

Just call the Close method and pass the save argument and filename.

wb.Close SaveChanges:=True, FileName:="C:\MyBook.xls"

[samtaylor08]

Hi Rob
Ran in to slight problem with the Save how do I do the overwrite part.

Sam

[RobDog888]

Oh ok you have a previously savesd xls file. Then we can use the .SaveAs method to overwrite.

wb.SaveAs FileName:="C:\MyBook.xls", FileFormat:=xlWorkbookNormal
wb.Saved = True
wb.Close SaveChanges:=False

[samtaylor08]

Sorry to be such a pain, but there is a error that says Error 'xlWorkbookNormal' is not declared.?????

Sam

[samtaylor08]

I used this instead

wb.Save()
        wb.Saved = True
        wb.Close(SaveChanges:=False)
        objExcel.Quit()

This ok?

How could I tell if when the file is opened whether its read only or not, the reason I ask this is because two people will be writing to this file. But if one has it open the other will not be able to save, I want this to come up with a msg saying already open cannot write until closed. Is this possible?

Sam

[RobDog888]

Its because I forgot the .NET fully qualified object path to the const.


you can do it that way too if its not a new file. If the other user has the workbook open then is it a shared file or are you getting the "Read Only Notification" dialog?

[samtaylor08]

This file will be on a network, it is basically a quote log. So if when person has got it open it will be read only and the app would now be able to add the quote. So I want to be able to check if it is open so it can be closed. Understand?

Sam

[RobDog888]

Yes I understand but if the workbook is not designated as a Shared Workbook then the users and your code will encounter the message that it is opened Exclusively by another user and if they want to open the file as Read Only.

[samtaylor08]

Yes it does open as Read-Only, if someone else has it open it opens as read only is there a way you can stop this and enable people to save when there are two open.

Also how do you take the content of a cell and then use it, I also want to be able to split this as well eg say the content was EL11-12-RRR and I wanted to take the 11. Can you show me how to do this? Appreciate this help alot.

Thanks

Sam

[RobDog888]

Excel 2003 and earlier: Go to Tools > Share Workbook. This will allow editing by multiple users at the same time and also merge workbooks too.

Excel 2007: Go to Review ribben tab > Share Workbook.

[samtaylor08]

So just to clarify for me, when this is done. Two people will be able to edit the spreadsheet and save it at different times but it will have both sets of data in. True?

Office 07 is not out yet in the UK dont think, will not be getting it for a while either because it is 70% more expensive here than in the US. Just a massive rip off

Sam

[RobDog888]

I havent been in an environment to be able to test it but its supossed to allow both to make changes. Doesnt specify if it will get refreshed or if they have to reopen it or ?

[samtaylor08]

Hi Rob

Can you help me with this? I have got what you have told me working so far. But I now need to write to a cell from two listboxes, this is what I want to happen:

In the cell I want:
The first item in listbox 1 the , and the first item in listbox 2. Then one a new line I would like the second items in the same format.

Help would be much appreciated

Sam

[RobDog888]

'Write to sheet1, cell A1 with the value of the first item in the listbox 
wb.Sheets("Sheet1").Cells(1, 1).Value = Me.Listbox1.Items(0).ToString

[samtaylor08]

Hi Rob
I have got this code, I want it to write first line of listbox1 the a , and then the first line of listbox2, then I want it to go to a new line. This is my code, but it overwrites each time obviously, but I didn't know how to do append the cell so wrote this so someone could modify:

objExcel.Range("F" & lRow.ToString).Select()
            For index = 0 To lstproname.Items.Count - 1
                objExcel.ActiveCell.FormulaR1C1 = lstproname.Items(index)   & _    ", " & lstproprice.Items(index) &  vbNewLine
                index = index +1
            Next

Thanks

Sam

[RobDog888]

I dont quite follow how you need it to be. Could you give an example?

[samtaylor08]

Right I have 2 listboxes, on for product and one for the price of the product.

So What I want is to list the contents of the listboxes into a cell.

So for example I will write to A1 with how I want it:
Product 1, Price 1
Product 2 , Price 2
Product 3, Price 3
and so on till all of the items are listed in that cell.

But what happens now is the last product and the last price are the only ones listed, because they overwrite the rest.

Its quite hard to explain, hope this helps you a little more to understand what I want.

Thanks

Sam

[RobDog888]

Oh ok. Its because you are not changing the cell that you are writting to. You are always writting to the ActiveCell which is not changing. Use an .Offset or just reference the Cells(x, 1) where x is the row number increasing in your loop.

[samtaylor08]

I want to write to the same cell eachtime so it stays on the same row. Can this be done I want each product and price one a different line thats why I put vbnewline but in the same cell. Can this be done?
Sam

[RobDog888]

Just reference the cell location as I suggested in a loop that will place each item on a new row. Make a secondary call to place another item on the same row but reference the next column by increasing the Y Cells value.

[samtaylor08]

I am not sure I understand this, if I put the cell bit in the loop, it will change the cell I am writing to and I want the whole listbox in 1 cell.

Sam

[RobDog888]

Ok how about an example of how you want it because I thought you wanted it in each cell and then each row as the record changes. Seems you are asking for everything in a single cell which is what you had in th beginning?[/color]

[samtaylor08]

Right I have uploaded a spreadsheet as an example, I had to do this is word then copy it across because when you try to press enter in excel it takes you to the cell underneath when I want a new line in that cell. Do you understand what I am after?

Sam

[RobDog888]

I'll look at it in a minute, but did you know that if you press Alt+Enter you will enter a line break and remain editing that same cell instead of moving out of edit mode and mving to the next cell.

[RobDog888]

So you want everything in the same cell but 2 items per line in that cell.

Instead of writting to the formula property of the cell in a loop, just write it all out to a string variable in your loop and then assign the cells value to that string variable after the loop is complete.

[samtaylor08]

Can you give me an example not sure what you mean?

But that is what I want.

Sam

[RobDog888]

Sure, heres a quick example

Dim str As String
for loop goes here
    str = str & lstproname.Items(index)   & ", " & lstproprice.Items(index) &  vbNewLine
done with loop
cells(1, 1).Value = str

[samtaylor08]

Thanks Alot understand now seems so simple when you know how.

Sam

[samtaylor08]

This works but there is a square at the end of each line is there a way to stop this I think it the vbnewline.

Sam

[RobDog888]

Ah, yes its the vbNewline or essentially the carriage return. I have seen this before but let me see if there was a workaround or not.

It could be done by entering items in separate rows and then .Merge the rows together.

[samtaylor08]

I am trying to loop though a spread sheet but I want to be able to use a 1 for the column A instead and so on for the rest.
This is so I can use a variable instead of the actual letter.
This is how I am doing it:

xl.Range(col & frow).Select()
            file = xl.ActiveCell.Text

But it comes up with a Comexception was not handled:
System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2146827284
Message="Exception from HRESULT: 0x800A03EC"
Source="Microsoft.Office.Interop.Excel"
StackTrace:
at Microsoft.Office.Interop.Excel.ApplicationClass.get_Range(Object Cell1, Object Cell2)
at itext.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Sam Taylor\My Documents\Visual Studio 2005\Projects\itext\itext\Form1.vb:line 57
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at itext.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Can someone help me with this?

Thanks

Sam

[RobDog888]

Well as shown in the error, you need to specify the two cells that will make up the range.

Range(Object Cell1, Object Cell2)

[samtaylor08]

col is the column and frow is the row. The problem is I need them both to be numbers so I can use a loop, but it will not let me because col needs to be a letter I was wondering if there was away around it?

Sam

[RobDog888]

Use the format I posted as you can use only numbers for both row and column.

oCell1 = [i]Sheet1[/i].Cells(1, 1)
oCell2 = [i]Sheet1[/i].Cells(2, 1)
[i]Sheet1.[/i]Range(oCell1, oCell2).Select()
'...
'But you dont need to "Select" the cell as it slows down 
'your code and also because you can just reference the cell.
 
file = [i]Sheet1.[/i]Range(oCell1, oCell2).Value