Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Export to the next available row in excel??

Options
  • 04-03-2010 8:31pm
    #1
    Sixaside
    Closed Accounts Posts: 25 ✭


    Hi all, have a quick question re exporting data from access to excel. I have an access query which will return one row of data. What I need to do is export this query to a specific excel worksheet. I then need to update my database and export the new query results to the next available row in the same spreadsheet. Anyone know a VBA function which will export to the next available row. i.e next row which isn't already populated?


Comments

  • Options
    #2
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭mark renton


    why 1 line at a time?


  • Options
    #3
    dragon_lordMTB
    Registered Users Posts: 128 ✭✭dragon_lordMTB


    LastRow = Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row

    This will return the last occupied row in your sheet, increment by 1 for next row.


  • Options
    #4
    Sixaside
    Closed Accounts Posts: 25 ✭Sixaside


    john47832 wrote: »
    why 1 line at a time?

    Well, I have to enter different data on a form each time. This changes the query results and I need to see all the different outcomes on a single excel sheet. In the future I will look at maybe automating the whole thing. Even then part of the automated process will involve exporting to the next available row in the excel sheet each time the new data has been inputted.

    I'm not sure if that makes sense and maybe as I work on this thing I will be able to get the desired results in an access table and then just export all the data together when the table is complete.


  • Options
    #5
    Sixaside
    Closed Accounts Posts: 25 ✭Sixaside


    dragon_lordMTB wrote: »
    LastRow = Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row

    This will return the last occupied row in your sheet, increment by 1 for next row.

    The VBA function will be part of my access database and not in excel. I'm not sure how I can use that line of code in the function. Maybe I'm missing something. I am not a VBA expert but can usually tweak functins found online to do what I need. Problem is after some serious googling I can't find any forum where someone has asked this particular question!!


  • Options
    #6
    Sixaside
    Closed Accounts Posts: 25 ✭Sixaside


    Think I'm going to park this. May as well use an append query to build a table of the data in access and then export the finished table to excel afterwards. Should have gone this way in the first place. Having said that, if anyone out there has a complete function which would carry out the task as I was looking to do originally please post it!

    Thanks for the earlier tip dragon_lordMTB.

    Sixaside


  • Advertisement
  • Options
    #7
    Seth Brundle
    Moderators, Politics Moderators Posts: 39,852 Mod ✭✭✭✭Seth Brundle


    could you not just use a DAO recordset to populate the next row?
    http://support.microsoft.com/kb/146406


  • Options
    #8
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    There is a oledb driver for excel, connecting to it with ADO shouldn't be a problem. IIRC your sheet needs a named range and you insert into that.


Advertisement