Link an Excel file to access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I'm trying to update my access data base from an Excel file. The Excel file
has few check boxes, when the user checks the check box in excel then the
Checkbox's caption should come to my opened data base. To receive this data,
in my form i've a text box.
Following is the code that I'm using in excel. when the CheckBox checked,
it's caption will show in Sheet1's B:11 Cell
I need this data(the one which shows in Cell B:11) in my access.
If CheckBox1.Value = 0 Then
Sheets("Sheet1").Range("B11").Value = ""
Else
Sheets("Sheet1").Range("B11").Value = CheckBox1.Caption
End If

Can any one help in this

Thanks
 
AN said:
The Excel file
has few check boxes, when the user checks the check box in excel then the
Checkbox's caption should come to my opened data base.
Following is the code that I'm using in excel. when the CheckBox checked,
it's caption will show in Sheet1's B:11 Cell
I need this data(the one which shows in Cell B:11) in my access.
If CheckBox1.Value = 0 Then
Sheets("Sheet1").Range("B11").Value = ""
Else
Sheets("Sheet1").Range("B11").Value = CheckBox1.Caption
End If
Click to expand...

Do you want to 'push' data to your database from within Excel? e.g.

If CheckBox1.Value = 0 Then
Sheets("Sheet1").Range("B11").Value = ""
Else
Sheets("Sheet1").Range("B11").Value = CheckBox1.Caption
Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
Con.Execute ("INSERT INTO Captions (caption) VALUES ('" &
CheckBox1.Caption & "')")
End If

BTW the table I used to test looks like this:

CREATE TABLE Captions (
caption VARCHAR(255) NULL,
entered DATETIME DEFAULT NOW NOT NULL
);

but I suspect you may want to do something more subtle. Post more
details.

Jamie.

--
 
Hi Jamie

Thanks for the reply, here I explain what I'm doing exactly.

I've a table.It has these fields SerialNo(Autonumber);Cleint
Name(Text);Remarks(Text).I'm using in Excel "Get External Data"; the data is
coming from my access file.I've given criteria as SerialNo so when my Excel
file refresh, it will pop-up for SerialNo.Then the give data will be dumped
in to Excel(Only one record).Here in my excel sheet i have given the check
boxes. What i am looking for is, when I check the check box, the system
should take the checkbox caption to my Access file's remark field and that
too in the corresposing record, for eg: if my excel file has the SerialNo
150, then text should be added in SerialNo 150 in access file. So the link
btween Access and Excel is the SerialNo

Can yu please help me.....
 
Back
Top