Export queries to Excel - specific sheet, specific range

[CarrieR]

Hi,

I thought this was a simple issue, but apparently it's not. I need to export the contents of about 30 queries, each into a specific sheet, and cell range, of an existing Excel workbook.

Here are all of the details I need in one sample transfer:

Query = "Query A"
Excel workbook = "C:\Documents and Settings\All Users\Workbook1.xlsx"
Sheet = "BB DATA"
Range = B2:J32
HasFieldNames = True

There are a lot of threads about exporting to Excel from Access, but I have searched all afternoon and can't find an answer that works. Sorry if I'm being a noob on this, but I just can't figure this one out.

Thanks so much for any help

[colintis]

Hi CarrieR,

Had you tried using the DoCmd.TransferSpreadsheet function?

Dim fileName As String
fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True,"B2:J32"

Here's something extra I work along with as well, creating the file name more dynamically. The code below will create the excel file in the same location where the database located, and the file is named with current date when executed.

fileName = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "Report" & "-" & Format(Date, "yyyymmdd") & ".xlsx"

The transferSpreadsheet command would only be exporting the query to a new excel file, or replacing an existing file. So put this as a reference for creating a new file.

[CarrieR]

Yes, the problem with the TransferSpreadsheet command, unfortunately, is that it won't export to a specific sheet. This is a pretty vital need, since the spreadsheet is a (complex) template for a lot of reporting...

[colintis]

Sorry I maybe mis-understood the question. As you want to put the generated records into existing workbooks (excel files), here's a sample code on what I've done from my work.

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim acRng As Variant
    Dim xlRow As Integer
 
    Dim qry As QueryDef
    Dim rst As Recordset
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Workbook1.xlsx")
    Set xlWS = xlWB.Worksheets("BB Data")
 
    xlRow = (xlWS.Columns("A").End(xlDown).Row)
 
    Set qry = CurrentDb.QueryDefs("Query A")
    Set rst = qry.OpenRecordset
 
    Dim c As Integer
    c = 1
    xlRow = xlRow + 1
 
    Do Until rst.EOF
        For Each acRng In rst.Fields
            xlWS.Cells(xlRow, c).Formula = acRng
            c = c + 1
        Next acRng
        xlRow = xlRow + 1
        c = 1
        rst.MoveNext
        If xlRow > 25 Then GoTo rq_Exit
    Loop
 
rq_Exit:
    rst.Close
    Set rst = Nothing
    Set xlWS = Nothing
    xlWB.Close acSaveYes
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function
 

The code before at the end of loop is simply limiting the copy process only allow 25 records, so remove it as you like. In the end of the exit part, it is a good hand on having a .Close and .Quit, otherwise the excel won't close itself and you need to kill the process from Task manager.

[CarrieR]

Thanks for your help!
Sorry this response is a little late - I had to leave and come back to this particular problem for a while...

Problem is solved (mostly, except for one or two small issues that I can work around).

I did want to note the issues here, for others that might see this thread, or in case anyone wanted to respond.

To note, I followed the above code exactly. To adjust the starting row the query would be exported to, I changed these lines:

Dim c As Integer
c = 1
xlRow = xlRow + 1

the "x1Rox + 1" will insert the file data starting in Row 3. So to begin the data in Row 41, it would be changed to:

Dim c As Integer
c = 1
xlRow = xlRow + 39

If I misunderstood any of this please let me know. These were the two issues I had:

1. I couldn't adjust the column that the first record appeared in, even if I adjusted:

xlRow = (xlWS.Columns("A").End(xlDown).Row) 

to:

xlRow = (xlWS.Columns("B").End(xlDown).Row) 

Not sure if changing the first column can be done. To solve my issue, I just made sure all of my destinations began in column A, which worked out fine for this project.

2. Export did not export the field names, only the data. Again, this was OK, but not sure if this is because I was doing something wrong.

[colintis]

Yes you did misunderstood the code you tried to modified.

Dim c As Integer
c = 1
xlRow = xlRow + 1

xlRow simply record the row number of the 1st empty cell in your spreadsheet in column A. So if A3 is the first empty cell from top, then you will get 3 in xlRow+1, and therefore change the column from reading A to B is correct, if you are leaving column A for something else.

The variable c is the one that stores column number, in which c = 1 means column A, c will be 2 for column B and so on.

In part of the code I posted earlier.

xlWS.Cells(xlRow, c).Formula = acRng

If xlRow+1 is 41, and c = 3, then the xlWS.Cells will be point to cell C41

[CarrieR]

Thanks, I appreciate the clarification, and patience! This did exactly what I needed to do.

Just to note:
I changed the initial set of "xlRow = xlRow + 1" (the one before the loop) to simply set "xlRow = 3", or whatever row number I need for each of these I need to write. This worked perfectly.

Thanks again for your help!

[colintis]

No worries =)

As if you will be overwriting the information whenever you generate the data, then I do agree you use xlRow = 3, so that it will starts putting data from row 3 every time you execute the code.

[sarak]

hi,

I tried and this too works...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True, "BB DATA!B2:J32"

change the range as required... may be for 6th row.. it may come like "BB DATA!A6:J32"

[hurude]

Hi, If you use a named range in your spreadsheet you can export directly to it, for example

On worksheet:
Create a named range for DATA A6:J32 = "Range1"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True, "Range1"

This will export directly to the correct sheets and range.

[dsatino]

Just as a suggestion, you may look into doing it the other way around.

Excel can connect to a database and query it just as Access can.

http://office.microsoft.com/en-us/ex...010099664.aspx

[zmbd]

hurude: Hi, If you use a named range in your spreadsheet you can export directly to it, for example (...)

Be advised, this is an "undocumented" and therefore unsupported method. Reliance upon this method is discouraged as it may be removed, amended, or provide un-reliable results either now or in the future.

From Microsoft's website:
----------

DoCmd.TransferSpreadsheet Method (Access)Office 2010

Range
Optional
Variant
A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

----------

@hurude:
This method was already mentioned in Post#9
In the future, please carefully read the entire thread before posting to such an old thread. If dsatino had not already replied your post would simply have been removed as it covers old information.