By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

How do I export specific cells in Excel to fields in MS Access

P: 19
I would like to click a command button on an Excel worksheet and have it open an existing Access db named production and export some of the data (some cells are calculated)to specific fields in a table (or new record on a form would be better) called Finished Batches.

As Such...
c5 exported to field named 'production_date'
d5 exported to field 'lot_number'
f23 to ingredient1
g23 to amount1

Even better it could stop and ask to save the excel sheet before opening the database.

just stuggling along trying to learn this stuff

Thanks
Feb 23 '14 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,636
This can be accomplished by using Automation Code from within Microsoft Excel to Open an Access Database, the Append a single Record to a Table.
  1. Create a Public Variable in an Excel Code Module to represent the Access Application Object.
    Expand|Select|Wrap|Line Numbers
    1. Public appAccess As Access.Application
  2. Create an Access Application Object.
  3. Set the Visible Property of the Application Object to False.
  4. Define an SQL String that will be used to Append Data in the 4 specifically mentioned Cells to the corresponding Fields in the Finished Batches Table.
  5. Use the OpenCurrentDatabase() Method of the Access Application Object to Open Production.accdb in the C:\Test Folder, namely: (C:\Test\Production.accdb)
  6. Use the runSQL Method of the DoCmd Object to Execute the SQL Statement previously defined, failing on any Error generated.
  7. Use the CloseCurrentDatabase() Method of the Access Application Object to Close the Database.
  8. Release the Resources assigned to the appAccess Object.
  9. The Code below has been tested and amazingly works (LOL).
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    2.  
    3. Set appAccess = CreateObject("Access.Application")
    4.  
    5. appAccess.Visible = False
    6.  
    7. strSQL = "INSERT INTO [Finished Batches] ([Production Date],[Lot_Number],[Ingredient1],[Amount1]) " & _
    8.          "VALUES (#" & Range("C5") & "#,'" & Range("D5") & "','" & Range("F23") & _
    9.          "'," & Range("G23") & ")"
    10.  
    11. With appAccess
    12.   .OpenCurrentDatabase ("C:\Test\Production.accdb")
    13.   .DoCmd.RunSQL strSQL, dbFailOnError
    14.   .CloseCurrentDatabase
    15. End With
    16.  
    17. Set appAccess = Nothing
P.S. - I am quite sure that the Code can be improved upon, but I am in work and cannot devote any more time to it.
Feb 24 '14 #2

P: 19
I'm still puzzled with lines 7,8,9. I was hoping to see a pattern there so I can append more cells, but I can't make sense of it. All the "'",&#'''(",&"' is a little confusing.
Thanks for the help
Feb 26 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
The double double quotes """"" place a single double quote.
Quite often many of us will use a single quote so the we we'll have "''"

The ampersand & is used to stick strings togeither (either variables and/or between the quotes "string1" & "_String2" or "string1_" & "String2" would both equal string1_String2

THe hash mark is a date value indicator #mm/dd/yyyy# Access requires the date in US date/time format so be carfull overseas.
Feb 26 '14 #4

P: 19
Why isnt there more consistency in lines 8 & 9? I'm having a hard time making sence of it.

So what would it look like if I added more cells, like

h24 to Ingredient2
i24 to Amount2
a2 to Title
b3 to Subtitle

Thanks Very Much
Sep 28 '14 #5

zmbd
Expert Mod 5K+
P: 5,397
What do you mean that there's a lack of consistency in 8 and 9?

Expand|Select|Wrap|Line Numbers
  1. (7) strSQL = "INSERT INTO [Finished Batches] ([Production Date],[Lot_Number],[Ingredient1],[Amount1]) " & _
  2. (8)         "VALUES (#" & Range("C5") & "#,'" & Range("D5") & "','" & Range("F23") & _
  3. (9)         "'," & Range("G23") & ")"
  4.  
  5. (where (7)...(9) refer to the original posting code block )
  6.  
The code is quite consistent with what information you've given us to work with.
  • h24 to Ingredient2
  • i24 to Amount2
  • a2 to Title b3 to Subtitle
Both A2 and H24 appear to be a string type-cast; thus will need quotes around the value (" ' ") as explained.
I24 appears to be numeric, if being placed into a numeric field in access do not use quotes

What have you tried so far with your additions? If you will post your work, we can help you troubleshoot it much more easily.

What are you getting back?
Sep 29 '14 #6

P: 19
In your first post above you stated:

"string1" & "_String2" or "string1" & "_String2" would both equal string1_String2

Is there any difference in the first and second example?
Sep 29 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
there was supposed to be... fixed it in the posting.
Sep 29 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.