Is it possible to link an excel table or worksheet into an Access form? (1 Viewer)

[Abeetan]

Hello Everyone,

I am trying to build a purchase order form into Access. I want to be able to integrate an excel table into the form so I perform the calculations in there then paste the table into the form. So when I print it, etc it shows on the form. Please advise.
Thank you

Abe

 

[June7]

Can link to Excel then bind form to that linked object, however, cannot edit Excel worksheet data via that link. What do you mean by 'paste' into form? Best to use report for printing.

 

[Abeetan]

The

Can link to Excel then bind form to that linked object, however, cannot edit Excel worksheet data via that link. What do you mean by 'paste' into form? Best to use report for printing.

The goal is to be able to create a table in excel and in the best possible way add it to access so I can print it. Basically, a form will have fields such as the date, purchase order number, project number, project name, etc all of which are saved into a table. In addition, I would like to add the excel table as a field if possible.

 

[CJ_London]

you can have a linked table to excel and that table can be displayed as as subform on your form. And as a table, it won't have any of the formatting you might be using in Excel. To link, use the transferspreadsheet command - https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet
one of the parameters allows you to specify a range.

Create a subform control and for it's controlsource put 'Table.myLinkedTableName'

Alternatively use a listbox with your linked table name as it's rowsource

If you want anything other than a basic datasheet look, you will need to use form based on your linked table.

As June says, you cannot edit Excel from Access.

Forms are not good objects for printing, better to use a report.

 

[arnelgp]

you can do it All within access without a need of extra workbook.
whatever you have entered (in access tables) can be exported as Excel worksheet.
why do the same thing on two different apps?

 

[Abeetan]

you can do it All within access without a need of extra workbook.
whatever you have entered (in access tables) can be exported as Excel worksheet.
why do the same thing on two different apps?

Can you explain a bit more, please? I am a bit confused about how can I use excel within access?

 

[arnelgp]

what i am saying is you can do it all in access (including the calculation, as you mentioned in post #1).

 

[The_Doc_Man]

You should also be aware that Access and Excel have different file-sharing rules, so it is possible that an app using a LINKED Excel data source would become single-user whereas you seem to want to share this with multiple users. Using a linked Excel table also has the shortcoming that you cannot EASILY update the Excel sheet. (Not impossible, just has limits.) Doing an update of an Excel worksheet IS possible, but usually requires a lot of VBA code if you want any selectivity in what you are updating.

The short answer is "they don't always play well together."

 

[Pat Hartman]

You seem to be trying to use Excel as the data master rather than Access and using Access to format a report rather than Excel. You are using them exactly backwards from how they should be used. Access should be the data master and if Access can't produce the pretty output you want, then use Excel.