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
neelyjerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
What you asking is not possible. You have to open the workbook, push the data, and then save it.

Kevin
byundtMechanical EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Patrick MatthewsCommented:
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
zorvek (Kevin Jones)ConsultantCommented:
It is definately still there but, from what I understand, Miscrosoft no longer supports it.

Kevin
Patrick MatthewsCommented:
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
neelyjerAuthor 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
zorvek (Kevin Jones)ConsultantCommented:
>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
Patrick MatthewsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.