Member Avatar for S.N.Mazumder

I prepared a simple database in ms excel. Now I want to link it with ms visual basic 6.0. I want to enter data in ms visual basic form and it will update my excel database/ spreadsheet. How to link a record in ms excel with visual basic 6.0 ? And How to enter data on visual basic from?

Plz help me, ...anyone


Regards
S.N.Mazumder Amit

  • 6 Contributors
  • 8 Replies
  • 6K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by rexgb_34

Dani AI

Generated

If you just need to push form data into Excel or Access from VB6, ADO is simpler and more robust than automating the Excel UI or using the intrinsic data control. In Project > References, tick "Microsoft ActiveX Data Objects 2.x Library". For Excel 97-2003 workbooks (.xls), add column headers in row 1 and use the sheet name with a trailing $.

Example: write a row to Sheet1 in Book1.xls.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\Book1.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"""
cn.Execute "INSERT INTO [Sheet1$] ([EmpName],[Amount]) VALUES ('Amit', 1000)"
cn.Close

For Access 2000-2003 databases (.mdb), use Jet 4.0 via ADO and parameterize inserts. Also double-check the path syntax (for example, E:\Salary in Access.mdb, not E...). The error "Unrecognized database format" typically means the provider does not match the file type.

Dim cn As ADODB.Connection, cmd As ADODB.Command
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Salary in Access.mdb;"
Set cmd = New ADODB.Command: Set cmd.ActiveConnection = cn
cmd.CommandText = "INSERT INTO Employees (EmpName,BasicPay) VALUES (?,?)"
cmd.Parameters.Append cmd.CreateParameter(, adVarChar, adParamInput, 50, txtName.Text)
cmd.Parameters.Append cmd.CreateParameter(, adCurrency, adParamInput, , CCur(txtPay.Text))
cmd.Execute
cn.Close

Notes:

  • For .xlsx or .accdb, use the ACE provider ("Microsoft.ACE.OLEDB.12.0") instead of Jet.
  • In Excel, the first row defines column names; wrap names in [brackets] and include the $ after the sheet name.

Recommended Answers

All 8 Replies

Member Avatar for vb5prgrmr

Okay, there is more than one way in which to use excel with visual basic 6.0. Now, you said as a database so you will need to use your friends (yahoo, google, ask, answers, bing) and search for vb6 ado tutorial. Then see http://www.connectionstrings.com for a DNS LESS connection string that you will need for your ADO connection.

Also, you could use your friends to search for vb6 automating excel tutorial...

Good Luck

Member Avatar for S.N.Mazumder

How to link or connect MS Access 2003 database with MS Visual Basic 6.0 for creating salary sheet?

Please anyone help me in datails. Or suggest me, Which verson of visual basic I can use as a beginner in programming side.

From
Amit

Member Avatar for abu taher

How to link or connect MS Access 2003 database with MS Visual Basic 6.0 for creating salary sheet?

Please anyone help me in datails. Or suggest me, Which verson of visual basic I can use as a beginner in programming side.

From
Amit

I can tell you a very simple way. like:
put a data control in your form. go to data control properties. select database name and then select the record source ( table name).
one thing you need service pack 6 (vb) for use office 2003.
you can use visual basic 6

Member Avatar for S.N.Mazumder

I did the work before that you advised. But when I click on record source the following message is shown: unrecognized database format ' E\Salary in Access.mdb'.
I use MS Access 2003 and MS Visual Basic 6.0. Isn't it possible?

Now what problem may arise here? Plz suggest me anyone.

Member Avatar for abu taher

u need service pack 6 of vb6. download it from Microsoft site.

Member Avatar for venice yu

how to connect VB6.0 to MS Excel 2003

Member Avatar for AndreRet

venice yu, please read our posting rules HERE. We do not allow the "theft" of others posts here on Daniweb.:)

Please open your own thread (at the top of the forum page) and we will gladly help you from there.;) I will not post a solution on this post.

Member Avatar for rexgb_34

'Check this out. VB6 interact with Excel

Dim xl As excel.Application
Dim sAmount As String
Private Function getTotalSales()
sAmount = Str(xl.ActiveSheet.Range("SalesTotal").Value)
Label1.Caption = "Total Sales: " & "P " & Trim$(FormatNumber(sAmount))
Text3.Text = Str(xl.ActiveSheet.Range("JanSales").Value)
Text2.Text = Str(xl.ActiveSheet.Range("FebSales").Value)
Text1.Text = Str(xl.ActiveSheet.Range("MarSales").Value)
End Function

Private Sub Command1_Click()
xl.Visible = True
End Sub

Private Sub Command2_Click()
tsentry = Val(txtJan) + Val(txtFeb) + Val(txtMar)
MsgBox ("P" & FormatNumber(tsentry) & " sales added.")

xl.ActiveSheet.Range("JanSales").Value = (xl.ActiveSheet.Range("JanSales").Value) + Val(txtJan.Text)
xl.ActiveSheet.Range("FebSales").Value = (xl.ActiveSheet.Range("FebSales").Value) + Val(txtFeb.Text)
xl.ActiveSheet.Range("MarSales").Value = (xl.ActiveSheet.Range("MarSales").Value) + Val(txtMar.Text)
txtJan.Text = ""
txtFeb.Text = ""
txtMar.Text = ""
getTotalSales

End Sub

Private Sub Command2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Command2.Font.Bold = True Then
Command2.Font.Bold = False
Else
Command2.Font.Bold = True
End If
End Sub


Private Sub Command4_Click()
xl.ActiveWorkbook.PrintOut
End Sub


Private Sub Form_Load()
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open (App.Path & "\Sales.xls")

getTotalSales

End Sub

Private Sub Form_Unload(Cancel As Integer)

xl.ActiveWorkbook.Save
xl.Quit
Set xl = Nothing

End Sub
Edited by peter_budo because: Keep It Clear - Please use code tags when posting any code
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.