• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

Assign table value to excel worksheet

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.
  • 3
  • 2
  • 2
1 Solution
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
alam747Author Commented:
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.

Could you upload a sample?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Rey Obrero (Capricorn1)Commented:
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

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

Rey Obrero (Capricorn1)Commented:

did you look at the link i posted above?
alam747Author Commented:
Thanks for your help.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now