DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

EXCEL as a Database

•

Join Date: Apr 2007

Posts: 22

Reputation: t3hfr3ak is an unknown quantity at this point

Solved Threads: 0

t3hfr3ak

Offline

Newbie Poster

EXCEL as a Database

Jul 2nd, 2007

I know theres a menu button for it somewhere but i cant seem to remember how to do it.

This is for my movie information Database (I dont know if any of you remember when I was asking about storing the data in a txt file. Well now I am trying to get it in a EXCEL document. Any help would be appreciated.

•

Join Date: Jan 2007

Posts: 319

Reputation: DenisOxon is an unknown quantity at this point

Solved Threads: 15

DenisOxon

Offline

Posting Whiz

Re: EXCEL as a Database

Jul 2nd, 2007

Hi,

For those of us who don't remember what are you trying to do ?

Denis

•

Join Date: Apr 2007

Posts: 22

Reputation: t3hfr3ak is an unknown quantity at this point

Solved Threads: 0

t3hfr3ak

Offline

Newbie Poster

Re: EXCEL as a Database

Jul 2nd, 2007

•

Originally Posted by DenisOxon

Hi,

For those of us who don't remember what are you trying to do ?

Denis

I am making a movie information program Which has 3 forms

Form 1: The Main form where it tells you basic info about the program and 3 command buttons.

Form 2: Used to enter the information about the movie into text boxes to save into a file for later use

Form 3: Used to view the information entered previously

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: EXCEL as a Database

Jul 3rd, 2007

Hi,

Check This Code:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Private Sub cmdOpenExcel_Click()
  '
  Dim ACn As New ADODB.Connection
  Dim RST As New ADODB.Recordset
  '
  With ACn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\MyExcel.xls ;Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
  End With
  '
  SET RST =Nothing
  RST.Open "select * from  [XL_SHEETNAME]", ACn, adOpenDynamic, adLockReadOnly
  Do While Not RST.EOF
     Debug.Print RST(0)
     RST.MoveNext
  Loop
  RST.Close
  ACn.Close
  '
End Sub
Private Sub cmdOpenExcel_Click()
  '
  Dim ACn As New ADODB.Connection
  Dim RST As New ADODB.Recordset
  '
  With ACn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\MyExcel.xls ;Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
  End With
  '
  SET RST =Nothing
  RST.Open "select * from  [XL_SHEETNAME]", ACn, adOpenDynamic, adLockReadOnly
  Do While Not RST.EOF
     Debug.Print RST(0)
     RST.MoveNext
  Loop
  RST.Close
  ACn.Close
  '
End Sub

Regrads
Veena

•

Join Date: Apr 2007

Posts: 22

Reputation: t3hfr3ak is an unknown quantity at this point

Solved Threads: 0

t3hfr3ak

Offline

Newbie Poster

Re: EXCEL as a Database

Jul 3rd, 2007

hmm... that honestly doesnt make too much sense too me... care to elaborate?

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: EXCEL as a Database

Jul 4th, 2007

Hi,

i thought u wanted to connect to Excel thru "ADO" onnection..

Regards
Veena

•

Join Date: Apr 2007

Posts: 22

Reputation: t3hfr3ak is an unknown quantity at this point

Solved Threads: 0

t3hfr3ak

Offline

Newbie Poster

Re: EXCEL as a Database

Jul 4th, 2007

no i want to write and read variables to a spreadsheet

•

Join Date: Jun 2007

Posts: 95

Reputation: manoshailu is an unknown quantity at this point

Solved Threads: 9

manoshailu manoshailu is offline

Offline

Junior Poster in Training

Re: EXCEL as a Database

Jul 5th, 2007

hi,

'declare the objects for connection and recordset
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

'Create object for connection
Set con = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\ioc\excel\ioc.xls;" & _
"Extended Properties=Excel 8.0;"

'apply connection string
con.ConnectionString = strcon

'Open the Connection
con.Open

'Create object for recordset
Set rst = New ADODB.Recordset

'Exp i use table employee
rst.Open "select * from employee", con, adOpenDynamic, adLockOptimistic

'To add a record
rst.AddNew
rst(0) = Trim(Combo1.Text)
rst(0) = Trim(Text2.Text)
rst(2) = Trim(Text1.Text)
rst.update

Same thing for search,update and delete records. Try this coding

al the best
shailu

•

Join Date: Jan 2007

Posts: 319

Reputation: DenisOxon is an unknown quantity at this point

Solved Threads: 15

DenisOxon

Offline

Posting Whiz

Re: EXCEL as a Database

Jul 5th, 2007

•

Originally Posted by t3hfr3ak

I am making a movie information program Which has 3 forms

Form 1: The Main form where it tells you basic info about the program and 3 command buttons.

Form 2: Used to enter the information about the movie into text boxes to save into a file for later use

Form 3: Used to view the information entered previously

Hi,

A question about each form

1) what are three buttons for ?

2) what file are you going to save them into ?

3) Is this displaying a spreadsheet ?

Denis

•

Join Date: Apr 2007

Posts: 22

Reputation: t3hfr3ak is an unknown quantity at this point

Solved Threads: 0

t3hfr3ak

Offline

Newbie Poster

Re: EXCEL as a Database

#10

Jul 5th, 2007

I know theres a none code way to do it if you follow buttons in VB. I was doing it before there was a "Create database using excel or foxpro or access or a text file ect.

1) The three buttons are on the MAIN form.
Button 1. Displays the input form to enter movie info and store the variables to a text file (I would like to store them to an excel file)
Button 2. Displays the form where you pick a movie and it will display on the information about the movie
Button 3. Closes the project

3) No it does not display a spreadsheet, I just want it to take the variables FROM the spreadsheet and throw them in a few labels.