Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

populate excel from vb 1

Status
Not open for further replies.

5679

Programmer
Feb 14, 2003
32
AU
I have a VB application. How can i put a value in an independant excel worksheet from the VB application.
 
First enable "Microsoft Excel 9.0 Object Library" and "Excel ctl Type Library" in Project -> Referances

Here is some very simple code that will allow you to open an excel application, set and read cell values and close the application. Put it in a module and try calling the functions. This should get you started
----------------------------------------------------
Dim x1 As Excel.Application
Dim x1Wb As Excel.Workbook
Dim x1Sh As Excel.Worksheet

Sub OpenExcelFile()
Dim filename As String
Set x1 = CreateObject("Excel.application")
filename = x1.GetOpenFilename
Set x1Wb = x1.Workbooks.Open(filename)
Set x1Sh = x1.Worksheets("Sheet1")
x1.Visible = True

End Sub


Sub CloseWorkbook(SaveIt As Boolean)
x1.ActiveWorkbook.Close savechanges:=SaveIt
End Sub

Sub CloseExcel()
x1.Quit
End Sub

Sub CloseForm()
Call CloseExcel
ExcelForm.Hide
End Sub



Sub SetCellValue(cell As String, value As Variant)
x1Sh.Range(cell).value = value
End Sub

Sub MakeCellActive(cell As String)
x1Sh.Range(cell).Select
End Sub

Function ReadActiveCell() As Variant
ReadActiveCell = x1.ActiveCell.value
End Function

Sub SetCellActiveRelative(RowOffset As Integer, ColOffset As Integer)
x1.ActiveCell.Offset(RowOffset, ColOffset).Select
End Sub

 
Using ADO:
Code:
dim conn as adodb.connection
set conn = new adodb.connection
with conn
 .Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
 .ConnectionString = "Data Source=C:\excel\customers.xls; Extended Properties=Excel 9.0;"
 .Open
end with

dim rst as adodb.recordset
set rst = new adodb.recordset
rst.open "Sheet1$",conn,adopendynamic,adlockoptimistic, adcmdTableDirect

s'cuse the typos if any
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top