?
Solved

Efficient was to copy a formula from within MS Access with a Excel template open

Posted on 2014-12-13
6
Medium Priority
?
236 Views
Last Modified: 2014-12-14
I have the attached template where from within MS Access I will be pasting data from a recordset into the template columns A and B.  What is the most efficient way to copy down the formula in col  d ?  My challenge is I do not know from day to day how many rows I will have to paste from MS Access to Excel.. In addition there is no guarantee I will have information in col c

From within MS Access
.Range("A2").CopyFromRecordset rs   - This copys my data from MS Access into Col A-C

Note the Experts Exchange Attachment facility will not allow me to attach as template so I have attached it below as an Excel spreadsheet.
Test-Template.xlsx
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40498132
did you look at the sample db here
0
 

Author Comment

by:upobDaPlaya
ID: 40498144
The formula already exists in my template.  Do you think it will be more efficient to copy down the formula that will always exist in the first row.  I can do a record count of my recordset from MS Access and then as an example copy down the formula from d1:d25 ?

The sample db you provided loops thru every single cell and perhaps that is more efficient, but that is what I am trying to determine...

Thus, in my example above I will dump the data from MS Access into Excel Col A-C.  Then I left with col D where I need to paste down the formula...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40498158
if the formula already exist in the template (.xltx)
it will still be available to the newly opened excel file.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:upobDaPlaya
ID: 40498199
Rey..but it looks like in your db example rather than use the existing formula found  in the first row of my attached tempate you are recreating the formula ?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40498201
if you are very sure that the number of columns will not change,
* no need to recreate the formula
* copyfromrecordset is enough

the same goes if the number of rows will not change

... you are recreating the formula
the codes I used will handle variations of columns and rows.

got it?
0
 

Author Closing Comment

by:upobDaPlaya
ID: 40499597
Thanks Rey I used a mix of your suggestions along with some suggestion I found while researching...
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question