David A Gibson
dgibso at uark.edu
Thu Nov 30 10:16:14 CST 2006
Been following this topic with interest. I do an XLS export from a
Form command button onClick event.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"qry1234", "Drive:\Dir\subDir\Filename" & ".xls", yes, ""
It will overwrite the file each time but you can always append the
date by using
Filename_" & Format(Date, "mmddyy") & ".xls"
Works for me.
David G.
At 09:55 AM 11/30/2006, you wrote:
>Keith:
>
>Here's a simple, brute force method of creating a CSV file with quotes
>around the strings. But if you need to do the formulas, you'll need to make
>a spreadsheet. I'll send you a doc with that method in it off line.
>
>Rocky
>
>Private Sub cmdExport_Click()
>
>strSQL = "SELECT tblEmployer.fldEmployerLBFCode, tblEmployee.fldEmployeeSSN,
>" _
> & " [fldWorkDetailMonth] & [fldWorkDetailYear] AS [Month] , " _
> & "[fldEmployeeLastName] & ',' & [fldEmployeeFirstName] AS Name, " _
> & "Sum(tblWorkDetail.fldWorkDetailHours) AS SumOffldWorkDetailHours, " _
> & "Sum(tblWorkDetail.fldWorkDetailTotal) AS SumOffldWorkDetailTotal FROM
>" _
> & "tblEmployer RIGHT JOIN (tblPlan INNER JOIN (tblEmployee INNER JOIN "
>_
> & "tblWorkDetail ON tblEmployee.fldEmployeeID =
>tblWorkDetail.fldEmployeeID) ON " _
> & "tblPlan.fldPlanID = tblWorkDetail.fldPlanID) ON
>tblEmployer.fldEmployerID = " _
> & "tblWorkDetail.fldEmployerID WHERE (((tblPlan.fldPlanName) = '401a')
>And " _
> & "((tblWorkDetail.fldWorkDetailEntryMonth) = " _
> & Forms!frm401aMonthlyReport!cboEntryMonth.Column(0) & ") And " _
> & "((tblWorkDetail.fldWorkDetailEntryYear) = " _
> & Forms!frm401aMonthlyReport!cboEntryYear.Column(0) & ")) GROUP BY " _
> & "tblEmployer.fldEmployerLBFCode, tblEmployee.fldEmployeeSSN, " _
> & "[fldWorkDetailMonth] & [fldWorkDetailYear], " _
> & "[fldEmployeeLastName] & ',' & [fldEmployeeFirstName] " _
> & "ORDER BY tblEmployee.fldEmployeeSSN;"
>
>Set db = CurrentDb
>Set rs = db.OpenRecordset(strSQL)
>
>If rs.BOF = True And rs.EOF = True Then
> MsgBox "No Records to Export.", vbExclamation
> Exit Sub
>End If
>
>txtExportFile = db.Name
>
>Do While Right(txtExportFile, 1) <> "\"
> txtExportFile = Left(txtExportFile, Len(txtExportFile) - 1)
>Loop
>txtExportFile = txtExportFile & "401 Text File For " &
>Me.cboEntryMonth.Column(1) & " " & Me.cboEntryYear.Column(0) & ".txt"
>
>On Error Resume Next
>Kill txtExportFile
>On Error GoTo 0
>
>Open txtExportFile For Output As #1
>
>Do While Not rs.EOF
> Print #1, """" & Right("00000" & Trim(str(Nz(rs!fldEmployerLBFCode))),
>5) _
> & """" & "," & """" & Right("000000000" &
>Trim(str(rs!fldEmployeeSSN)), 9) _
> & """" & "," & """" & rs!Name & """" _
> & "," & """" & AlphaMonthYear(rs!Month) & """" & "," _
> & rs![SumOffldWorkDetailHours] & "," & rs![SumOffldWorkDetailTotal]
> rs.MoveNext
>Loop
>
>Close #1
>
>MsgBox "Export Done.", vbExclamation
>
>End Sub
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson
>Sent: Thursday, November 30, 2006 7:39 AM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Still on the Reporting
>
>Thanks, Rocky.
>
>Well...it is only out of the running...because I couldn't find any
>reference online for doing this....that wasn't A) bought; and B)
>problematic. If you've got something otherwise.....I'd LOVE to put it
>back into the running.
>
>:)
>
>Thanks,
>
>Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
>
>RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
>21231-3305
>
>410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Beach Access
>Software
>Sent: Thursday, November 30, 2006 10:21 AM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Still on the Reporting
>
>Keith:
>
>I've also got a module that exports to a comma delimited, CSV file with
>quotes around the text fields. Can send that off-line if it would help.
>
>Why is CSV out of the running now?
>
>Rocky
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
>Williamson
>Sent: Thursday, November 30, 2006 7:08 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Still on the Reporting
>
>Man...I forgot what a pain in the A@& getting reports out of Access is.
>
>
>
>I asked earlier how to export a report into csv format....doesn't seem
>that is likely. I need to ultimately generate a csv format file, to
>import into our sql-based application. This is for journal entries. I
>have written a couple of reports to generate journal
>entries....utilizing the different levels of grouping and summing. A
>good bit of the report is taking data, at different groupings, and
>calculating percentages to apply to user-entered data (for allocating
>overhead to different entities and account numbers.) Since csv is out,
>I am resigned to bring the data into excel...and then export to csv
>format.
>
>
>
>The problem is that, oddly, when I am looking at the data on the report
>(on screen) and then hit the "Analyze It with Excel" button....the
>columns come over in different order than the report. Plus, one of the
>fields is a text field = "00". It keeps coming into Excel as = 0
>(general format.) I can't think of a way to store the values, and
>export values and formulas to Excel...as Rocky suggested.
>
>
>
>Any help is greatly appreciated.
>
>
>
>Regards,
>
>
>
>Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
>
>RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
>21231-3305
>
>410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
><http://www.rtkl.com/>
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.430 / Virus Database: 268.15.0/557 - Release Date:
>11/29/2006
>4:15 PM
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.430 / Virus Database: 268.15.0/557 - Release Date: 11/29/2006
>4:15 PM
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
David A. Gibson
Computer Support Specialist II
University of Arkansas
232 Silas H. Hunt Hall
1 University of Arkansas
Fayetteville, Arkansas 72701
800-377-8632
(479) 575-4654
<http://www.uark.edu>www.uark.<http://www.uark.edu>edu
The University of Arkansas
Nationally Competitive- Student Centered- Research University