Output to Excel

[Robert Gillard]

What is the best way to do the following; -

run a query in Access to give a list of (say) 10 items long and 4 field wide
send it to an Existing excel spreadsheet
have it add the 10 items to the bottom of the spreadsheet
next week re-query the database and get a list of (say) 4 items
and again add it to the bottom of the spreadsheet below the 10 from the
previous week.

I am not a programmer, but more than willing to give it a go.( I tried
unsuccessfully to do this with macros from within Access. The problem was in
adding the items to the bottom of the Excel spreadsheet.)

with thanks

Bob

 

[onedaywhen]

You can do all of that within the query. Use an INSERT INTO query e.g.

INSERT INTO
[MyWorksheet$]
IN 'C:\MyExcelWorkbook.xls' 'EXCEL 8.0;'
SELECT
MyCol1, MyCol2, MyCol3, MyCol4
FROM
My Table
WHERE ...

An INSERT INTO query will always add rows to the bottom of your
existing Excel range.

 

[Robert Gillard]

Sorry I am not familiar with INSERT INTO could you just look at
this to see where I am going wrong.(It keeps coming up as "Syntax error
in INSERT INTO Statement")

INSERT INTO sheet1 [IN c:\dummy\book1.xls][Tab name]
SELECT PerDealtbl.[Tab Name]
FROM PerDealtbl;

with thanks

Bob

You can do all of that within the query. Use an INSERT INTO query e.g.

INSERT INTO
[MyWorksheet$]
IN 'C:\MyExcelWorkbook.xls' 'EXCEL 8.0;'
SELECT
MyCol1, MyCol2, MyCol3, MyCol4
FROM
My Table
WHERE ...

An INSERT INTO query will always add rows to the bottom of your
existing Excel range.

"Robert Gillard" <[email protected]> wrote in message

What is the best way to do the following; -

run a query in Access to give a list of (say) 10 items long and 4 field wide
send it to an Existing excel spreadsheet
have it add the 10 items to the bottom of the spreadsheet
next week re-query the database and get a list of (say) 4 items
and again add it to the bottom of the spreadsheet below the 10 from the
previous week.

I am not a programmer, but more than willing to give it a go.( I tried
unsuccessfully to do this with macros from within Access. The problem was in
adding the items to the bottom of the Excel spreadsheet.)

with thanks

Bob

 

[onedaywhen]

Robert,
Try this (you may prefer the alternative 'sqaure brackets' syntax):

INSERT INTO
[Excel 8.0;Database=c:\Tempo\db.xls].[sheet1$]
SELECT
[Tab Name]
FROM
PerDealtbl

Personal asides: avoid column names (any names!) with spaces in them,
use Pascal case instead e.g. TabName. And having 'tbl' in your table
name looks unprofessional (isn't it obvious it's a table?)

Sorry I am not familiar with INSERT INTO could you just look at
this to see where I am going wrong.(It keeps coming up as "Syntax error
in INSERT INTO Statement")

INSERT INTO sheet1 [IN c:\dummy\book1.xls][Tab name]
SELECT PerDealtbl.[Tab Name]
FROM PerDealtbl;

with thanks

Bob

You can do all of that within the query. Use an INSERT INTO query e.g.

INSERT INTO
[MyWorksheet$]
IN 'C:\MyExcelWorkbook.xls' 'EXCEL 8.0;'
SELECT
MyCol1, MyCol2, MyCol3, MyCol4
FROM
My Table
WHERE ...

An INSERT INTO query will always add rows to the bottom of your
existing Excel range.

"Robert Gillard" <[email protected]> wrote in message

What is the best way to do the following; -

run a query in Access to give a list of (say) 10 items long and 4 field wide
send it to an Existing excel spreadsheet
have it add the 10 items to the bottom of the spreadsheet
next week re-query the database and get a list of (say) 4 items
and again add it to the bottom of the spreadsheet below the 10 from the
previous week.

I am not a programmer, but more than willing to give it a go.( I tried
unsuccessfully to do this with macros from within Access. The problem was in
adding the items to the bottom of the Excel spreadsheet.)

with thanks

Bob

 

[Mary Osborne]

I have found that it is easier to run your query and then
got to Excel and inport the information. I loose to much
information exporting from Access to Excel but retain all
information by Importing from Excel.

Data/Get External Data/New Database Query/Ms Access
Database*/OK - at this point a window will pop up "Select
Database" you will now have to search for your particular
database, once found, another window "Query Window -
Choose Columns" will appear. Scroll through the list
until you find the table you are looking for. Double
click on it and it will open underneath will all of the
fields you have in your table. From there you can
customize the fields you need for your report. Follow the
instructions Query wizard. In the last Query Wizard
window - it will give you three options to select from -
choose "Return Data to Microsoft Excel" and then click
finish. A new pop up window will appear in
Excel "Returning External Data to Microsoft Excel". Again
there are choices of options to select from. I use the
first option "Where do you want the data?" Existing
Worksheet. You can then modify your worksheet cells and
colums as needed. I hope this helps.

Mary