[AccessD] Export to Excel

Hi Kath

> Quick question as I am also struggling with Access to Excel this week. In my case, I need to export a client's investment report (formatted) to Excel. The client may or not have each type of
> investment, eg. shares, or property.

> I am using some tips you have posted to export data (using linked xls files) to generate one linked xls per investment type (ie. one of shares, 1 for property). 

> In the master xls for the client I need to read from each of the separate xls files to put the client's investments together. I have defined a range in each investment type xls, in every case
> called 'ImportRange'

> In the master xls I am trying to read in that range if it's not zero or blank, with this syntax in the cell.

> =IF([RptPortfolioValuation_AustralianShares.xls]Sheet1!$A$2=0,0,RptPortfolioValuation_AustralianShares.xls!ImportRange)

> BUT when the condition is met, it is only putting in the first cell ref from the range, not the entire block. How do I get this cell to read the entire range in / expand?

> Or is there a better way in Excel to do this?? Where the client has the investment type, I also need headings, and subtotals so it's a bit tricky.

Looks like you copy one cell only.

I think you need to set your source and target ranges. Then:

    rngSource.Copy
    rngTarget.PasteSpecial [add parameters ...]
    ' Empty copy-paste buffer.
    Application.CutCopyMode = False

/gustav