469,300 Members | 2,355 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

Export queries to Excel - specific sheet, specific range

21
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
Nov 16 '10 #1
11 43788
colintis
255 100+
Hi CarrieR,

Had you tried using the DoCmd.TransferSpreadsheet function?
Expand|Select|Wrap|Line Numbers
  1. Dim fileName As String
  2. fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 16 '10 #2
CarrieR
21
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...
Nov 16 '10 #3
colintis
255 100+
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.
Expand|Select|Wrap|Line Numbers
  1.     Dim xlApp As Excel.Application
  2.     Dim xlWB As Excel.Workbook
  3.     Dim xlWS As Excel.Worksheet
  4.     Dim acRng As Variant
  5.     Dim xlRow As Integer
  6.  
  7.     Dim qry As QueryDef
  8.     Dim rst As Recordset
  9.     Set xlApp = New Excel.Application
  10.     Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Workbook1.xlsx")
  11.     Set xlWS = xlWB.Worksheets("BB Data")
  12.  
  13.     xlRow = (xlWS.Columns("A").End(xlDown).Row)
  14.  
  15.     Set qry = CurrentDb.QueryDefs("Query A")
  16.     Set rst = qry.OpenRecordset
  17.  
  18.     Dim c As Integer
  19.     c = 1
  20.     xlRow = xlRow + 1
  21.  
  22.     Do Until rst.EOF
  23.         For Each acRng In rst.Fields
  24.             xlWS.Cells(xlRow, c).Formula = acRng
  25.             c = c + 1
  26.         Next acRng
  27.         xlRow = xlRow + 1
  28.         c = 1
  29.         rst.MoveNext
  30.         If xlRow > 25 Then GoTo rq_Exit
  31.     Loop
  32.  
  33. rq_Exit:
  34.     rst.Close
  35.     Set rst = Nothing
  36.     Set xlWS = Nothing
  37.     xlWB.Close acSaveYes
  38.     Set xlWB = Nothing
  39.     xlApp.Quit
  40.     Set xlApp = Nothing
  41.     Exit Function
  42.  
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.
Nov 16 '10 #4
CarrieR
21
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:
Expand|Select|Wrap|Line Numbers
  1. Dim c As Integer
  2. c = 1
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim c As Integer
  2. c = 1
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. xlRow = (xlWS.Columns("A").End(xlDown).Row) 
to:
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 9 '10 #5
colintis
255 100+
Yes you did misunderstood the code you tried to modified.

Expand|Select|Wrap|Line Numbers
  1. Dim c As Integer
  2. c = 1
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. xlWS.Cells(xlRow, c).Formula = acRng
If xlRow+1 is 41, and c = 3, then the xlWS.Cells will be point to cell C41
Dec 10 '10 #6
CarrieR
21
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!
Dec 10 '10 #7
colintis
255 100+
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.
Dec 11 '10 #8
sarak
1
hi,

I tried and this too works...

Expand|Select|Wrap|Line Numbers
  1. 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"
Mar 26 '12 #9
hurude
1
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"

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True, "Range1"
This will export directly to the correct sheets and range.
Feb 19 '14 #10
dsatino
393 256MB
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
Feb 19 '14 #11
zmbd
5,400 Expert Mod 4TB
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.
Feb 19 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by DC Gringo | last post: by
13 posts views Thread by Hemant Sipahimalani | last post: by
1 post views Thread by JawzX01 | last post: by
2 posts views Thread by Access user | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.