Extract Data From Access Query to Excel

[kbsudhir]

Hi All,

I have a query in access which is pulling the required data. I want to know how to extract that from that query to the excel.

Please guide.

Thanks
Sudhir

:doh:

[stanl]

Simplest way...Use ADO and the jet 4.0 provider - see

http://support.microsoft.com/kb/295646

Stan

[CreganTur]

Another option is the TransferSpreadsheet method- you can use it to create a new Excel Workbook based off of either tables or queries.

There are excellent resources available for this method in Access Help.

[kbsudhir]

Thanks,

I am able to the same by creating a report in acess & then exporting that report to excel as given below.

DoCmd.OutputTo acReport, stDocName, acFormatXLS, "D:\Sudhir\Remainder.xls", True

Here every time I run this I need to replace the previous file. & I have manually click replace.
1. How to replace the file by default.

2. Then I have to format the two columns of this excel sheet as "m/d/yy h:mm AM/PM;@".
3. Then send this file as attachment to the perdecided addresses.

I am trying code these in access form button click.

Please guide.

Thanks
Sudhir


Thanks
Sudhir

[CreganTur]

1. How to replace the file by default.

You can't replace it, but you can get rid of the original file using the Kill method- search for it in Access VBA help for specifics. It deletes the specified file.


2. Then I have to format the two columns of this excel sheet as "m/d/yy h:mm AM/PM;@".
If you format your report to show the data in your desired formats then you won't have to do anything special to the exported spreadsheet.


Then send this file as attachment to the perdecided addresses.

Take a look at this. (http://www.vbaexpress.com/forum/showthread.php?t=23294&highlight=attachment)

[kbsudhir]

Thanks Cregan, I really appreciate your time & help.

When I am exporting the report to excel there are two fields where I get the data in two fileds are in the following format "11/19/2008 10:25:26 AM"
but once exported to excel it shows only the date "11/19/2008" not the time as originally. But when we focus on that cell then we can see the complete data "11/19/2008 10:25:26 AM" in the formula bar.

How to fix this ..????
:dunno :dunno
:dunno

Thanks
Sudhir

[CreganTur]

It's a display property of Excel. It's not something that you can change from Access via VBA (that I know of). As you said, the data is there when you focus on the cell, it just displays differently.

The only suggestion I can offer is to see if you can pull an answer from Google, or create a new thread in the Excel help section.

[kbsudhir]

I am getting an error "User-defined type not defined"
in the line
Dim appOutlook As Outlook.Application

But when I try to add the reference of "Micorsoft Outlook 11.0 Object Library" I am getting the error

"Name conflicts with existing module, project or teh library"

But I do not have any conflicts, then why the error..??

Thanks
Sudhir

[kbsudhir]

My Database name is OutlookDB & one of the tables is named as "Outlook", is this creating the problem..??

[kbsudhir]

I have changed my table name "Outlook" to "OutlookTable" but of no use.