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

[Ineedhelpplz]

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_dat e'
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

[ADezii]

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.
   

   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 OpenCurrentData base() Method of the Access Application Object to Open Production.accd b in the C:\Test Folder, namely: (C:\Test\Produc tion.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 CloseCurrentDat abase() 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).
   

   Dim strSQL As String
   
   Set appAccess = CreateObject("Access.Application")
   
   appAccess.Visible = False
   
   strSQL = "INSERT INTO [Finished Batches] ([Production Date],[Lot_Number],[Ingredient1],[Amount1]) " & _
            "VALUES (#" & Range("C5") & "#,'" & Range("D5") & "','" & Range("F23") & _
            "'," & Range("G23") & ")"
            
   With appAccess
     .OpenCurrentDatabase ("C:\Test\Production.accdb")
     .DoCmd.RunSQL strSQL, dbFailOnError
     .CloseCurrentDatabase
   End With
   
   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.

[Ineedhelpplz]

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

[zmbd]

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.

[Ineedhelpplz]

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

[zmbd]

What do you mean that there's a lack of consistency in 8 and 9?

(7) strSQL = "INSERT INTO [Finished Batches] ([Production Date],[Lot_Number],[Ingredient1],[Amount1]) " & _
(8)         "VALUES (#" & Range("C5") & "#,'" & Range("D5") & "','" & Range("F23") & _
(9)         "'," & Range("G23") & ")"

(where (7)...(9) refer to the original posting code block )

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?

[Ineedhelpplz]

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?

[zmbd]

there was supposed to be... fixed it in the posting.