Excel data entry form uploaded to Acces

[PMMHart]

I've read several Excel books and one on Access, but I can't seem to find an answer in any of them to this long question.
I work in a college business office. The campus has several required transmittal forms (ordering supplies, travel reimbursement, etc.) that are in Excel. The forms are protected, so I can only write info in a handful of cells. The campus Procurement office then enters that data into the campus ERP. But I run a parallel system in Access. Consequently, I type the info into one of the various Excel forms and then re-type it in Access. (The ERP information available to me is less detailed that what I type into the data entry form so downloading the ERP information isn't satisfactory.) Is there a way to build a macro for each transmittal form that updates the same attribute in an Access table or query. For a specific example, the name of the traveler goes on C5 in the Excel form. I want that data to be entered into the Vendor attribute in the Transactions table in Access. If this process is addressed in some book, please send me the info and save yourself the trouble of writing a long response. I appreciate your help.

 

[JonXL]

I would think importing the Excel data-entry workbook into Access would be the simplest option.

But what you are looking for is possible but would require a lot more to set up and probably be more finicky to use and maintain.

 

[petertenthije]

Where is the information that's entered in Excel stored? Is it on a worksheet in Excel? Or does it go immediately into the ERP system?

What you could do, is put the entries in a Excel worksheet (if it is not there already) and link that worksheet to your Access database.

Ideally the entries would have a unique idenfitication number. But I would guess your Excel file already makes those? If not I recommend to make a combination of date, time (incl seconds) and username. This way you should be pretty much guaranteed of unique entries.


To link an excel file to your database, open Access and go to: External Data > Import Excel Spreadsheet.

This will open a pop-up screen where you can select the Excel file. Then select "Link to the data source by creating a linked table". From there just follow the steps till you're done.

 

[PMMHart]

Thanks.
The transmittal form handles 1 entry at a time. I could aggregate a day's worth of transmittal forms in a workbook and then paste them at the end of the day into the Access table. (The transmittal form is hand delivered across campus where the data for the transaction is re-typed into the ERP.)
As I've thought more about it today, I've focused on building a macro that copies the form's transmittal data to a new worksheet on the form that has the same attributes in the same order as the Access transaction table. Then, the macro's last step will be to copy the Excel row. I'll then open the Access transaction table and append the Excel row. It's less automated than I'd like, but still eliminates time & typos. FWIW, I was hoping there was a way to automate data entry from the transmittal form to the Access table.

 

[petertenthije]

You could run the append query from Excel as well? What you could do:

Link the new Excel transmittal data worksheet you mentioned to Access as explained earlier.
Create an append query in Access that copies the data from the linked Excel table to your Access transaction table.
Modify your Excel macro with below VBA. This will open Access, run the Access append query, and close Access.

Dim strDatabasePath As String
Dim appAccess As Access.Application

strDatabasePath = "C:\folder\folder\database.accdb"
Set appAccess = New Access.Application
With appAccess
    Application.DisplayAlerts = False
    .OpenCurrentDatabase strDatabasePath
    .Run "name_of_append_query"
    .Quit
End With
Set appAccess = Nothing

 

[PMMHart]

Thanks. I will try that!