Re: Exporting query to excel

Tech-Archive recommends: Fix windows errors by optimizing your registry

The excel file is created when I click the 'Create Journal Entry' button. But
no data is entered into the spread***. Also I need all the information
generated by the query to be returned to the same excel file. No rows are
being processed according to the Dialog box that appears after I return to
Access.

"SHAWTY721" wrote:

I realize that the code is hard to read I am going through it and removing
unncessary variables. Also about the If... structure would it be good to move
the End If after the rst.MoveNext line. Or would it be better before that.

Thanks for all the help,
Alicia

"John Nurick" wrote:

Your code is unnecessarily hard to read thanks to the unused
variables, overlong lines and inconsistent indentation. If it weren't
for the last of these you'd have noticed that the If... structure that
begins with
If Not rst.BOF Then
terminates with an
End If
*after* the error handler. This means that if the query ever gives you
an empty recordset the code will skip to the End If and exit with no
return value and no clue what might have happened.

(By the way, if I want to disable an error handler I never use
Application.SetOption "Error Trapping", 0
in code. I usually leave the option set to break on unhandled errors,
and then comment out the On Error GoTo statement. Safer because
otherwise you'll cause confusion or worse when the error trapping
option gets set to the wrong value.)

Beyond saying "nothing to be written to the excel file" you don't give
any clue what is happening. The code appears to create one excel file
per record returned by the query, and you don't say whether these
files are being created but withoutthe contents, or that they aren't
even being created.

Have you stepped through the code statement by statement (using F8 or
the button on the debugging tool bar)? This is the best way to find
out what's happening.

On Tue, 30 Oct 2007 18:52:05 GMT, "SHAWTY721 via AccessMonster.com"
<u38136@uwe> wrote:

Can anyone tell by looking at my code if there is something causing nothing
to written to the excel file. Here is a copy of the updated code:
[Code]
Public Function ExportQuery() As String
On Error GoTo err_Handler

'Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Work***

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim IRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 3
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

'Set to break on all errors
Application.SetOption "Error Trapping", 0

'Start with clean file built from template file
sTemplate = CurrentProject.Path & "\JournalEntryTest.xls"
sOutput = CurrentProject.Path & "\JournalEntryFormTest.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

'Create the Excel Application, Workbook and Work*** and Database object
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open(sOutput)

'sSQL = "SELECT * FROM qryJEtest"
sSQL = "SELECT * FROM tblAllPerPayPeriodEarnings " & vbCrLf & "WHERE PG =
'" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND ('LOCATION#') =
'" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND CHECK_DT Between
#" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").
Controls("txtTo").Value & "#" & ";"


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If Not rst.BOF Then
rst.MoveFirst
'For this template, the data must be placed in the appropriate cells of
the spread***
Do While Not rst.EOF
With wbk
.Sheets("JournalEntry").Range("G3") = rst.Fields("Branch Number")
.Sheets("JournalEntry").Range("K15") = rst.Fields("Account")
.Sheets("JournalEntry").Range("L15") = rst.Fields("Sub Account")
.Sheets("JournalEntry").Range("O15") = rst.Fields("SumOfGROSS")
.Sheets("JournalEntry").Range("Q15") = rst.Fields("Account
Description")
.Sheets("JournalEntry").Range("G3,K15,L15,O15,Q15").Columns.
AutoFit
.SaveAs CurrentProject.Path & "\" & rst.Fields("Branch Number") &
".xls"
'.SaveAs CurrentProject.Path & "\" & rst.Fields("Branch Number")
& "" & ("Description") & ".xls"
End With
rst.MoveNext

Loop
rst.Close

ExportQuery = "Total of " & IRecords & " rows processed."

exit_Here:
'Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportQuery = Err.Description
Resume exit_Here
End If
End Function

[\Code]

Any clues and help would be greatly appreciated I've been trying to figure it
out but no luck.

SHAWTY721 wrote:
I figured out why I was getting that error message it was from not having the
proper ## around the date fields. Also now that the code runs successfully
nothing is being written into the excel spread*** based on the query results

Okay here is the code that I created based on what you gave me but I am
having a issue with oone of the parameters. I have a field called Check Date
[quoted text clipped - 159 lines]
--
John Nurick - Access MVP
--
John Nurick - Access MVP

.

Quantcast