How can I programmatically import Excel data into an Access table

[Castillb]

The question I had today is whether the following process can be automated and if it is possible what is the best way to go about it for someone such as myself with limited knowledge of coding.

I've attached 2 files, the 1st one is named "Excel Data Cell" ("EDC"), which is the Excel s/s I enter tha data on, the other is "Access Data Entry" ("ADE"), which is a data entry screen in Access. What I would like to do is automate this process. I will use "ADE" and "EDC" abbreviations to distinguish the file names.

The following is exactly what I am trying to achieve:

1) First, I would like for this automation process to be able to pull up each file number seperately in the Access Database. The file numbers are shown in row 1, cell J-N, of the "EDC".

Once the first file is up (516115), I would like the following automated entries done for each file:



2)I would like the field directly next to the "Draft #" on the "ADE" field to be populated with the data entered on "EDC" Cell D7 (highlighted yellow)

3) I would like for the "Date Req:" and "Acctg Draft Date" fields on the "ADE" to both be populated with the data entered on "EDC" cell F3 (Highlighted black)

3) I would like for the "Amount" field to be poulated with the data entered in cell J7 (highlighted green)

4) For this spreadsheet I would like for Access to defulat to "Allocated Expense"

5) I would like for the "Payee" field on the "ADE" to be populated with the data entered in cell A2 (highlighted burgundy)

6) Lastly, I would like for the "Payment Reason" to list all invoices shown in cell D4 of the "EDC".

[alansidman]

Crossposted: http://www.excelforum.com/access-pro...ess-table.html

Please read this on crossposting: http://www.excelguru.ca/content.php?184

[Castillb]

Sorry about that..... I just wasnt sure if what I am trying to acheive should b handled in Excel or in Access.

Crossposted: http://www.excelforum.com/access-pro...ess-table.html

Please read this on crossposting: http://www.excelguru.ca/content.php?184

[June7]

Why are you using Excel for data entry?

The import can be done but because the sheet structure makes a link impractical as well as import with wizard or TransferSpreadsheet method, will require some elaborate VBA code. Review:

http://forums.aspfree.com/microsoft-...el-413629.html
http://forums.aspfree.com/microsoft-...le-413493.html

[Castillb]

Hello June,

To answer you question, the reason why we enter data on the Excel spreadsheet is so that we can email the spreadsheet to an outside party. The outside party uses the payment schedule to see how the payments were allocated amongst the different file numbers and case names.

I use Access on a daily basis, but when it comes to its "behind the scenes" capabilities, I'm totally clueless. I've been doing research pretty much all day on how to automate what I am trying to achieve, but haven't been to successful. I tried both the import/link method and I am able to view most of the spreadsheet in Access, however, one of the errors I received was a "Invalid Column Name" error, which I'm assuming is the file numbers used on the column heading of the Excel sheet. Other errors that appear on the import/link is the percentages are displayed as ##VALUE in Access.

When you say - "the sheet structure makes a link impractical as well as import with wizard or TransferSpreadsheet method"

Do you mean because their are formulas on the Excel sheet?

Why are you using Excel for data entry?

The import can be done but because the sheet structure makes a link impractical as well as import with wizard or TransferSpreadsheet method, will require some elaborate VBA code. Review:

http://forums.aspfree.com/microsoft-...el-413629.html
http://forums.aspfree.com/microsoft-...le-413493.html

[June7]

The two main issues for import are the XYZ, Inc line - this value (or company ID) needs to be on each row; and the multiple file number columns which I suspect are dynamic dependent on the company ID.

The total row would import as another record as will the blank row above it.

Field names can be composed entirely of numbers but the other column names with special characters might be an issue.

Data can be exported from Access into spreadsheet. Can also email directly from Access with the data attached to email as an Excel or PDF or text file.