Place Data to a Closed Workbook from a VBA form

neelyjer
neelyjer used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
What you asking is not possible. You have to open the workbook, push the data, and then save it.

Kevin
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Hi Jeremy,
What you request is definitely possible. Here is a PAQ with the code to do it using ADO: http:/Q_20815824.html#9874250

Brad
Top Expert 2008

Commented:
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 = False the user will not know the difference.

Kevin
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Top Expert 2010

Commented:
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
Top Expert 2008

Commented:
It is definately still there but, from what I understand, Miscrosoft no longer supports it.

Kevin
Top Expert 2010

Commented:
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

Author

Commented:
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
Top Expert 2008

Commented:
>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 turned off and doing the work directly to the workbook as you would any other open workbook. You will acheive success much more quickly and the user will see no difference whatsoever. Your code will be more readable and familiar and thus more maintainable.

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
Top Expert 2010

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial