473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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

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
7 2751
ADezii
8,834 Expert 8TB
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
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
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
there was supposed to be... fixed it in the posting.
Sep 29 '14 #8

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

Similar topics

1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
4
by: Hans [DiaGraphIT] | last post by:
Hi! I want to export a dataset to an excel file. I found following code on the net... ( http://www.codeproject.com/csharp/Export.asp ) Excel.ApplicationClass excel = new ApplicationClass();...
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
8
by: Edgar | last post by:
Tools: SQL Server 2K, Excel 2000 Hi, I have an Excel report worksheet with formatted headings. What I want to do is to export data from the SQL server into a specific cell of the excel file....
1
by: edralyn | last post by:
guys can u please help me...what is the code for exporting an excel to access
2
by: Peter99 | last post by:
We are about to receive 500 excel spreadsheets from a client. The data has not been arranged in tabular form. Is it possible to import specific cells, say J10, M10, J15, M22 into an Access table...
1
by: elbatz | last post by:
Hello.Does anyone here know how to convert or export data from excel to access database having an existing table using visual basic 6 codes? And if that data in excel already exist in access...
11
by: CarrieR | last post by:
Hi, I thought this was a simple issue, but apparently it's not. I need to export the contents of about 30 queries, each into a specific sheet, and cell range, of an existing Excel workbook. ...
0
by: jason1286 | last post by:
I currently have code that exports from Excel to Access to add to the bottom of the table. However, if my data in Excel has a primary key that already exists in the Access table it causes an error...
1
by: Ineedhelpplz | last post by:
I am trying to export the record displayed on a form in Access to an Excel template which I would then rename and use the data in further calculations. I want to click a button on the form to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.