Database link to worksheet!

K

Kim-Anh Tran

I have a database contains personal information and a blank Exce
template on the network. I would like to have this happen:
Whenever I enter the individual information to the database, I ca
click a button to have some of these information also migrate to thi
blank Excel template and allow me to save the new worksheet with th
individual information.
Your helps are well appriciated!
Regards,
Kim-Anh Tra
 
J

Jamie Collins

Kim-Anh Tran wrote ...
I have a database contains personal information and a blank Excel
template on the network. I would like to have this happen:
Whenever I enter the individual information to the database, I can
click a button to have some of these information also migrate to this
blank Excel template and allow me to save the new worksheet with the
individual information.
Click to expand...

In short, yes.

Slightly longer:

- You can use an Excel workbook as a data source e.g. using ADO and
the MS OLE DB provider for Jet and query the workbook using SQL.

- The Jet provider treats .xls and .xlt files the same.

- In addition to SQL queries, the Jet provider, when used with an
Excel source, supports INSERT INTO and UPDATE syntax, but not DELETE.
It also supports limited DDL syntax e.g. CREATE TABLE and DROP TABLE.

- If your database is Jet (MS Access ,.mdb, etc) or Excel, you can
specify the Excel workbook in queries e.g. the following in queries in
the MS Access UI:

CREATE TABLE
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
(
MyNumCol INTEGER,
MyTextCol VARCHAR(10),
MyDateCol TIMESTAMP
)
;

INSERT INTO
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
(MyNumCol, MyTextCol, MyDateCol)
VALUES (37000, 37000, 37000)
;

SELECT
MyCol1, MyCol2, MyCol3
INTO
[Excel 8.0;database=C:\New2.xls;].Sheet1
FROM
MyTable
;

The same applies to SQL Server to some extent in that it has
OPENROWSOURCE and OPENROWSET functions to access an *existing* Excel
workbook.

- By automating Excel, you can do just about anything you want to the
workbook!

Jamie.

--
 
K

Kim-Anh Tran

it's too advanced for me. The term ADO and the Jet provider is new t
me. I am a basic Excel and Database user! Would you please explain i
more simple term. I will find book to learn. I can see this will be m
long term project.
I appreciate any help!
Regards,
Kim-An
 
J

Jamie Collins

Kim-Anh Tran wrote ...
it's too advanced for me. The term ADO and the Jet provider is new to
me. I am a basic Excel and Database user!
Click to expand...

Which database product are you using?

Jamie.

--
 
J

Jamie Collins

Another thought. I've assumed you want the database to notify the
template when something changes in the database, so that when a
workbook is created from the template it will have the current data.
As an alternative, you could, at the time a workbook is created from
the template, get the workbook to query the database. This is how
databases are normally used i.e a client application queries a
database server for the current data, rather than the other way round!

Jamie.

--
 
K

Kim-Anh Tran

Thank you so much for your help! I really want to learn how to do this
I have a database that we log in who ever call in to apply for ou
program. For everyone who apply, I need to create a workbook from
template that I create. For now I have to retype some information fro
the database to this worksheet like name, address, gender, DOB,ect
Would you teach me the easiest way to accomplish this. If I need t
create a macro, I would prefer to have macro in database and not creat
any macro from the workbook. Because some in experience user ma
disable macro and my formulas will not work. I am using Microsof
database 2000.
Thank you so much in advance for your time and patience in teachin
me!
Regards,
Kim-Anh
[email protected]
 
J

Jamie Collins

Kim-Anh Tran wrote ...
I am using Microsoft database 2000.
Click to expand...

I don't understand. Do you mean MS SQL Server 2000 or MS Access 2000
or something else? Sorry to ask again but it does make a difference.

Jamie.

--
 
K

Kim-Anh Tran

I am sorry! I must thiking of something at the same time! I am using MS
Access 2000. I need to clarify that I only need to create a workbook
using the personal data from database once. Once I have the worksheet
for the individual, we do update on this worksheet. No update will be
done in the database. The database only tracking all calls come in.
Thanks again,
Kim-Anh
 
J

Jamie Collins

Kim-Anh Tran wrote ...
I am using MS
Access 2000. I need to clarify that I only need to create a workbook
using the personal data from database once. Once I have the worksheet
for the individual, we do update on this worksheet.
Click to expand...

Deferring to my earlier, you can do this in a query. In MS Access,
open a query object, go the SQL view, paste in the following queries
and amend the details (columns, tables, workbook path etc) to suit
your situation.

To update an existing Excel table:

INSERT INTO
[Excel 8.0;database=C:\MyWorkbook.xls;].MyBookLevelName
(MyCol1, MyCol2, MyCol3)
SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
FROM
MyTable
;

To create a new Excel workbook/worksheet/table:

SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
INTO
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
FROM
MyTable
;

Jamie.

--
 
K

Kim-Anh Tran

I appreciate so much for your help! I am having problem linking th
database in my local computer. May I ask you how do I link my databas
to another database on the network?
Thanks again for your help! And have a wonderful day!
Regards,
Kim-An
 
Top