Exporting Qry Data To Excel Range

[Punice]

Need help on exporting data from a query containing my monthly revenue to the revenue range of cells in an existing excel spreadsheet, for example.

Been experimenting for 3 weeks with no luck. So, once again, I'm asking for the expert help that I always get here. It's obvious that I don't know how to do it, regardless of all the 'Google' helps I've read & tried. (I know "poor baby".)

After learning how to do that, I'll use it and add the code to export data from other queries (material, dump, labor, taxes, etc.) to their specific named category ranges in the spreadsheet, by using the appropriate queries and matching category names & range names.

BTW, I've been working on this accounting DB for 2 years. Now, finishing it with the creation of the 'Profit & Loss' report in excel.:banghead:

So, here ya go: using MS Office 12 w/Access 2007 & Excel Ver.12.0.6732

Query name is: "PnLqryJob_Revenue_ByMthName"
xlsx file path is: "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx"
Excel file name is: "Jan-15PnL.xlsx"

Tried these & many more variations:

DoCmd.OutputTo acOutputQuery, "PnLqryJob_Revenue_ByMthName", "ExcelWorkbook(*.xlsx)", "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx", True, "Revenue", 0, acExportQualityPrint

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PnLqryJob_Revenue_ByMthName", "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx", -1, "Revenue"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "sqYourQueryNameOrTable", "x:\ExcelFile.xls", -1, "NameRangeInExcelWorkBook"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PnLqryJob_Revenue_ByMthName", "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx", True, "Revenue", 0, acExportQualityPrint

 

[sneuberg]

You can't specify an range when exporting a table using DoCmd.TransferSpreadSheet. https://msdn.microsoft.com/en-us/library/office/ff844793.aspx says this and it also doesn't say queries can be exported; just tables. I'm not 100% sure but I don't think you can output to an existing spreadsheet with DoCmd.OutputTo either. https://msdn.microsoft.com/en-us/library/office/ff192065.aspx doesn't show any parameter where you could specify where in the spreadsheet, a cell like C4 for example, the data would go.

You could do this yourself in VBA. http://www.devhut.net/2012/04/19/ms-access-VBA-export-records-to-excel/ would give you a good start. This would ultimately give you the most control, but before doing all that have you tried solving this by pulling the data in from the Excel side. I suggest you go to the Data tab in Excel, click From Access and see how that goes.

 

[Frothingslosh]

(Excel Range).CopyFromRecordset is what you're looking for.

 

[Punice]

Given these, is the syntax correct and should it do what I want?
Query name is: "PnLqryJob_Revenue_ByMthName"
xlsx file path is: "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx"
Excel file name is: "Jan-15PnL.xlsx"

DoCmd.OutputTo acOutputQuery, "PnLqryJob_Revenue_ByMthName", "ExcelWorkbook(*.xlsx)", "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx", True, "Revenue", 0, acExportQualityPrint

 

[Minty]

I don't think your use of the template file "revenue" will work. From MS

TemplateFile
Optional
Variant
A string expression that's the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.

 

[sneuberg]

No. For starters I don't believe "ExcelWorkbook(*.xlsx)" is a valid constant. See https://msdn.microsoft.com/en-us/library/office/aa220433(v=office.11).aspx you probably want acFormatXLS there. Also where you have "Revenue" that's suppose to be a full path to template file. " A string expression that's the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file." I don't think it applies to your situation.

 

[Frothingslosh]

You won't be able to do what you want with OutputTo.

CopyFromRecordset does precisely what you're asking for.

What you'll need to do is create an Excel object, use that to create a worksheet object. Example:

Public Function ExportXLData(xlFilePath as String, xlSheetName as String, xlCell as String, rs as DAO.Recordset) as Boolean

Dim wb as Object
Dim ws as Object
Dim XL as Object

     Set XL = CreateObject("Excel.Application")
     Set wb = XL.Workbooks.Open(xlFilePath)
     Set ws = wb.Sheets(xlSheetName)

     ws.Range(xlCell).CopyFromRecordset rs

     Set ws = Nothing
     wb.Save
     wb.Close
     Set wb = Nothing
     Set XL = Nothing

     ExportXLData = True

End Function

You'll need to pass the path to the Excel file, the worksheet name, the cell you want to use as the upper left corner of the exported data (in A1 format), and the recordset to be exported.

This doesn't check any of the parameters for errors or empty recordsets, and has no error handling - that's on you.

Also, this is technically untested air code, but it's based on code I've been using daily.

 

[sneuberg]

To use the code that Frothingslosh posted you will need to add a reference to the Microsoft Excel xx.0 Object Library. The xx will depend on your version of Access.

 

[Frothingslosh]

Actually, I used late binding specifically so he doesn't need to do that.

Edit: Errr...I meant to. Fixing that! Yeah, the code I based it on is early bound, and I kind of missed a couple references originally.

 

[Punice]

Re my initial posting and the following information:

Query name is: "PnLqryJob_Revenue_ByMthName"
xlsx file path is: "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx"
Excel file name is: "Jan-15PnL.xlsx"

This is the code that I thought would do what I wanted. If not, what code will?
DoCmd.OutputTo acOutputQuery, "PnLqryJob_Revenue_ByMthName", "ExcelWorkbook(*.xlsx)", "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx", True, "Revenue", 0, acExportQualityPrint

As you must have concluded by now, I'm not a programmer. I'm a 'code-patcher' Although, I learned a lot from the research I've done trying to solve my problem on my own, I'm still not familiar with a lot of the programming lingo, actual meanings of the terms and the more sophisticated code writing techniques.

I am, respectfully, requesting a complete sample of the exact code, using my named items, that will accomplish my objective. I think that after I have that code, I'll be able to modify it to copy the data from additional queries into the corresponding field ranges of the same spreadsheet.

 

[sneuberg]

As you must have concluded by now, I'm not a programmer. I'm a 'code-patcher' Although, I learned a lot from the research I've done trying to solve my problem on my own, I'm still not familiar with a lot of the programming lingo, actual meanings of the terms and the more sophisticated code writing techniques.

I suggest you keep on learning. Try the videos at https://www.youtube.com/watch?v=AefCUyzqElM&index=26&list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s I suspect you could learn what you need to know in a shorter time than the time it takes you to get complete solution from forums

I am, respectfully, requesting...

Well if you put it that way how can we refuse albeit I would prefer some sucking up more than respect.

The following is Frothingslosh's code with the additional code to open a recordset.

Public Sub ExportXLData(QueryName As String, xlFilePath As String, Optional xlSheetName As String = "Sheet1", Optional xlCell As String = "A1")

Dim wb As Object
Dim ws As Object
Dim XL As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(QueryName)
Set XL = CreateObject("Excel.Application")
Set wb = XL.Workbooks.Open(xlFilePath)
Set ws = wb.Sheets(xlSheetName)
ws.Range(xlCell).CopyFromRecordset rs
Set ws = Nothing
wb.Save
wb.Close
rs.Close
db.Close
Set wb = Nothing
Set XL = Nothing

End Sub

You can put this in a module as I did in the attached database. You can call this in the following manner.

Sub TestExportXLData()

'Query name is: "PnLqryJob_Revenue_ByMthName"
'xlsx file path is: "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx"
'Excel file name is: "Jan-15PnL.xlsx"

ExportXLData "PnLqryJob_Revenue_ByMthName", "C:\9_R11_TY-15\Marketing\Jan-15PnL\Jan-15PnL.xlsx"

End Sub

I tested this just as shown above and it works fine. I didn't include any error checking. Note that this puts the query data in Sheet1, Cell A1. You will have to provide parameters for something different.

 

[Punice]

Thanks for your help, still...
(1) The php opened to jibberish using Notebook++ & EditRocket.
(2) Confused a bit. which hunks of the code go where? I copied the 'module' code to a module & assigned the other to a control with
the proper name and to an on-click event. Reversed that and it
still didn't work.