Access to excel export?

[SSHA_SP]

Hi,

More simple questions for you experts (apologies in advance)

I have a selection of Access Queries which are currently been manually pasted into various locations of an Excel template located (C:/MyDocuments/Weekly Report.xls):

[Query_Sales_000] pasted into “Sales Sheet” cell “A1”

[Query_Canx_001] pasted into “Canx Sheet” cell “A17”

Etc…

I know its possible to automate this process but all the examples I have found on here appear to complex for my simple requirements.

Any help will be much appreciated.

 

[namliam]

Is there some way to join your queries prior to exporting??

You will have to open the queries one by one and open an "excel application object", then copy/paste or write the values into excel

 

[SSHA_SP]

No there all separate entities. I guess I could lump them all together in a form/report if that makes it easier?

Thanks

 

[namliam]

I understand they are seperate entities... But if you can join them into 1 query, or even just replace all the seperate queries by 1... You can easily use "Docmd.Transferspreadsheet" to create your excel file.

Doing all the copy/paste stuff is a nightmare!

 

[SSHA_SP]

Ok sorry not trying to insult your intelligence. No I cant think of a way of bringing them all together.

I was just hoping there would be some code I could use that would copy Query 1 into Sheet 1 A1.

Then I could tailor this to suit. I know this is quite a lengthy way of doing things and could mean alot of hard coding but it should do the job even if its not the most efficient way.

Cheers

 

[namliam]

What you can do using Transferspreadsheet is export all the queries to one excel file.
Each query is then stored on a seperate tab in excel.

Then use coding to copy from each tab to 1 tab... Shouldnt be to hard to do....

There almost must be some way of joining the queries, how else can your data be shown on the sheet as one line?? The figures shown must have some kind of correlation / common denominator to make some kind of sense?? Otherwize it would just be random numbers??

 

[hgus393]

Hi,

More simple questions for you experts (apologies in advance)

I have a selection of Access Queries which are currently been manually pasted into various locations of an Excel template located (C:/MyDocuments/Weekly Report.xls):

[Query_Sales_000] pasted into “Sales Sheet” cell “A1”

[Query_Canx_001] pasted into “Canx Sheet” cell “A17”

Etc…

I know its possible to automate this process but all the examples I have found on here appear to complex for my simple requirements.

Any help will be much appreciated.

As Namliam states a transferspreadsheet macro would do the trick.

Function Macro1()
     DoCmd.TransferSpreadsheet acExport, 8, "[Customer]", "C:/MyDocuments/Weekly Report.xls", True, ""
End Function

There is another way from excel to access using ADODB another simple way in excel as well exists. Define your database as an ODBC source (mdb) then choose if you're using excel 2003 and below. Data - Import External Data - New Database Query and find your database that you defined. Then you choose your query and then export it to appropriate range. Next time is only to right click on range and choose refresh data.
//Bob

 

[SSHA_SP]

Thank you for the help I'll give that a go tomorrow.

 

[DCrake]

Here is an article from MS on transeferring data from Access to Excel

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

David

 

[gemma-the-husky]

i think if the spreadsheet is non-normalised, (as it will be) then it is very likely that a single query wont do the job.

you could make a table in access to look like the spreadsheet - populate that in access, then export the finished table

if you want to do it all in code, the pseudocode is something like this.
it won't be too hard, except doing everything manually, keeping track of variables etc, is a bit of a bind at times

open spreadsheet

open recordset for query1

while not eof recordset
   populate appropriate spreadsheet cell
   next item
wend
close recordset

open recordset for query2

while not eof recordset
   populate appropriate spreadsheet cell
   next item
wend
close recordset

open recordset for query3

while not eof recordset
   populate appropriate spreadsheet cell
   next item
wend
close recordset

etc

 

[SSHA_SP]

I went about it this way in the end, I dont know if its the best way but it does exactly what I want:

Option Compare Database
Function ExcelExport()
    Dim lngFieldCounter As Integer
    Dim strPath
    Dim fso
    Dim f As Boolean
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim db As Database
    Dim rs As Recordset
    Dim rsDetail As Variant
    Dim rsdetail1 As Variant ' SGP
    Dim rsdetail2 As Variant ' SGP1
       Dim rsSOS As Recordset
    Dim strSQL As String
    Dim strSelect As String
    Dim rsWh As Recordset
    Dim intCol As Integer
    Dim intExportedRows As Integer
    Dim i As Integer
    Dim excelApp As New Excel.Application
    Dim oWorkbook As New Excel.Workbook
    Set db = CurrentDb
    Set oWorkbook = excelApp.Workbooks.Add
    strPath = "Z:\Test.xls"
 
        f = fso.FileExists(strPath)
 
 '''''''''''''''''''''''''''''''''''''''''''''
 
        excelApp.Workbooks.Open strPath
        excelApp.Worksheets("Sheet1").Select
        Set rsDetail = db.OpenRecordset("Select * from [Query2]")
 
        excelApp.Worksheets("Sheet1").Range("D13").CopyFromRecordset rsDetail
 
        Set rsDetail = db.OpenRecordset("Select * from [Query3]")
 
        excelApp.Worksheets("Sheet2").Range("C13").CopyFromRecordset rsDetail
        Set rsdetail1 = db.OpenRecordset("select * from [0 Last Week Qry]")
        excelApp.ActiveWorkbook.SaveAs "Z:\CLStats" & "\Test" & rsdetail1.Fields("WEEK_YEAR") & ".xls"
 
 
    'excelApp.ActiveWorkbook.Save
     excelApp.Workbooks.Close
    excelApp.Quit
    Set fso = Nothing
    Set excelApp = Nothing
    Set oWorkbook = Nothing
 
 
    excelApp.Workbooks.Close
 
    excelApp.Quit
 
    End Function