<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.4134.600" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial color=#0000ff size=2>Gustav / John - </FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>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.</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>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). </FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>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'</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>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.</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff
size=2>=IF([RptPortfolioValuation_AustralianShares.xls]Sheet1!$A$2=0,0,RptPortfolioValuation_AustralianShares.xls!ImportRange)</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>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?</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>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.</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>TIA</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>Kath</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=gustav@cactus.dk href="mailto:gustav@cactus.dk">Gustav Brock</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">John W. Colby</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, February 11, 2003 4:15
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] Export to
Excel</DIV>
<DIV><BR></DIV>Hi John<BR><BR>>>An additional trick is to use named
ranges in the Excel sheet. These can be<BR>>>attached as tables to the
Access app which means that you can write directly<BR>>>to these from
your query or code in Access.<BR><BR>> Oooooohhhhhh. Tell us
more!<BR><BR>> I am basically Excel ignorant. Can you publish a
little demo of how to do<BR>> this?<BR><BR>First open your workbook and
assign (create) a named range or two.<BR>Save and close.<BR>Open Access and
choose to attach a table. Choose .xls as file type and<BR>pick your
workbook.<BR>Now the attach wizard shows a dialog. Upper left are radio
buttons to<BR>select to attach either sheets or named ranges. Choose named
ranges,<BR>and your newly created named ranges show up in the listbox. Pick
one.<BR><BR>Now you have an attached table you can write and append to.<BR>But
you cannot delete rows; instead you'll need to run a loop that<BR>erases the
rows.<BR><BR>/gustav<BR><BR>_______________________________________________<BR>AccessD
mailing list<BR><A
href="mailto:AccessD@databaseadvisors.com">AccessD@databaseadvisors.com</A><BR><A
href="http://databaseadvisors.com/mailman/listinfo/accessd">http://databaseadvisors.com/mailman/listinfo/accessd</A><BR>Website:
<A
href="http://www.databaseadvisors.com">http://www.databaseadvisors.com</A><BR></BLOCKQUOTE></BODY></HTML>