Assign table value to excel worksheet

Posted on 2012-09-03
Last Modified: 2012-09-04
I have a Access table( "tblSchedule) with 3 fields named "Route" , "Planned Start"  and "Planned End".
Data in the table like as:
Route          Planned Start                      Planned End
A                  12:00                                     14:00
B                   13:00                                     16:00
C                    16:00                                     18:00

I want to assign Planned Start to an excel worksheet.
I created a hidden form with the record source tblSchedule.
To assign the value of Route A to C to the excel worksheet write the VBA code as below:
wb_Target.Cells(2, 2) = Forms("Form1").Planned_Start(1)
wb_Target.Cells(3, 2) = Forms("Form1").Planned_Start(2)
wb_Target.Cells(4, 2) = Forms("Form1").Planned_Start(3)
I am getting "run time error 451.Property let procedure not defined and property get procedure did not return an object."
Would you please advise how to fix it or if other easy way to do. please provide example code.
Thanks in advance for your prompt response.
Question by:alam747
    LVL 48

    Expert Comment

    You have the information from the table in the form, right? In textboxes?

    It should be:

    wb_Target.Cells(2, 2) = Forms("Form1").ControlName.Value

    or if you are the code to write to Excel in the same form:

    wb_Target.Cells(2, 2) = Me.ControlName.Value

    Author Comment

    Hi jpaulino,

    Thanks for your response.
    The hidden form record source is "tblSchedule" and I added "Planned Start" field to the form.
    If there is three record of table field "Planned Start" as I mentioned above 12:00 , 13:00 and 16:00 and want to assign in three correspoding Cells in excell workshit than what would be VBA code?
    I am not in the same form therefore I tried the way you advised and giving me run time error:
    "Object doesn't support this property or method."
    wb_Target.Cells(2,2) = Forms("Form1").Planned_Start.Value

    Please advise whats the problem or how to fix it.

    LVL 48

    Expert Comment

    Could you upload a sample?
    LVL 119

    Expert Comment

    by:Rey Obrero
    try a simple way of exporting the table to excel
    for access 2003
    docmd.transferspreadsheet acexport,8,"tblSchedule", "C:\folderName\myExcel.xls", true

    for access 2007, 2010
    docmd.transferspreadsheet acexport,10,"tblSchedule", "C:\folderName\myExcel.xls", true

    if you want to do it using vba coding, see this link

    Author Comment

    Hi Capricorn1,
    I tried the way you mentioned, its creating a worksheet named tblschedule.
    Your advise to export the table to excel , if there a way to move/export table data while there is a update of access table "tblSchedule".
    In a data entry form After update event exprot table content to specific cells of a worksheet instead of creat a worksheet of the excel workbook myExcel.  
    For example myExcel.xls have a worksheet "Updated Schedule" and want to export/overwrite all record of table field "Planned Start" and "Planned End" field to the related cell fields.

    VBA code to do that would be highly appreciated.

    LVL 119

    Accepted Solution


    did you look at the link i posted above?

    Author Closing Comment

    Thanks for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now