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

 

[DButler]

This form works fine, thanks. I have locked the controls on the form so that only the data needed to be input manually can be manipulated. Now, I need the form from the linked table to record the record the permanent table as a new record. Possibly with a "Record Data" button on the form. This is great, thank you!!

DB

 

[xenou]

This will be a long bit of code for you to write but I don't see a simpler solution. You can write an insert statement that runs from your command button. I think if you're getting data from Excel and updating a table that's not bound to the form you have to take extra precaution to validate the data. So I'd validate everything, and use a raw sql statement, which is another way to validate the data in and of itself since the data will need to be in good form in order to execute.

[COLOR=Navy]Private[/COLOR] [COLOR=Navy]Sub[/COLOR] Command6_Click()
[COLOR=Navy]Dim[/COLOR] dtmTransDate [COLOR=Navy]As[/COLOR] [COLOR=Navy]Date[/COLOR]
[COLOR=Navy]Dim[/COLOR] lngTransNum [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] strProductID [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
    
    [COLOR=SeaGreen]'Capture Form Values[/COLOR]
    dtmTransDate = Me![TransDate]
    lngTransNum = Me![TransNum]
    strProductID = Me![ProductID]

    [COLOR=SeaGreen]'//Validate Date[/COLOR]
    msg = ""
    [COLOR=Navy]If[/COLOR] dtmTransDate = 0 [COLOR=Navy]Then[/COLOR]
        msg = msg & "Invalid TransDate" & vbNewLine
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]If[/COLOR] lngTransNum = 0 [COLOR=Navy]Then[/COLOR]
        msg = msg & "Invalid TransNum" & vbNewLine
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]If[/COLOR] strProductID = "" [COLOR=Navy]Then[/COLOR]
        msg = msg & "Invalid ProductID" & vbNewLine
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    
    [COLOR=SeaGreen]'//If Invalid Date report errors[/COLOR]
    [COLOR=Navy]If[/COLOR] msg <> "" [COLOR=Navy]Then[/COLOR]
        msg = "Error: " & vbNewLine & msg
        MsgBox msg
    
    [COLOR=Navy]Else[/COLOR]
        [COLOR=SeaGreen]'//If Valid Data create Insert Statement[/COLOR]
        s = "INSERT INTO Table2 "
        s = s & "( TransDate, TransNum, ProductID ) "
        s = s & "VALUES ( "
        s = s & "#" & dtmTransDate & "#, "
        s = s & lngTransNum & ", "
        s = s & "'" & strProductID  & "' "
        s = s & ");"
        [COLOR=Navy]Debug[/COLOR].[COLOR=Navy]Print[/COLOR] s
        
        [COLOR=SeaGreen]'//Run Insert Command to create a new record in Table2[/COLOR]
        [COLOR=Navy]On[/COLOR] [COLOR=Navy]Error[/COLOR] [COLOR=Navy]Resume[/COLOR] [COLOR=Navy]Next[/COLOR]
        CurrentDb.Execute s, dbFailOnError
        [COLOR=Navy]If[/COLOR] Err [COLOR=Navy]Then[/COLOR]
            MsgBox Err.Description
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]

[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

This will be a lot of work for you with so many controls but that's that. I'm not really very thrilled about your setup with half your data entry in Excel and half in Access, and even half the excel data half from Access. You're spreading yourself all over the place and it's going to make for more work. It's not a good way to get started with Access. My two cents. In any case, good luck!