Exporting Excel cells to Access using VBA

[Ineedhelpplz]

The code below works but I don’t understand the Values (line line 9). I keep getting error messages. Could someone add more fields to it, such as:
F24 into Ingredient2
F25 into Ingredient3
F26 into Ingredient4
G24 into Lot2
G25 into Lot3
G26 into Lot4
The Ingredient column and the Lot column will both have 30 rows for data.

Sub Button10_Click()
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:\users\jay neuh\desktop\database\ss database.mdb")
  .DoCmd.RunSQL strSQL, dbFailOnError
  .CloseCurrentDatabase
End With
 
Set appAccess = Nothing
End Sub

[twinnyfo]

If you already have the Access Project, it might be easier to import into Access from Access. Access allows you to link Spreadsheets and treat them as tables. This greatly speeds up the import process.

[Ineedhelpplz]

I wish I could attach my spreadsheet, I don't think it will work as a table. It contains a bunch of calculations.

[Ineedhelpplz]

Here is the spreadsheet I'm using.

https://mega.co.nz/#!2cZ3kKAZ!BL-HJM-st10EAIKAx7ZZ_r-goflHo1tZewrw-CH-1Dc

[twinnyfo]

I cannot open the link.

Do you need the calculations or only the values represented by the calculations?

[Ineedhelpplz]

I need the values. I hope this link will work, I think it would really help it you saw me excel sheet. Maybe this would be easier if I copied ( =sheet1!a1 ) all the values to sheet2 in one long row with a header row.

https://mega.co.nz/#!2cZ3kKAZ!BL-HJM-st10EAIKAx7ZZ_r-goflHo1tZewrw-CH-1Dc

[twinnyfo]

If you only need the values, then you can still link the Spreadsheet, as only the value is displayed, not the calculation or formula.

[Ineedhelpplz]

Thanks twinnyfo, Im willing to try it. I want the user to be able to use the info on the spreadsheet and enter more data onto it, then export into access. Each time the sheet is used it will have a new name. I would still like to see what Lines 8,9 and 10 would look like if the select and insert were changed to:

F24 into Ingredient2
F25 into Ingredient3
F26 into Ingredient4
G24 into Lot2 (Formated as number
G25 into Lot3 (Formated as number
G26 into Lot4 (Formated as number

[twinnyfo]

If you simply want to expand the code you have above, all your "Insert Into" values are on the same line and all your "Values" are on another line. Gradually expand your code to make sure it works with each addition and you should be able to work it through.

We can help with trouble shooting.

It looks like your spreadsheet is less of a "table" and just holds somewhat random, but standardized information.