By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Write records to Excel

P: n/a
Hi again,

What I need to do is get an entire table from access into excel. It
would have to over-write the existing data on the spreadsheet, and it
should not run unless it is asked to. (this is why the linked table or
MS Query method will not work.)

Is it possible to make this code write directly to Excell instead of
to the new table?

AppCmd1.CommandText = _
"INSERT INTO New_Complete_Data (Facility_Code, ColA, ColB,
ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL, ColM,
ColN, ColO, ColP, ColQ, ColR)" & _
"SELECT M1C.Facility_Code AS Facility_Code, M1C.C11 AS ColA,
M1C.C12 AS ColB, M1C.C14 AS ColC, M1C.C32, M1C.C43, M1C.C51, M1C.C52,
M1C.C53, M1C.C61, M1C.C71, M1C.C72 AS ColK, M1C.SumDenC16 AS ColL,
M1C.SumC16 AS ColM, M1C.SumDenC82 AS ColN, M1C.SumC82 AS ColO, M1C.C84
AS ColP, M1C.C91 AS ColQ, M1C.C102 " & _
"FROM M1C WHERE M1C.Facility_Code = '" & sClinic & "' "
AppCmd1.Execute LngAffected

Thanks in advance.
Jenni
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Why not create a procedure in excel that retrieves the info from access?

That way you could click on a command button to refresh the data.

In Excel, look up the "CopyFromRecordset" method (this only works with DAO
recordsets)

P
"Jenni" <jr*******@khulisa.com> wrote in message
news:db*************************@posting.google.co m...
Hi again,

What I need to do is get an entire table from access into excel. It
would have to over-write the existing data on the spreadsheet, and it
should not run unless it is asked to. (this is why the linked table or
MS Query method will not work.)

Is it possible to make this code write directly to Excell instead of
to the new table?

AppCmd1.CommandText = _
"INSERT INTO New_Complete_Data (Facility_Code, ColA, ColB,
ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL, ColM,
ColN, ColO, ColP, ColQ, ColR)" & _
"SELECT M1C.Facility_Code AS Facility_Code, M1C.C11 AS ColA,
M1C.C12 AS ColB, M1C.C14 AS ColC, M1C.C32, M1C.C43, M1C.C51, M1C.C52,
M1C.C53, M1C.C61, M1C.C71, M1C.C72 AS ColK, M1C.SumDenC16 AS ColL,
M1C.SumC16 AS ColM, M1C.SumDenC82 AS ColN, M1C.SumC82 AS ColO, M1C.C84
AS ColP, M1C.C91 AS ColQ, M1C.C102 " & _
"FROM M1C WHERE M1C.Facility_Code = '" & sClinic & "' "
AppCmd1.Execute LngAffected

Thanks in advance.
Jenni

Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

See the Access help article on the OutputTo Action/Method. Just use
the SELECT part of the query - IOW, don't use the INSERT INTO
statement.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6FjI4echKqOuFEgEQLswgCfSW7aSVM0lETwPGfN/nkpSZA1LO0AoM04
r0tStqY0yylr4uXsTdeRlNgP
=8qd0
-----END PGP SIGNATURE-----
Jenni wrote:
Hi again,

What I need to do is get an entire table from access into excel. It
would have to over-write the existing data on the spreadsheet, and it
should not run unless it is asked to. (this is why the linked table or
MS Query method will not work.)

Is it possible to make this code write directly to Excell instead of
to the new table?

AppCmd1.CommandText = _
"INSERT INTO New_Complete_Data (Facility_Code, ColA, ColB,
ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL, ColM,
ColN, ColO, ColP, ColQ, ColR)" & _
"SELECT M1C.Facility_Code AS Facility_Code, M1C.C11 AS ColA,
M1C.C12 AS ColB, M1C.C14 AS ColC, M1C.C32, M1C.C43, M1C.C51, M1C.C52,
M1C.C53, M1C.C61, M1C.C71, M1C.C72 AS ColK, M1C.SumDenC16 AS ColL,
M1C.SumC16 AS ColM, M1C.SumDenC82 AS ColN, M1C.SumC82 AS ColO, M1C.C84
AS ColP, M1C.C91 AS ColQ, M1C.C102 " & _
"FROM M1C WHERE M1C.Facility_Code = '" & sClinic & "' "
AppCmd1.Execute LngAffected

Thanks in advance.
Jenni


Nov 12 '05 #3

P: n/a
how about....

Option Compare Database
Option Explicit

'------------------------------------------------------------
' SendMeToExcel
'
'------------------------------------------------------------
Function SendMeToExcel()
On Error GoTo SendMeToExcel_Err

DoCmd.OutputTo acQuery, "Query1", "MicrosoftExcel(*.xls)",
"C:\ExportedTable.xls", False, ""
SendMeToExcel_Exit:
Exit Function

SendMeToExcel_Err:
MsgBox Error$
Resume SendMeToExcel_Exit

End Function

If you're going to overwrite the file each time, you can do this with
a macro (just tweak it to allow overwrite or include a Kill statement
to delete the file beforehand...
Nov 12 '05 #4

P: n/a
jr*******@khulisa.com (Jenni) wrote in message news:<db*************************@posting.google.c om>...
Hi again,

What I need to do is get an entire table from access into excel. It
would have to over-write the existing data on the spreadsheet, and it
should not run unless it is asked to. (this is why the linked table or
MS Query method will not work.)

Is it possible to make this code write directly to Excell instead of
to the new table?


yes it should be possible, but I don't see why MS Query would not be a
useful solution. When using MS Query the spreadsheet user can refresh
the tabledata on request if it is not defined to refresh during
loading. If you want to initiate the refresh from the
Access-environment a link to a named range in the spreadsheet can be
defined and linked as table within Access with the name
'New_complete_data' and the suggested sql would work supposed the
structure is according the into-clause. When in the Excelsheet a
automatic filter is defined for the table a rangename is automatically
created that can be used for the link-definition within Access. The
main concern is to define the datatypes correctly when defining the
link. My experience is that after Access97 the manipulation of data in
Excel from Access this way is very well possible without problems
supposed some precautions are taken like no formula within the
tablerange, enough free space below the table area to expand the table
and restrictions on the size of the table within the possibilities of
Excel...

Marc
Nov 12 '05 #5

P: n/a
Hi , and thanks a million for all the input, I have solved the problem
by
1. allowing the data to refresh only on request and not on loading,
and
2. then by refreshing the data in the click of a button.

I did not experience any problems at all with data types, and I could
not make the Outputto macro work as the data had to go into a
worksheet in a template type spreadsheet.

Thanks again.
Jenni
Nov 12 '05 #6

P: n/a
Hi again,

Just checking, is it possible to specify an existing worksheet in a
spreadhseet using the OutputTo command, and if so could you post and
example?

Thanks
Jenni
Nov 12 '05 #7

P: n/a
jr*******@khulisa.com (Jenni) wrote in message news:<db************************@posting.google.co m>...
Hi again,

Just checking, is it possible to specify an existing worksheet in a
spreadhseet using the OutputTo command, and if so could you post and
example?

Thanks
Jenni


I think if you're passing it to an existing worksheet with data in it,
you'll need this...

http://www.mvps.org/access/modules/mdl0035.htm

so that you can somehow figure out where the data should go and then
can put it in the proper location on your spreadsheet. If you use
OutputTo, you can specify that the data should be copied into a
template, but not exactly where it should go (I don't think, anyway).
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.