Exporting data from Access (one record at a time) to an excel spreadsheet and being able to add the data to the next available line

Hello All

I have an Access database where the users put in orders.  After they put in an order they want the data to be saved to an Excel spreadsheet.  I know how to do that but where I am getting stuck is how to tell Access in VBA to append this new line of Access data to the first available excel row.

The users put orders in one at a time and could take many minutes between each order to enter the next into the Access database.  Plus other users may put in orders.

They do not want to build this at the end of the day in one shot, they want each order, as added to go right into the excel spreadsheet.

I could probably make a table in Access with one field that remembers what line in excel was just used but I would think there has to be a more elegant way?

Thanks!
alevin16Asked:
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.

Rey Obrero (Capricorn1)Commented:
you have to find the next line after the last record, here is how to do this

dim xlObj as object, lastRow as integer, rs as dao.recordset
set rs=currentdb.openrecordset(<query to get the last record from table>)

set xlobj= createobject("excel.application")
     xlobj.workbooks open <complete path to excel file>
     with xlObj
           .worksheets("nameofSheet").select
           lastrow=.activesheet.usedrange.rows.count

          .range("A" & lastrow +1).copyfromrecordset rs

    end with
0

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
PatHartmanCommented:
A spreadsheet is not a database.  Access will NOT share it with others.  How do you expect to manage this?  At any point in time, someone may have the workbook open or some other user may be adding an order that you want to append to this workbook -- not gonna' happen.

What is the business case for this process?  Why are the users not using Access if they are monitoring orders in real time?  There are much better solutions than Excel.

I see that Rey has jumped in with some code to find the last row.  That is all well and good but first you need to get a lock on the file.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd agree with Pat - you'll quickly run into the locked workboot issue, and there's no getting around that.

Use Excel, or use Access. Don't use both. If the goal of the Excel sheet is to be something of a "report", then create one in Access instead.
0
alevin16Author Commented:
I am with all of you.  I wish they would not use Excel.  I am going to restrict them so that only one person will write to it.  No one else will open it.  In fact I will make it so that no one opens it except the program during this writing phase.

Thanks to all again!
0
PatHartmanCommented:
You still never told us what the business purpose of this functionality is.  

Make sure you add code to the Access end of things to handle finding the spreadsheet locked.  Then you'll need code to try again.  

I'm sure there is a better solution if only we knew what problem we were actually trying to solve.
0
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 Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.