PDA

View Full Version : Solved: Writ data to Excel problem



Nader
11-30-2011, 01:16 PM
Hi I'm using vb.net
I tried this code to write data to Excel File, already open.but didn't success with me.may you help
Dim exl As New Excel.Application Dim exlWorkSheet As Excel.Worksheet exlWorkSheet = exl.Workbooks("ta1").Worksheets("Sheet") exlWorkSheet.Cells(2, 6).Value = "hello"

Paul_Hossler
11-30-2011, 05:58 PM
Just guessing, but in VBA you need the 'Set' to reference objects


Dim[/COLOR] exl As New Excel.Application
Dim exlWorkSheet As Excel.Worksheet
Set exlWorkSheet = exl.Workbooks("ta1").Worksheets("Sheet")
exlWorkSheet.Cells(2, 6).Value = "hello"


Paul

GTO
11-30-2011, 07:29 PM
Hi there,


Hi I'm using vb.net...
As Paul mentioned, we are using VBA, not VB.Net.


I tried this code to write data to Excel File, already open.but didn't success with me
Okay, presumably you mean that the file is already opened in the same instance of Excel that the code is running in. You appear to be trying to reference the workbook in a new, auto-instanced, instance of Excel. I believe you'd need to open the file in the new instance (disregarding for the moment, reopening a file that is already opened).

With the file closed, I suppose you could do something like:

Sub ex()
Dim exl As New Excel.Application
Dim exlWorkSheet As Excel.Worksheet
Dim wb As Workbook

Set wb = exl.Workbooks.Open(ThisWorkbook.Path & "\wb.xlsx")
exl.Visible = True
Set exlWorkSheet = exl.Workbooks("wb.xlsx").Worksheets("Sheet2")
exlWorkSheet.Cells(2, 6).Value = "hello"
End Sub


I would not auto-instance though. You may find this interesting:
http://www.cpearson.com/excel/classes.aspx

Hope thta helps,

Mark

Nader
12-01-2011, 01:55 AM
Thank you for help!!