Solved

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

Posted on 2014-12-13
6
226 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
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.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel printing an invoice header over two sheets 3 30
Set a Range to a Cell in Excel VBA 2 17
Look for an number in a column 42 24
How do a DCount on a report 1 18
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

730 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