pasting text to excel

[saphiroth]

Hi, I was wondering if Visual Basic .NET can write text to a specific Excel cell.

What I wanted to do is print a report with a bunch of values from a database and was wondering if vb.net can incert those values into a specific cell in excel.

Thank you

 

[dace]

Yes, it can - you need to add a COM reference to the Microsoft Excel Object Library, and then you can programmatically create and modify Excel documents. See

http://www.vbdotnetheaven.com/Code/Jul2003/2124.asp

for starters.

 

[saphiroth]

Hi,
Thanks for the website...one thing im confused on is where or how i would create this dll file...I mean where would I put the TlbImp Excel9.olb Excel.dll statement? I tried putting it in the main or other subs but it game me errors.

thank you

 

[dace]

The easier way is to go to (from the main menu) Project > Add Reference, select the COM tab, and find the latest Microsoft Excel Object library you have. That way you don't have to use the type importer.

 

[saphiroth]

Hi,
Very sorry to bother you again. I dont have much knowledge in COM interfaces and how to build them but what you sugested worked. I have created a module and referenced Excel like you said to do except Im getting errors in

1) Dim workbook As _Workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0)
- Its giving me a problem with the 0. It says to many arguments.

2) Dim worksheet As _Worksheet = CType(sheets.get_Item(1), _Worksheet)
- This says that get_Item(1) is not a member of Excel.sheets.

3) Dim range1 As Range = worksheet.get_Range("C1", Missing.Value)
- This says that get_Range is not member of Excel._Worksheet

What I want to do with this is create a report from an Access database that I have. I need to create a certificate of compliance for a certian hardware and incert particular fields from the database into the Excel report.

Thank you again

 

[saphiroth]

Oh, by the way...I forgot the say that I would already have a premade excel sheet with logo and title, and other stuff, so that's why i need to insert certain text into certain excel cells.

 

[dace]

Hm, that link I sent you isn't all that great. Try looking at the Excel sample code here:

http://www.wizkil-webs.net/NET/DotNeT_Office_Samples.htm

-it should get you started off a bit better.

What you basically want to do is reference your excel file with an Excel.Application object, open the appropriate Excel.Workbook for the file, open the Excel.Worksheet where you want to put data, and then start using MyWorksheet.Range("A1").Value = ".....".

 

[saphiroth]

I have this now:

Imports System
Imports System.Reflection ' For Missing.Value and BindingFlags
Imports System.Runtime.InteropServices ' For COMException
Imports Excel

Module Excel1

Private Sub BulidExcelReports(ByVal intReport As Integer, ByVal strReportName As String)

Dim xlApp As Excel.Application
Dim xlWB1 As Excel.Workbook
Dim MyWorksheet As Excel.Worksheet
xlApp.Visible = false
xlWB1 = xlApp.Workbooks.Open("Gauge.xls")

MyWorksheet = xlWB1.Worksheet("Sheet1")
MyWorksheet.Range("A6").Value = "Gauge"
end sub
end module

would this put the word Gauge in to cell A6 or do I need to add something else? Also if i wanto to call this Sub from another Form..since this is a module all I would have to do is just call BulidExcelReports(), right? And do I need to privide arguments for BulidExcelReports() such as the first int...what ever that does.

And also will this work with any excel version such as Excel 2000?

thank you

 

[dace]

Something more like this model should help you:

 Private Sub AddToExcel() 
       Dim xlApp As Excel.Application
        Dim xlWB1 As Excel.Workbook
        xlApp = New Excel.Application
        xlApp.Visible = False
        xlWB1 = xlApp.Workbooks.Open("C:\myfile.xls")

        With CType(xlWB1.ActiveSheet, Excel.Worksheet)
            .Range("A1").Value = "....."
            .Range("A2").Value = "....."
        End With

        xlApp.Visible = True
        xlApp.ActiveWorkbook.Close(True)
        xlApp = Nothing
End Sub

 

[dace]

Whoops, you should possible add

 xlApp.Quit()

before

 xlApp = nothing

 

[saphiroth]

Thank you for you help, it works fine.

 

[saphiroth]

Is it possible to state in the code that that worksheet/workbook will not be saved when the application is closed instead of having the save changes popup appear, because the user will not see the actuall worksheet, also I put workbook into the bin folder of my project so it should just open when I provide just the name of the workbook such as "Gauge.xls: in xlWB1 = xlApp.Workbooks.Open("Gauge.xls"). But for some reason its not opening up. I donw know where the application will be installed to so I need to be able to do this.

Thank you