473,320 Members | 2,073 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,320 software developers and data experts.

How do I export a single record from MS-Access to specific cells in Excel

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 transfer the record to Excel, I rename it and be done. The data fields will need to go to specific cells in the spreadsheet. Each record represents a seasoning formula with fields as such:
Formula_name
Formula_number
Date_entered
Ingredient1
Amount1
Ingredient2
Amount2
Ingredient3
And so on

The amounts are a percent of total amount. Ingredients and amounts need to be in columns. I will send the newly named excel sheet (Batch Sheet) to a pc in production for pulling, weighing and lot code recording. I have looked at vba but don’t have time to learn it in time for this project so I am hoping to just cut and paste the code or better-yet email the database and excel template to someone much smarter than me and have it working when returned. $$ Also can it work with different versions of MS Office. Thanks.
Dec 28 '13 #1

✓ answered by ADezii

I have created the simplest of all scenarios for you to be used as a Starting Point, but first a couple of assumptions:
  1. An Excel File named Formulas.xls exists and is in the same Folder as your Database.
  2. A Worksheet names Batch Sheet exists within Formulas.xls. The order of this Sheet makes no difference.
The following Code, executed within the Click() Event of a Command Button, will:
  1. Create an Instance of Excel and Open Formula.xls making it visible.
  2. Make Batch Sheet the Active Worksheet.
  3. Copy the Values from the eight Fields on the Form that you mentioned to specific Cells, namely Row 1/Columns A-H.
  4. Autofit Columns A thru H so that these Values fill the Cell Widths.
  5. Excel will remain Open and Visible so you can see the outcome of the Code execution.
  6. As stated, this is the simplest scenario which can easily be expanded.
  7. Any questions, feel free to ask.
    Expand|Select|Wrap|Line Numbers
    1. 'First you must set a Reference to the Microsoft Excel XX.X Object Library
    2. Dim appExcel As Excel.Application
    3. Dim wrkBook As Excel.Workbook
    4.  
    5. Set appExcel = New Excel.Application
    6. Set wrkBook = appExcel.Workbooks.Open(CurrentProject.Path & "\Formula.xls")
    7.  
    8. appExcel.Visible = True     'Make Excel Window Visible
    9.  
    10. 'Make Batch Sheet the active Worksheet
    11. wrkBook.Worksheets("Batch Sheet").Activate
    12.  
    13. With wrkBook.ActiveSheet
    14.   .Range("A1").Value = Me![Formula_name]
    15.   .Range("B1").Value = Me![Formula_number]
    16.   .Range("C1").Value = Me![Date_entered]
    17.   .Range("D1").Value = Me![Ingredient1]
    18.   .Range("E1").Value = Me![Amount1]
    19.   .Range("F1").Value = Me![Ingredient2]
    20.   .Range("G1").Value = Me![Amount2]
    21.   .Range("H1").Value = Me![Ingredient3]
    22.     .Columns("A:H").AutoFit         'Autofit Columns A thru H to hold contents
    23. End With

1 5154
ADezii
8,834 Expert 8TB
I have created the simplest of all scenarios for you to be used as a Starting Point, but first a couple of assumptions:
  1. An Excel File named Formulas.xls exists and is in the same Folder as your Database.
  2. A Worksheet names Batch Sheet exists within Formulas.xls. The order of this Sheet makes no difference.
The following Code, executed within the Click() Event of a Command Button, will:
  1. Create an Instance of Excel and Open Formula.xls making it visible.
  2. Make Batch Sheet the Active Worksheet.
  3. Copy the Values from the eight Fields on the Form that you mentioned to specific Cells, namely Row 1/Columns A-H.
  4. Autofit Columns A thru H so that these Values fill the Cell Widths.
  5. Excel will remain Open and Visible so you can see the outcome of the Code execution.
  6. As stated, this is the simplest scenario which can easily be expanded.
  7. Any questions, feel free to ask.
    Expand|Select|Wrap|Line Numbers
    1. 'First you must set a Reference to the Microsoft Excel XX.X Object Library
    2. Dim appExcel As Excel.Application
    3. Dim wrkBook As Excel.Workbook
    4.  
    5. Set appExcel = New Excel.Application
    6. Set wrkBook = appExcel.Workbooks.Open(CurrentProject.Path & "\Formula.xls")
    7.  
    8. appExcel.Visible = True     'Make Excel Window Visible
    9.  
    10. 'Make Batch Sheet the active Worksheet
    11. wrkBook.Worksheets("Batch Sheet").Activate
    12.  
    13. With wrkBook.ActiveSheet
    14.   .Range("A1").Value = Me![Formula_name]
    15.   .Range("B1").Value = Me![Formula_number]
    16.   .Range("C1").Value = Me![Date_entered]
    17.   .Range("D1").Value = Me![Ingredient1]
    18.   .Range("E1").Value = Me![Amount1]
    19.   .Range("F1").Value = Me![Ingredient2]
    20.   .Range("G1").Value = Me![Amount2]
    21.   .Range("H1").Value = Me![Ingredient3]
    22.     .Columns("A:H").AutoFit         'Autofit Columns A thru H to hold contents
    23. End With
Dec 28 '13 #2

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

Similar topics

0
by: Larry | last post by:
I am trying to export a record set (or everthing from an array) from an old asp page at client computer. Can any one tell me how to do it? Thank you very very much. Larry
3
by: Gaffar | last post by:
Hello, how to export ms-access tables information to excel sheet using ASP. please give sample code. it is very urgent to me or if u have no idea please give any news group to solve my...
5
by: Maria L. | last post by:
Hi, I need to export the content of a DataGrid (in Windows application in C#), into an Excel spreadsheet. Anyone knows how to do this? Any code snippets would help! thanks a lot, Maria
2
by: aran | last post by:
I am trying to export each record in an Access database into its own separate Excel file. Is there an easy way to do this? Please dumb it down for me as much as possible. Thank you. Also is...
1
by: davidwelli | last post by:
Hello, I have a Access 200 format database that contains contact details and a picture for each record. The contact details are held in one table and the images are held in another as OLE...
31
by: ashitaka65 | last post by:
Hello, i'm writting a databse using access and would like to export certain records to an excel file. i have coded a form that allows me to find a record by using the item number called "C#" then...
2
by: msksmack | last post by:
Hi, When I enter a new record into my main table, I'd like figure out a way to copy the data into the table and also append this new record into an existing Excel spreadsheet. Here's the trick!...
0
by: hiranmaie | last post by:
Hi, I have a gridview with more than 10 rows of data. I want to export these gridview values into an excel sheet along with the header names of excel sheet. I am not using a database here. I...
4
by: lena1342 | last post by:
Hi Everyone! I tried to insert a picture in specific cells and I have coded a small VBA for Excel 2007. Unfortunately the macro only opens and inserts a picture in the active sheet but it should...
1
by: nannu88 | last post by:
I wanted to export data from jsp page to excel. i have created excel sheet for small ammount of data but when the data size increases it will create problem to export data from jsp.
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...
1
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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....

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.