Place Data to a Closed Workbook from a VBA form
Is there a way to place data to a closed workbook from a VBA form? I have a form in one workbook where users will be entering data and hitting a command to save. Upon hitting the command button, I'd like the data to be placed into a sheet in a closed workbook. Is this possible? Can you provide an example if it is?
Thanks in advance,
Jeremy
Thanks in advance,
Jeremy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Brad is sort of correct. Strickly speaking the workbook is still opened by DAO but you don't have to worry about opening the workbook yourself using a Workbooks.Open command. Personally I prefer to just open the workbook myself and push the values into cells - it's easier. ADO and DAO (now obsolete technology) are a bit trickier to use than referencing cells using the more familiar Excel object model. If you freeze the UI by setting Application.ScreenUpdating
Kevin
Kevin
Hi Kevin,
> DAO (now obsolete technology)
Actually, DAO's demise is greatly exagerrated. In the Access world, there are still a lot of things that are easy to
do with DAO, but tricky or impossible to do with ADO. These things are all Access / Jet related, but even so DAO
is still useful to learn for Access developers.
:)
Regards,
Patrick
> DAO (now obsolete technology)
Actually, DAO's demise is greatly exagerrated. In the Access world, there are still a lot of things that are easy to
do with DAO, but tricky or impossible to do with ADO. These things are all Access / Jet related, but even so DAO
is still useful to learn for Access developers.
:)
Regards,
Patrick
It is definately still there but, from what I understand, Miscrosoft no longer supports it.
Kevin
Kevin
Kevin,
> It is definately still there but, from what I understand, Miscrosoft no longer supports it.
There will not be any new development for DAO, but it will exist as long as Access and Jet exist (and as much as
Microsoft would like to kill off both, neither are going anywhere).
MS had actually turned DAO off by default in at least some releases of Access 2000, only to relent after getting
deluged with complaints. DAO and ADO are both on by default in Access 2002 (and presumably 2003 and 2007,
but I have neither of those versions).
Regards,
Patrick
> It is definately still there but, from what I understand, Miscrosoft no longer supports it.
There will not be any new development for DAO, but it will exist as long as Access and Jet exist (and as much as
Microsoft would like to kill off both, neither are going anywhere).
MS had actually turned DAO off by default in at least some releases of Access 2000, only to relent after getting
deluged with complaints. DAO and ADO are both on by default in Access 2002 (and presumably 2003 and 2007,
but I have neither of those versions).
Regards,
Patrick
ASKER
matthewspatrick--
I am working with MS Office 2003, and the DAO Libraries are there. Looks like DAO is still supported.
Byundt--
That PAQ link you provided looks like it may work...
Everyone--
If you follow the link provided by Byundt and read through the questions, there is a mention of appending data to the end of the existing data. Can anyone suggest a way to verify that a particular field doesn't already exist, and if it does to overwrite that existing field. EX:
-- I have an existing record w/ ref# HD00000000321
-- If someone makes a change to that record, I don't want it to be added as a new record, instead I want the previously entered data overwritten
I am working with MS Office 2003, and the DAO Libraries are there. Looks like DAO is still supported.
Byundt--
That PAQ link you provided looks like it may work...
Everyone--
If you follow the link provided by Byundt and read through the questions, there is a mention of appending data to the end of the existing data. Can anyone suggest a way to verify that a particular field doesn't already exist, and if it does to overwrite that existing field. EX:
-- I have an existing record w/ ref# HD00000000321
-- If someone makes a change to that record, I don't want it to be added as a new record, instead I want the previously entered data overwritten
>I am working with MS Office 2003, and the DAO Libraries are there. Looks like DAO is still supported.
The DAO libraries are definately still installed by various applications and development environments but the technology is NOT supported by Microsoft and has not been for years. Dispite the fact that some things are easier in DAO I strongly recommend not touching it if you are starting out with a new project. To build in dependencies to DAO at this time is risky at best. When I first explored integrating VB/VBA with databases about five years ago I saw the indication then that DAO was on it's way out and have never touched it. My current client, Gap, has a corporate-wide policy that DAO is not to be used on any new projects.
>Can anyone suggest a way to verify that a particular field doesn't already exist, and if it does to overwrite that existing field.
You are now pushing the envelope trying to keep the workbook "closed". Remember that any service that updates or appends a closed workbook has to open the workbook anyway. In a sense it's a fool's game with no performance gain and only headaches as you attempt to do that which is easily done using the Excel object model. The only time I have ever not opened a workbook is when reading it and even that has questionable benefit when reading more than one cell.
I suggest opening the workbook in your code with Application.ScreenUpdating
On a different note you really should consider moving the data into an Access database. Then all the things you are wanting to do are natural Access operations. In fact, any attempt to use ADO or DAO is actually only a hair away from putting an Access database on the other end versus a workbook.
Kevin
The DAO libraries are definately still installed by various applications and development environments but the technology is NOT supported by Microsoft and has not been for years. Dispite the fact that some things are easier in DAO I strongly recommend not touching it if you are starting out with a new project. To build in dependencies to DAO at this time is risky at best. When I first explored integrating VB/VBA with databases about five years ago I saw the indication then that DAO was on it's way out and have never touched it. My current client, Gap, has a corporate-wide policy that DAO is not to be used on any new projects.
>Can anyone suggest a way to verify that a particular field doesn't already exist, and if it does to overwrite that existing field.
You are now pushing the envelope trying to keep the workbook "closed". Remember that any service that updates or appends a closed workbook has to open the workbook anyway. In a sense it's a fool's game with no performance gain and only headaches as you attempt to do that which is easily done using the Excel object model. The only time I have ever not opened a workbook is when reading it and even that has questionable benefit when reading more than one cell.
I suggest opening the workbook in your code with Application.ScreenUpdating
On a different note you really should consider moving the data into an Access database. Then all the things you are wanting to do are natural Access operations. In fact, any attempt to use ADO or DAO is actually only a hair away from putting an Access database on the other end versus a workbook.
Kevin
Kevin,
> On a different note you really should consider moving the data into an Access database.
I'm going to have to bookmark this Q for later :)
Patrick
> On a different note you really should consider moving the data into an Access database.
I'm going to have to bookmark this Q for later :)
Patrick
Kevin