Embedding Excel data entry templates in Database and then saving them

[Kronix]

I want to use Excel documents for data entry for each of my projects in Access. After the data has been transferred from Excel to Access, I want the Excel doc with the entered data to be saved in a folder for that project with other relevant documents. The Excel data entry doc is a template that I wish to store within the Access file instead of as a separate file that must be copied with the Access file. So, a few questions:

1. So far, the only way I've found to store an Excel document (in this case the blank template) within the Access file, is to use an Unbound Object Frame, select the template file I've saved from outside, and make sure the "Linked" checkbox is unchecked so it is instead embedded. It appears a copy is made in Access which is separate from the original Excel file. I must say I have found little documentation on this topic on the internet. It is usually assumed that the file is linked instead of embedded. My question in this case is, is this the only way to embed an Excel doc within the Access file? And does this mean I must find a place on a form for the Unbound Object Frame? What if I want to be able to access the embedded file from various forms and the form that has the Object Frame is closed? Do I need to place it on every Form that will be open?

2. After the user has entered data into the embedded Excel doc, what is the fastest and most efficient way to save a copy into the external folder? By fast and efficient I mean in terms of processing speed and error avoidance. Of course the embedded template should remain blank and unchanged after the save.

3. What is the best way to transfer the data from the Excel file into the Access database? I have read that Excel files cannot be linked to Access databases, but I have seen Excel VBA code that accesses an Access database. I must decide whether Excel VBA code or Access VBA code does the transfer, so are there advantages/disadvantages to each? In the first case, I would have a button on the Excel template doc that can be clicked after the data is entered, in the second case Access would pull the data from the completed Excel doc after I tell it in which folder it is saved. Is it possible to have Excel VBA automatically give notice to Access where it is saved when it is saved, and then have Access VBA do the actual data import using that file location? Unfortunately I will probably have to settle for a situation where the data in Access is stored separately from the Excel document and not linked, since I don't want my database falling apart if the Excel documents get moved or deleted. Any advice on how to manage this is welcome.

 

[isladogs]

I want to use Excel documents for data entry for each of my projects in Access. After the data has been transferred from Excel to Access, I want the Excel doc with the entered data to be saved in a folder for that project with other relevant documents. The Excel data entry doc is a template that I wish to store within the Access file instead of as a separate file that must be copied with the Access file. So, a few questions:

I have no idea as to the answers to your questions but I really can't see the point of doing what you suggest even if its possible.

I suspect the fact that you say there is little or no info available is because normally people use template files differently

For example, I have a number of Excel (and Access) template files that are distributed with several of my databases. These aren't linked files. Their purpose is to provide end users with a simple method of doing some work offsite by exporting specified data to the template & reimporting when done.

For example, these are used by some teaching staff to do student interim or end of year reports at home. Less important than in the past as many staff now use terminal server but the files exist for those who want them

The method works. Why reinvent the wheel?

 

[Kronix]

Well where do you put the VBA code for the import and export, in the Access or template file?

And what do you mean by terminal server?

 

[isladogs]

The export/import code is in the main Access database

Terminal services are available from Microsoft as an effective way of connecting securely to a network server from a remote location. Its been around for many years & works well but it isn't cheap.

EDIT:Here's are a couple of links describing TS:
https://www.parallels.com/blogs/ras/terminal-server-2016/
https://docs.microsoft.com/en-us/windows-server/remote/remote-desktop-services/welcome-to-rds

 

[Kronix]

So your main Access database has a file dialog where you can select the external Excel/Access file to import?

Also I think my situation is different from yours because I want to use the Excel files because Excel has faster calculation capabilities. It involves changing the costs of various items and getting an immediate feedback on how it affects the sum. Any form interface I could come up with would require too many clicks to make fast small adjustments. I was close to making a datasheet form that simulated an Excel page by allowing entry of multiple services and costs on a single datasheet and then parsed the totals albeit in separate text boxes (as far as I know it is not possible to display totals on a datasheet/table in real time as values are entered into same table, if it is please let me know), but after considering that there could be variable numbers of items, differently named items, variable numbers of groups of items, and variable numbers of sub sums for each offer, I realized nothing could replace the flexibility and convenience of an Excel page if I could get it to work with Access. That means we will be using and saving the Excel files for every project, regardless of where the work is supposed to be done. And the fact that I already need a folder on the server for the schematic files means I already have a place to store the Excel files.

 

[isladogs]

1. You can view the results of any calculation in real time using the Eval function.
This can manage complex equations including multiple variables.

2. Or you can 'embed' an Excel document (or any other document) in Access using a web browser control
There is an example showing this at https://www.access-programmers.co.uk/forums/showthread.php?t=297837

 

[1268]

I must be missing something? Can you not link the template to access under a generic name, query the spreadsheet then use docmd.transferdatabase to export the file where you want?

Sent from my SM-G950U using Tapatalk

 

[isladogs]

I must be missing something? Can you not link the template to access under a generic name, query the spreadsheet then use docmd.transferdatabase to export the file where you want?

Sent from my SM-G950U using Tapatalk

That was basically my original response except that you don't need to link the template to export to a named file based on the template. Similarly I don't bother linking the updated spreadsheet before reimporting as the name will not be consistent

Did you mean DoCmd.TransferSpreadsheet?

 

[Kronix]

ridders, but how would I use the Eval function on individual lines in an access datasheet? Eval is not for runtime, is it? Say, the first 8 rows are for user input, then the 9th row is the total of the first 8 rows, and then a 10th row with a rate for additional costs and an 11th row for that cost in currency, and then a 12th row that is a sum of the 9th and 11th row. Then a 13th row for tax rate, and a 14th row for the tax calculated, then a 15th row for the total. That's easily done in Excel, but I don't know how to make Access act like this, namely making each row do different things.

1268 I am already doing that with Word documents. But the original question is that I want the Excel template (as well as the Word ones) to be part of of the Access file and the Access file should "spawn" filled out Excel docs to the appropriate folder. I don't want dozens of Excel and Word templates that I have to copy to every computer with every update (if I update the templates), and also the possibility that someone will change the templates if they are separate files (after they crack the safeguards, and then I'd have to add VBA code for that).

 

[isladogs]

ridders, but how would I use the eval function on individual lines in an access datasheet? Say, the first 8 rows are for user input, then the 9th row is the total of the first 8 rows, and then 2 more rows and then a 12th row that is the result of a division operation of those 2 rows and a sum with the 9th row with the total. Then a 13th row for tax rate, and a 14th row for the tax calculated, then a 15th row for the total. That's easily done in Excel, but I don't know how to make Access act like this, namely making each row do different things.

You can't as that's not what Access is designed to do.
The purpose of Access tables and datasheets is to store data.
Don't try and force it to do something it isn't designed for.

You could do this in an Access report using grouping
Otherwise embed an Excel file using a web browser control or just work in a standalone Excel worksheet

1268 I am already doing that with Word documents. But the original question is that I want the Excel template (as well as the Word ones) to be part of of the Access file and the Access file should "spawn" filled out Excel docs to the appropriate folder. I don't want dozens of Excel and Word templates that I have to copy to every computer with every update (if I update the templates), and also the possibility that someone will change the templates if they are separate files (after they crack the safeguards, and then I'd have to add VBA code for that).

So instead of a simple and well tried solution you want to do something in a way that frankly makes little sense. It seems this is just to save managing distribution of a few template files which could easily be protected using a password or better still saved in a restricted access folder.

I think its a very odd idea and one for which I am unable to assist with its implementation. Good luck with your project

 

[1268]

That was basically my original response except that you don't need to link the template to export to a named file based on the template. Similarly I don't bother linking the updated spreadsheet before reimporting as the name will not be consistent

Did you mean DoCmd.TransferSpreadsheet?

Joy.

No I meant docmd.transferdatabase.

Sent from my SM-G950U using Tapatalk

 

[JHB]

.. Say, the first 8 rows are for user input, then the 9th row is the total of the first 8 rows, and then a 10th row with a rate for additional costs and an 11th row for that cost in currency, and then a 12th row that is a sum of the 9th and 11th row. Then a 13th row for tax rate, and a 14th row for the tax calculated, then a 15th row for the total. That's easily done in Excel, but I don't know how to make Access act like this, namely making each row do different things.

Is it always so? Maybe you should show some screen dumps from your Excel sheets - perhaps we then could come up with some solutions.
Of cause, I assume that users use the database for input instead of an Excel sheet.

 

[1268]

ridders, but how would I use the Eval function on individual lines in an access datasheet? Eval is not for runtime, is it? Say, the first 8 rows are for user input, then the 9th row is the total of the first 8 rows, and then a 10th row with a rate for additional costs and an 11th row for that cost in currency, and then a 12th row that is a sum of the 9th and 11th row. Then a 13th row for tax rate, and a 14th row for the tax calculated, then a 15th row for the total. That's easily done in Excel, but I don't know how to make Access act like this, namely making each row do different things.

1268 I am already doing that with Word documents. But the original question is that I want the Excel template (as well as the Word ones) to be part of of the Access file and the Access file should "spawn" filled out Excel docs to the appropriate folder. I don't want dozens of Excel and Word templates that I have to copy to every computer with every update (if I update the templates), and also the possibility that someone will change the templates if they are separate files (after they crack the safeguards, and then I'd have to add VBA code for that).

Filled out ones based on what?

Sent from my SM-G950U using Tapatalk

 

[Kronix]

1268 filled out ones based on the blank templates that I want saved in the Access file. The user gets a template to fill out when they set up a new offer in Access. The user uses Excel because its the quickest way to change numbers and see totals on the fly. The Excel docs will need to be standardized a bit to facilitate import of the data into Access after the data is finalized and saved, while still allowing the flexibility that Excel provides.

Here's a screenshot of a possible Excel calculation created on the fly without Access. For the template I want to keep the flexibility of a variable number of rows and columns, while putting in markers that allow Access to detect where the data is.