Solved

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

Posted on 2014-12-13
6
224 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

856 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