Export to an already setup excel spreadsheet where columns<>

[Cheese_whiz]

Hi all,

I'm in a jam. I've been handed something to do that I haven't done before
and have to have it finished tonight.

I have been given an access db with three tables. I need data from those
three tables and then I need to export the results to an excel spreadsheet.
However, the data in the tables only respresents a portion of the columns in
the spreadsheet. In other words, some columns in the query either need to be
'placeholders' (empty and just to keep the data lined up properly), or I need
some way to specify which column in access goes to which column in excel.

Since I don't play much with Excel (or haven't), I was think I'd make the
query with placeholders and then just export it using the 'export data'
feature. However, I can't get the 'placeholders' to work in design view of
my query.

Questions (all related):
1. Can I add placeholders to a query?
2. Am I going about exporting this data in the right way?

ANY direction would be helpful. I'm jammed up until later this evening and
this HAS to be finished by morning.

Thanks,
CW

 

[Klatuu]

Having the placeholders will not work. You really need to have all the data
to be received from Access contiguous. That is, the columns all have to be
to gether. If you mix them and use place holders, the placeholder columns
will overwrite what is in the spreadsheet.

There are other ways to accomplish this. One would be to use automation,
but that takes a lot of code. Another would be to do the export to a
different sheet, then use Excel functions to reference the data in the sheet
with the mixed data.

 

[Cheese_whiz]

Thanks for the reply,

On the off chance you're still around and willing to reply (or someone else
is), does it make a difference if the excel sheet only has the column
headings in it right now (meaning no current data)?

How about this: Could I just make my query in access to include all the
data I do have there (in access....no placeholders), then rearrange the
columns in the empty spreadsheet to the order that matches my query (read:
move all the columns in the spreadsheet that I don't have data for to the
right, and all the columns I do have data for to the left...guess that's
redundant), and then export it to the spreadsheet that way? Will it fill my
first query column into the first spreadsheet column?

Related question: If I 'export', can I keep the column headings that exist
now?

Thanks again,
CW

 

[Ken Snell \(MVP\)]

This article will give you some information about some "tricks" for
exporting data via TransferSpreadsheet:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

However, as Dave (Klatuu) noted, you may need to use Automation to
accomplish your desired result, or use formulas in an EXCEL spreadsheet that
read the exported data on a separate worksheet in that file.
--

Ken Snell
<MS ACCESS MVP>

 

[Cheese_whiz]

Thanks Ken,

I'll check out the links. I wish I could just copy and paste the info, but
I don't think I can even do that... I can probably write the code for
automation, though, with a little work. I've done a little of that before.

Of course, the spreadsheet seems to be 'protected' and I have NO idea what
to do about that, but I guess that's a question for another board....

Thanks again,
CW

 

[Cheese_whiz]

Got the sheet unprotected. Who-Ray for me. Tune in later for more crying
and whinning, with occasional wimpering just for variety.

CW

 

[Cheese_whiz]

Well, I have a solution....not what I'd call 'elegant', but it does seem to
work:

1. To unprotect the sheet you can, oddly enough, just copy the whole thing
and paste it in another sheet.

2. Then, I just select the data in my query, by column, copy it, and then
go into the excel sheet and select the first couple or three cells under the
column where the data goes (NOT incuding the column header), and paste it.
It says the amount of data doesn't 'match' the selected cells, but if you
tell it to paste anyway, it does.

3. After I hand paste all the columns of data from access to their
respectve columns in excel, I'll have to delete the row #2 (the first pasted
row) because that row has the column headers from access.

After that, I should be all set. Like I said, it's not a very elegant
solution, and not practical for many jobs, but for this one time situation,
it's working for me.

Thanks for the help,
CW

 

[Klatuu]

NO, it is not elegant. If this is something you expect users to accomplish
with accuracy and regularity, you can expect some grief.
Here is what I would suggest.
Add an additional sheet to the workbook that you will use only for receiving
the data.
Create formulas in the sheet where you actually want the data to reference
the data in the sheet where the data is imported.
This way, you can automate the export and users wont have to do a lot of
manual (read opportunity for error) manipulation.

 

[Cheese_whiz]

Thanks for the reply, Dave.

I'll try what you suggest, but mostly for my own edification. In terms of
this particular task, it's a one-time thing and I've accomplished what they
wanted.

Thanks again for the help,
CW

 

[Klatuu]

okay, for a one timer it is fine.
Good luck.