VB.Net, Access, and Excel
I'm working on a project that will involve putting information into an access database. The project will be written in VB.Net (as of now, anyway). Ultimately, we want to be able to export the info from Access to an Excel spreadsheet so we can format reports to print out. I have a few questions concerning this first, though.
1. Is it possible? (I'm pretty sure it is, but better make sure first).
2. If possible, I would like to export into a premade excel spreadsheet, with formulas and linked fields between a couple of spreadsheets. If it's possible, when I export info into these cells, would I have to re enter any formulas or links, or will I be able to just write over the info already in the cells?
::edit::
Better yet, is there a way to just link a cell in Excel straight to a field in the database?
::/edit::
I'm not fully up to answering your q's but...
Have some thoughts just the same.
On the Access point, as I understand it, you need to consider some things depending upon the tool you are using assuming you are talking about a web-based application?
If you use Web Matrix to develop you will find the overall functionality is restricted in terms of database options unless you use SQL Server or MSDE as your backend dB. You need to use Visual Studio.NET really to make use of other data sources, or be very technically au fait with .NET coding.
In terms of the actual process you are setting out to achieve, it is most likely possible as a former colleague of mine undertook a very similar task a few years back ...which meant using older versions of Excel etc. I'm not sure exactly how he did it, but I know he was able to do so using VB for applications. The data he entered in to the Excel sheet, was not directly manipulated by formulae for the most part, as it generally entailed adding a chunk of data in, or finding the last row from the previous load, then inserting the new data. Where I recall formulae being used, was not the actual data being added-in, but in adjacent columns, any other data manipulation was handled at the dB layer.
Not sure if this is of any help at all!
Re: VB.Net, Access, and Excel
Quote:
Originally posted by monroeski
I'm working on a project that will involve putting information into an access database. The project will be written in VB.Net (as of now, anyway). Ultimately, we want to be able to export the info from Access to an Excel spreadsheet so we can format reports to print out. I have a few questions concerning this first, though.
1. Is it possible? (I'm pretty sure it is, but better make sure first).
2. If possible, I would like to export into a premade excel spreadsheet, with formulas and linked fields between a couple of spreadsheets. If it's possible, when I export info into these cells, would I have to re enter any formulas or links, or will I be able to just write over the info already in the cells?
::edit::
Better yet, is there a way to just link a cell in Excel straight to a field in the database?
::/edit::
You can create pivots and data connections in Excel. Click on Data from the menu and then Import External Data. Clicking on New Database Query will allow you to connect to a database and import data directly. Once the data has been imported you can right click on that area of the spreadsheet and modify the properties that will refresh teh data each time the spread sheet is open.
