Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-12-13
6
Medium Priority
?
242 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

618 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