Access(ing) Excel data (pun intended)

[DButler]

Hello, and thank you!!

I have a data acquisition program that stores plant data in real time, and daily totals in Excel. I have built a workbook that complies this data, and is linked to a report sheet that does the calculations. Some of the data for the reports must be manually entered into the cells, as they are not in the DAS.

I am in the process of moving the stored data to an Access database. In the process, I want to automate the reporting process. I have a Excel sheet that linked to the cells needed in the DAS Excel sheet, and to the manually entered values.
Ex.
GROSSMW AUXMW FUEL STEAM
124, 7, 28, 768
I know the columns did not look right in this ex. but they are correct in the Excel file.

In this example, the GROSSMW and AUXMW are manually entered into a cell, and the FUEL and STEAM are linked to the DAS Excel sheet and transfer the values automatically into these cells.

What I want to do is, have the user go into Access on a form and have the DAS values populate their respective fields, and be able to manually input the other needed data. Then be able to click a command button to save the form data to the access database. I already have the Excel sheets built that will get the requested data from Access and populate the reports. I am just unsure how to make Access and Excel communicate like I want them to.

Thanks so much for your advise!!

DB

 

[xenou]

It seems to me that if you're moving this to Access you won't need Excel any more - the data would be (should be) in Access, as well as your input forms. In short - there would be enough work involved in creating input forms in Access that you may as well treat this as a new design and (possibly) rework it from bottom up.

I'm tempted to say that if you have something that's working with Excel just go with it ... but if you want to get into Access, just take your time and (perhaps) work up a parallel system as you test it. It's generally more time developing in Access, but once completed usually something that's easy to maintain and enhance.

ξ

 

[DButler]

As you suggested, this is pretty much a total redo of the reporting for me. We are still using the "old" one as I build this one. I have the Access DB built, and the report sheet built in Excel for the calculations. I would like the forms to be in Access. I need the ability to manually input about 8 values, and have about 20 values auto populate the form.

Thanks for your input. I have a few questions though...

1, can access perform all the calculations? The reason I want to keep the report side of it in Excel, I have several addins in Excel that perform operations on the data to calculate for example, Enthalpy, Saturation temperatures, etc.

2, One reason I wanted to move the database to Access, is that, well, I really have no database in the excel sheets. Each report is a separate sheet. Thus taking a LOT of room, and making it pretty slow. I have a workbook for each month. It contains a worksheet for each daily report as well as, a monthly report.

So far, I have built the database in Access, and linked it to a single Excel workbook that has a daily, monthly and yearly report built. All I have to do is enter the start and end dates, and the requested data is pulled from Access. This way, I can get a report for any chunk of data, and just have one report sheet do the calculations, and print it. My only hang (so far), is having the intelligence to get the data to transfer in a manor that is easy for the ones doing the nightly reports, I would like for them to be able to enter the 6 or 8 values that have to be entered manually, have the form populate the rest, and click a button to save the record.

May be asking too much, lol.

Thanks so much for your reply, and I will mull over your idea and see if that is an option for my situation.

DB

 

[xenou]

Well, it's really in the details. You can pull data from Access using Excel. It would be possible to use a form to do so. As far as calculations, if you are using Excel addins then it's probably something that would take some time to redesign for Access. Hard to say more without specifics.

ξ

 

[DButler]

Thank you again for your time!!

What details would you like?

On the Excel sheet I have a place for 2 dates. Start date and end date. I query the access DB from Excel based on these dates. Then I use formula in cells to calculate the data from the access DB.
EX. =(VLOOKUP($B$3,Table_Query_from_MS_Access_Database_1,2)-VLOOKUP($B$2,Table_Query_from_MS_Access_Database_1,2))/1000
where $B$3 and $B$2 are the dates entered into 2 date cells. The values from these formula are then linked to the report sheet, where other calculations are performed.
EX. =IF(C7>0,(L17+L18)*2*K24/(C7+C12+C13),0)

What I would like to change, is this. I have a 1 row table in Excel that has approximately 30 values. Of the 30 values, i think 10 of them are manual entries, the rest are linked to the data acquisition system sheets to populate the cells.

In Access, I have built the DB for this 1 row table to be entered, using the date as the Primary Key. I have built a form in Access for each of these entries. What I would like to do is have this form automatically populate the fields from the Excel cells that are auto populated, and be able to manually enter the data into the form that is input manually. This would allow me to eliminate a step or 2 and make the process of getting the data to Access easier. The people entering the data, like myself, are pretty computer challenged and I want to eliminate areas for mistakes.

As you can see, it all works, I just want to streamline it a bit for ease of data entry. Currently, they will have to do too many things to make it all work, and will undoubtedly keep me having to fix it.

I hope this makes it clearer. If you need, tell me how and I can UL the files for you to look at.

Thanks,
DB

 

[xenou]

So you have data in Access, that you put into Excel, that you want to put back in Access? I'm not getting it. It's also impossible to know what your data acquisition sheets are or how they are involved, nor indeed what data you are storing in Access in the first place. I don't think there's general solutions to database design - not without some idea of what the data is (order entry, inventory, financials, recipes, etc.)

 

[DButler]

Let me try to clear it up a bit.

I have an Access DB. I have an Excel sheet that pulls data from the Access DB and does calculations.
Currently, all of the data (approx 30 numbers) must be entered into the Access DB manually. I have created an Access form to accomplish this. Many of the "numbers" that I am entering into the Access DB are already in Excel. I want the Access form to populate these values, on the Access form automatically, from Excel, and enter the remainder of the numbers into the Access form manually. Then, I want the Access form to have a button, or some way to enter the data on the Access form into the Access DB as a record. It will be 1 record for each day.

Then, I can use Excel to extract what data I want, and calculate what I need based on the dates I put in. Whether it be finding the difference between the first and last day, or summing the values between the first and last day, or calculating from cell to cell.

The Access DB is created already, the Excel sheet is created already, and the Excel sheet will pull the data I need from the Access DB as requested.

What I am needing, is how to get the fields on the Access form to automatically populate with the values on another Excel worksheet when I click the "GET DATA" button. Then I can complete the form by manually entering the rest of the data, and save the record into the Access DB.

Clear as Mud?? lol
I hope this is a better explanation.

Again, I can attach files if needed, given a bit of instruction.

Thanks,
DB

 

[xenou]

Well, as I said, the devil is in the details. You can pull data from Excel to Access. Do you want to pull one cell? Two cells? A row of data? A table of data? All of it or some of it? What criteria should be used? What is the address of the Excel file? What sheet is it on? Will the data be changing in size, growing or shrinking? Does it need to be validated to trap errors?

There's just no way to tell you what to do without specific details.

 

[xenou]

Okay, for kicks I have done the following:

Created a workbook in Excel
Added column names to Cells C2 To AK2
Added some data to Cells C3 to AK3
Named the Range C2:AK3 (I called it R_01)
Linked this range in Access as a linked Excel table (so I end up with a table named R_01)
Created a form using the linked table as its data source

Now I can display the data in the form.

I'm sorry I don't find this very pretty and I'm not sure it will solve your issues of wanting something more dummy-proof as you still have weak links (i.e., a linked Excel table in the database that can be vulnerable to bad data or changes in the worksheet structure --> Excel is somewhat loosey goosey this way). But we have shown that the data can be displayed. There is probably a bit more to be done as far as actually designing a data entry form that will work effectively in the actual situation you have.

 

[DButler]

Thank you Sir, I will create this as stated and see where I need to go from there. You did solve one of my issues though, lol. I had created a linked table, and I was trying to get the linked table values into my form, instead of just creating a form from the linked table, It was an ID10T error, lol.

Thanks very much!!
DB