Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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. 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. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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! Which database product are you using? Jamie. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
Kim-Anh Tran wrote ...
I am using Microsoft database 2000. 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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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 --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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. 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. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database link to worksheet!
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link to access database | Excel Discussion (Misc queries) | |||
Link to SQL Database | Excel Worksheet Functions | |||
Moved database, how do I change link to the database in excel | Excel Discussion (Misc queries) | |||
Database Link | Excel Discussion (Misc queries) | |||
Removal of link to database | Excel Discussion (Misc queries) |