Results 1 to 2 of 2
Thread: InputBox (Excel/Access 2000)
2003-11-14, 23:41 #1
- Join Date
- May 2003
- tunbridge wells, Kent, England
- Thanked 0 Times in 0 Posts
InputBox (Excel/Access 2000)
Whilst working in an Excel spreadsheet, I need to prompt the user to enter text into an InputBox, then paste that value into the "PartnerDb" field of an Access table called "PersonalDatabase". So far I've managed to get the InputBox up and running but can't yet do anything with the result! This is it so far:
PDb = InputBox("Which Partner or Consultant?") I then need to do something like the following:
PDb = "INSERT INTO PersonalDatabase ( PartnerDb)"
This will be need to be a macro. Please could someone supply the missing bits for me!
2003-11-15, 00:52 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: InputBox (Excel/Access 2000)
You will have to use ADO or DAO to connect to the database. Here is code using ADO; I used late binding so that you don't have to set a reference in the Excel workbook. Substitute the name and path of the database you are using. I assumed that you are inserting a text value.<pre>Dim Pdb As String
Dim cnn As Object
Pdb = InputBox("Which Partner or Consultant?")
If Pdb = "" Then Exit Sub
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:AccessTest.mdb"
cnn.Execute "INSERT INTO PersonalDatabase ( PartnerDb ) VALUES ( " &
Chr(34) & Pdb & Chr(34) & ")"
Set cnn = Nothing</pre>