[AccessD] Export to Excel

Gustav / John - 

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.

TIA
Kath
  ----- Original Message ----- 
  From: Gustav Brock 
  To: John W. Colby 
  Sent: Tuesday, February 11, 2003 4:15 AM
  Subject: Re: [AccessD] Export to Excel


  Hi John

  >>An additional trick is to use named ranges in the Excel sheet. These can be
  >>attached as tables to the Access app which means that you can write directly
  >>to these from your query or code in Access.

  > Oooooohhhhhh.  Tell us more!

  > I am basically Excel ignorant.  Can you publish a little demo of how to do
  > this?

  First open your workbook and assign (create) a named range or two.
  Save and close.
  Open Access and choose to attach a table. Choose .xls as file type and
  pick your workbook.
  Now the attach wizard shows a dialog. Upper left are radio buttons to
  select to attach either sheets or named ranges. Choose named ranges,
  and your newly created named ranges show up in the listbox. Pick one.

  Now you have an attached table you can write and append to.
  But you cannot delete rows; instead you'll need to run a loop that
  erases the rows.

  /gustav

  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030210/dd645017/attachment-0002.html>