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

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

P: 19
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

Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,597
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

Post your reply

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