sending data to a named range in excel

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hello, need help please:

Trying to output information from a query to a named range in excel,
the problem is that the process results in every field in the query
being tranferred to each consecutive column in the range. The problem
for me is that I have a couple of columns within the range that I need
to remain as is. I tried naming the range in excel to be (a1:c5, g1:g5)
as an example however access still puts out the data to the d column
rather then skipping it and putting it into g. I tried creating an
empty field within the query but it blanks out what I have in that
column. I could send out the first filed I want to the first part of
the range, then have another query send out the last field to another
range but this seems like a waste to me and should be able to do in one
step. any help would be greatly appreciated . in case my explanation
is hard to follow, this is what i need

access query fields:

time, temp, depth, pressure

to goto a range in excel that has columns:

time, temp, gradient, gradient2, depth, pressure

obvoiusly i need the fields to go in the right columns, however the data
from access goes into the first 4 columns in the range

tia
 
Hi Scott,

As you've discovered, Access can only work with contiguous ranges in
Excel. One thing you could do is of course to re-arrange the columns to
something like
time, temp, depth, pressure, gradient, gradient2

Another is to create the (contiguous) named range on another worksheet,
and use formulas in the columns on the original sheet to pick up the
values from the contiguous range.

Or else you could use Automation to put values into individual cells.
 
Thanks for the info. The columns in excel are in that order for other
applications so changing them around would be counter productive, I have
thought about using another worksheet like you mentioned but was hoping it
wasnt necessary and would like to avoid it if possible so as not to pile up
the spreadsheet with data over and over.

So there is no way to have access recognize a range in excel thats 2 seperate
ranges named as one?

Or to create a so called null column in my output query that would not output
anything to that column yet still take up that space?

I guess I will use the automation method, would it be easier to have 2
different queries output to 2 seperate ranges in excel? Or is it wiser to
output directly from a recordset field into columns? (not sure I know how to
do the later off the top of my head)

thanks again
 
Thanks for the info. The columns in excel are in that order for other
applications so changing them around would be counter productive, I have
thought about using another worksheet like you mentioned but was hoping it
wasnt necessary and would like to avoid it if possible so as not to pile up
the spreadsheet with data over and over.

So there is no way to have access recognize a range in excel thats 2 seperate
ranges named as one?
Click to expand...

No. Contiguous ranges only. Also, AFAIK the named range must be
specified as a plain cell reference (e.g. Sheet99!A1:AC12345); Access
won't recognise fancy stuff like a formula that returns a range.
Or to create a so called null column in my output query that would not output
anything to that column yet still take up that space?
Click to expand...

There's no such thing as a null column.
I guess I will use the automation method, would it be easier to have 2
different queries output to 2 seperate ranges in excel? Or is it wiser to
output directly from a recordset field into columns? (not sure I know how to
do the later off the top of my head)
Click to expand...

Probably I'd create two queries, one for each subset of columns, then
use VBA & Automation to

launch Excel and the workbook
open recordset rs1 on the first query and rs2 on the second
Dim R as Excel.Range
Set R = the first cell of the Time column
R.CopyFromRecordset rs1
Set R = the first cell of the "depth" column
R.CopyFromRecordset rs2
save and close the workbook
quit Excel

This is a useful article giving the basics of controlling Excel from
Access:
http://www.mvps.org/access/modules/mdl0006.htm

The following Microsoft Knowledgebase articles are helpful too:

123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(142476)
http://support.microsoft.com/?id=142476

There's also a white paper and code samples including tutorials
available from Microsoft at
http://support.microsoft.com/?id=253235
 
Back
Top