Exporting query to excel

[Guest]

I am using the DoCmd.TransferSpreadsheet to transfer the result of a query
into excel. Is there a way to get the results being transfered from this
query to be inserted into specific fields of a worksheet in the workbook I
have listed in the DoCmd.TransferSpreadsheet statement. My statement looks
like this: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"qryJE", "C:\Documents and Settings\amontgomery\desktop\PROJECTS\PAYROLL
DATABASE\TEMPLATES\JournalEntryForm.xls", False
Which successfully sends the results of the query to a new worksheet. The
reason for sending the query result to excel is so that it can be viewed in
a certain format that is in excel. I have the form created that I want to use
to show this data. Each time the query is ran different information is
exported.

 

[John Nurick]

You may be able to get the results you want by specifying a Range
argument in the TransferSpreadsheet call. See Ken Snell's article at
http://alexdyb.blogspot.com/2006_07_01_alexdyb_archive.html,
under the heading "Export to Excel range".

 

[Guest]

Okay I created this function but I keep getting various error messages when I
click on the button that contains the export data.
This is the error that I receive "Run-time error ‘3061’:
To few parameters. Expected 4."
Here is what the code looks like:

Private Sub btnJE_Click()

On Error GoTo Err_btnJE_Click

MsgBox ExportQuery, vbInformation, "Your information has been sent to
microsoft excel in the following path-> C:\Documents and
Settings\amontgomery\Desktop\PROJECTS\PAYROLL
DATABASE\Database\JournalEntryTest.xls"


Exit_btnJE_Click:
Exit Sub

Err_btnJE_Click:
MsgBox Err.Description, vbCritical, "Error"
Resume Exit_btnJE_Click

End Sub

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.Worksheet

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 Worksheet and Database object
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open(sOutput)

sSQL = "SELECT * FROM qryJEtest"
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 spreadsheet
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&""&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

 

[John Nurick]

If you get that error on this line
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
it's probably because qryJEtest is a parameter query getting values
from a form.

You'll need to build the SQL statement in a string variable,
interpolating the actual values of the parameters, e.g.

Dim SQL As String
SQL = "SELECT * FROM MyTable" & vbCrLF _
& "WHERE SomeField='" & Forms("FFF").Controls("XXX").Value _
& "' AND OtherField<" & Forms("FFF").Controls("YYY").Value _
& ";"

 

[Guest]

Okay where would I put this segment of code that I will need to create.

 

[Guest]

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
that uses the parameters from two text boxes to determine which records need
to be displayed. I am running into the problem when I try to use both fields
like I have in the query I receive the {Run-time error ‘3075’:
Syntax error in date in query expression ‘PG = ‘RYU’ AND LOCATION# = ‘63’
AND CHECK_DT = ‘9/1/2007’ & ‘9/30/2007;’" - Error Message
The 3 fields that need a parameter are ADP Company, Location #, & Check
Date.

Here is what the segment of code look like:
sSQL = "SELECT * FROM tblAllPerPayPeriodEarnings " & vbCrLf & "WHERE PG = '"
& Forms("frmJE").Controls("cboADPCompany").Value & "' AND LOCATION# = '" &
Forms("frmJE").Controls("cboLocationNo").Value & "' AND CHECK_DT = '" &
Forms("frmJE").Controls("txtFrom").Value & "' & '" &
Forms("frmJE").Controls("txtTo").Value & ";"

Okay where would I put this segment of code that I will need to create.

If you get that error on this line
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
it's probably because qryJEtest is a parameter query getting values
from a form.

You'll need to build the SQL statement in a string variable,
interpolating the actual values of the parameters, e.g.

Dim SQL As String
SQL = "SELECT * FROM MyTable" & vbCrLF _
& "WHERE SomeField='" & Forms("FFF").Controls("XXX").Value _
& "' AND OtherField<" & Forms("FFF").Controls("YYY").Value _
& ";"

 

[Guest]

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 spreadsheet 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
that uses the parameters from two text boxes to determine which records need
to be displayed. I am running into the problem when I try to use both fields
like I have in the query I receive the {Run-time error ‘3075’:
Syntax error in date in query expression ‘PG = ‘RYU’ AND LOCATION# = ‘63’
AND CHECK_DT = ‘9/1/2007’ & ‘9/30/2007;’" - Error Message
The 3 fields that need a parameter are ADP Company, Location #, & Check
Date.

Here is what the segment of code look like:
sSQL = "SELECT * FROM tblAllPerPayPeriodEarnings " & vbCrLf & "WHERE PG = '"
& Forms("frmJE").Controls("cboADPCompany").Value & "' AND LOCATION# = '" &
Forms("frmJE").Controls("cboLocationNo").Value & "' AND CHECK_DT = '" &
Forms("frmJE").Controls("txtFrom").Value & "' & '" &
Forms("frmJE").Controls("txtTo").Value & ";"

 

[SHAWTY721 via AccessMonster.com]

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:

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.Worksheet

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 Worksheet 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 spreadsheet
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.
[QUOTE]
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 spreadsheet based on the query results
[QUOTE]
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[/QUOTE]
[quoted text clipped - 159 lines][QUOTE][QUOTE][/QUOTE][/QUOTE][/QUOTE]

 

[John Nurick]

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.

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:

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.Worksheet

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 Worksheet 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 spreadsheet
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.
[QUOTE]
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 spreadsheet based on the query results
[QUOTE]
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[/QUOTE]
[quoted text clipped - 159 lines][QUOTE][/QUOTE][/QUOTE][/QUOTE]

 

[Guest]

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

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.

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:

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.Worksheet

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 Worksheet 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 spreadsheet
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.
[QUOTE]
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 spreadsheet 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][/QUOTE][/QUOTE][/QUOTE]

 

[Guest]

The excel file is created when I click the 'Create Journal Entry' button. But
no data is entered into the spreadsheet. 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.

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

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.

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:

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.Worksheet

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 Worksheet 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 spreadsheet
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 spreadsheet 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][/QUOTE][/QUOTE][/QUOTE]

 

[John Nurick]

Hi Alicia,

Let me say again that you must step through your code (execute it line
by line) using F8 in order to see just what is happening. But from
what you say I think you need something like this

'get started much as now
...


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

At this point you can be sure you're either at the first record
in the recordset or (if there are no records) at rst.EOF.
So there's no need for your
If Not rst.BOF Then
and its End If after the error handler. Instead you could just use
something like this:

Do Until rst.EOF 'Until is easier to read than While Not
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("Branch Number").Value
.Range("K15") = rst.Fields("Account").Value
.Range("L15") = rst.Fields("Sub Account").Value
.Range("O15") = rst.Fields("SumOfGROSS").Value
.Range("Q15") = rst.Fields("Account Description").Value
End With

At this point you have poked the values of the fields of one record
into cells in the worksheet. You say you need "all the information
generated by the query to be returned to the same excel file": do you
mean that you want all the records on one worksheet, or each record on
a separate worksheet in the same workbook?

I assume you mean all records on one worksheet. I also presume that
the Branch Number in G3 is the same for all records. In that case you
have to move down one row for each record. You could do that with
something like this in place of the Do Until... snippet above. It uses
..Cells() instead of .Range(): Range("C8") is the same as Cells(8,3).

Dim j As Long

j = 15 'first row

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("Branch Number").Value
.Cells(j, 11).Value = rst.Fields("Account").Value
.Cells(j, 12).Value = rst.Fields("Sub Account").Value
.Cells(j, 15).Value = rst.Fields("SumOfGROSS").Value
.Cells(j, 17).Value = rst.Fields("Account Description").Value
End With
j = j + 1 'next row
rst.MoveNext 'next record
Loop

'Format

wbk.Sheets("JournalEntry").Range("G3,K15,L15,O15,Q15").Columns.AutoFit

wbk.Close True 'save and close the workbook

'Tidy up
...




Loop
rst.Close

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

The excel file is created when I click the 'Create Journal Entry' button. But
no data is entered into the spreadsheet. 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.

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

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"

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:

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.Worksheet

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 Worksheet 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 spreadsheet
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 spreadsheet 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][/QUOTE][/QUOTE][/QUOTE]

 

[SHAWTY721 via AccessMonster.com]

Yes I need all the records retrieved from the query to be entered into the
same worksheet. I need the information to appear in the different cells that
I listed in my original code is this possible. Also can you use a DLookup in
the query like when creating a query in design view in access.

Hi Alicia,

Let me say again that you must step through your code (execute it line
by line) using F8 in order to see just what is happening. But from
what you say I think you need something like this

'get started much as now
...


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

At this point you can be sure you're either at the first record
in the recordset or (if there are no records) at rst.EOF.
So there's no need for your
If Not rst.BOF Then
and its End If after the error handler. Instead you could just use
something like this:

Do Until rst.EOF 'Until is easier to read than While Not
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("Branch Number").Value
.Range("K15") = rst.Fields("Account").Value
.Range("L15") = rst.Fields("Sub Account").Value
.Range("O15") = rst.Fields("SumOfGROSS").Value
.Range("Q15") = rst.Fields("Account Description").Value
End With

At this point you have poked the values of the fields of one record
into cells in the worksheet. You say you need "all the information
generated by the query to be returned to the same excel file": do you
mean that you want all the records on one worksheet, or each record on
a separate worksheet in the same workbook?

I assume you mean all records on one worksheet. I also presume that
the Branch Number in G3 is the same for all records. In that case you
have to move down one row for each record. You could do that with
something like this in place of the Do Until... snippet above. It uses
.Cells() instead of .Range(): Range("C8") is the same as Cells(8,3).

Dim j As Long

j = 15 'first row

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("Branch Number").Value
.Cells(j, 11).Value = rst.Fields("Account").Value
.Cells(j, 12).Value = rst.Fields("Sub Account").Value
.Cells(j, 15).Value = rst.Fields("SumOfGROSS").Value
.Cells(j, 17).Value = rst.Fields("Account Description").Value
End With
j = j + 1 'next row
rst.MoveNext 'next record
Loop

'Format

wbk.Sheets("JournalEntry").Range("G3,K15,L15,O15,Q15").Columns.AutoFit

wbk.Close True 'save and close the workbook

'Tidy up
...




Loop
rst.Close

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

The excel file is created when I click the 'Create Journal Entry' button. But
no data is entered into the spreadsheet. Also I need all the information

[quoted text clipped - 149 lines]

 

[John Nurick]

It's not possible to put more than one value in the same cell, so if
you want to put more than one record on the worksheet you'll have to
have one set of cells per record (e.g. one record per row as I
suggested in the 'air code' in my last message).

Yes, you can use a DLookup() in the query, but it's often better to
use a join or a subquery.

Yes I need all the records retrieved from the query to be entered into the
same worksheet. I need the information to appear in the different cells that
I listed in my original code is this possible. Also can you use a DLookup in
the query like when creating a query in design view in access.

Hi Alicia,

Let me say again that you must step through your code (execute it line
by line) using F8 in order to see just what is happening. But from
what you say I think you need something like this

'get started much as now
...


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

At this point you can be sure you're either at the first record
in the recordset or (if there are no records) at rst.EOF.
So there's no need for your
If Not rst.BOF Then
and its End If after the error handler. Instead you could just use
something like this:

Do Until rst.EOF 'Until is easier to read than While Not
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("Branch Number").Value
.Range("K15") = rst.Fields("Account").Value
.Range("L15") = rst.Fields("Sub Account").Value
.Range("O15") = rst.Fields("SumOfGROSS").Value
.Range("Q15") = rst.Fields("Account Description").Value
End With

At this point you have poked the values of the fields of one record
into cells in the worksheet. You say you need "all the information
generated by the query to be returned to the same excel file": do you
mean that you want all the records on one worksheet, or each record on
a separate worksheet in the same workbook?

I assume you mean all records on one worksheet. I also presume that
the Branch Number in G3 is the same for all records. In that case you
have to move down one row for each record. You could do that with
something like this in place of the Do Until... snippet above. It uses
.Cells() instead of .Range(): Range("C8") is the same as Cells(8,3).

Dim j As Long

j = 15 'first row

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("Branch Number").Value
.Cells(j, 11).Value = rst.Fields("Account").Value
.Cells(j, 12).Value = rst.Fields("Sub Account").Value
.Cells(j, 15).Value = rst.Fields("SumOfGROSS").Value
.Cells(j, 17).Value = rst.Fields("Account Description").Value
End With
j = j + 1 'next row
rst.MoveNext 'next record
Loop

'Format

wbk.Sheets("JournalEntry").Range("G3,K15,L15,O15,Q15").Columns.AutoFit

wbk.Close True 'save and close the workbook

'Tidy up
...




Loop
rst.Close

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

The excel file is created when I click the 'Create Journal Entry' button. But
no data is entered into the spreadsheet. Also I need all the information

[quoted text clipped - 149 lines]

 

[SHAWTY721 via AccessMonster.com]

Okay the reason I brought up a DLookup is because that is what I was using in
the original query that I created to send the information to excel. The
fields that are being sent to excel are looked up based on the values that
are used in the parameters on the form. Also is there a way to Sum field
using this method like can be done using the Total feature in a query.

It's not possible to put more than one value in the same cell, so if
you want to put more than one record on the worksheet you'll have to
have one set of cells per record (e.g. one record per row as I
suggested in the 'air code' in my last message).

Yes, you can use a DLookup() in the query, but it's often better to
use a join or a subquery.

Yes I need all the records retrieved from the query to be entered into the
same worksheet. I need the information to appear in the different cells that

[quoted text clipped - 83 lines]

 

[SHAWTY721 via AccessMonster.com]

This is an example of the original query I created in the Design View of
Access:

SELECT DISTINCT DLookUp("GL_Acct","tblGLAllCodes","Dept=" & [GLDEPT]) AS
Account, DLookUp("GL_Subacct","tblGLAllCodes","Dept=" & [GLDept]) AS
Subaccount, DLookUp("AccountDescription","tblGLAllCodes","Dept=" & [GLDept])
AS [Account Description], tblAllPerPayPeriodEarnings.PG,
tblAllPerPayPeriodEarnings.[LOCATION#], Sum(tblAllPerPayPeriodEarnings.GROSS)
AS SumOfGROSS, DLookUp("PayrollType","tblGLAllCodes","Dept=" & [GLDept]) AS
PayrollType, DLookUp("[BranchNumber]","[tblAllADPCoCodes]","[ADPCompany]= '"
& [PG] & "' And [LocationNumber]= '" & [LOCATION#] & "'") AS [Branch Number],
tblAllPerPayPeriodEarnings.ID, tblAllPerPayPeriodEarnings.CHECK_DT
FROM tblAllPerPayPeriodEarnings
GROUP BY DLookUp("GL_Acct","tblGLAllCodes","Dept=" & [GLDEPT]), DLookUp
("GL_Subacct","tblGLAllCodes","Dept=" & [GLDept]), DLookUp
("AccountDescription","tblGLAllCodes","Dept=" & [GLDept]),
tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.[LOCATION#],
DLookUp("PayrollType","tblGLAllCodes","Dept=" & [GLDept]), DLookUp("
[BranchNumber]","[tblAllADPCoCodes]","[ADPCompany]= '" & [PG] & "' And
[LocationNumber]= '" & [LOCATION#] & "'"), tblAllPerPayPeriodEarnings.ID,
tblAllPerPayPeriodEarnings.CHECK_DT
HAVING (((tblAllPerPayPeriodEarnings.PG)=[Forms]![frmJE]![cboADPCompany]) AND
((tblAllPerPayPeriodEarnings.[LOCATION#])=[Forms]![frmJE]![cboLocationNo])
AND ((tblAllPerPayPeriodEarnings.CHECK_DT) Between ([Forms]![frmje]![txtFrom])
And ([Forms]![frmje]![txtTo])))
ORDER BY DLookUp("GL_Acct","tblGLAllCodes","Dept=" & [GLDEPT]), DLookUp
("GL_Subacct","tblGLAllCodes","Dept=" & [GLDept]);

[\Code]
[QUOTE]
Okay the reason I brought up a DLookup is because that is what I was using in
the original query that I created to send the information to excel. The
fields that are being sent to excel are looked up based on the values that
are used in the parameters on the form. Also is there a way to Sum field
using this method like can be done using the Total feature in a query.
[QUOTE]
It's not possible to put more than one value in the same cell, so if
you want to put more than one record on the worksheet you'll have to[/QUOTE]
[quoted text clipped - 11 lines][QUOTE][/QUOTE][/QUOTE]

 

[SHAWTY721 via AccessMonster.com]

I think that I have created a SQL statement that includes the join and
parameters but I am getting
{Run-time error '3075': Syntax error (missing operator) in query expression
‘WHERE PG = ‘RYU’ AND [LOCATION#] = ‘63’ AND CHECK_DT Between #9/1/2007# AND
#9/30/2007#’.}

The SQL Statement that is producing this error looks like this:
{ sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct,
tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.
AccountDescription , tblAllADPCoCodes.BranchNumber, Sum
(tblAllPerPayPeriodEarnings.GROSS) FROM tblAllADPCoCodes, tblGLAllCodes INNER
JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept =
tblAllPerPayPeriodEarnings.GLDEPT GROUP BY tblAllPerPayPeriodEarnings.GLDEPT,
tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept,
tblGLAllCodes.AccountDescription, tblAllPerPayPeriodEarnings.PG,
tblAllPerPayPeriodEarnings.[LOCATION#], tblAllADPCoCodes.BranchNumber,
tblAllPerPayPeriodEarnings.CHECK_DT, 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 & "#" & ";"}

This is an example of the original query I created in the Design View of
Access:

SELECT DISTINCT DLookUp("GL_Acct","tblGLAllCodes","Dept=" & [GLDEPT]) AS
Account, DLookUp("GL_Subacct","tblGLAllCodes","Dept=" & [GLDept]) AS
Subaccount, DLookUp("AccountDescription","tblGLAllCodes","Dept=" & [GLDept])
AS [Account Description], tblAllPerPayPeriodEarnings.PG,
tblAllPerPayPeriodEarnings.[LOCATION#], Sum(tblAllPerPayPeriodEarnings.GROSS)
AS SumOfGROSS, DLookUp("PayrollType","tblGLAllCodes","Dept=" & [GLDept]) AS
PayrollType, DLookUp("[BranchNumber]","[tblAllADPCoCodes]","[ADPCompany]= '"
& [PG] & "' And [LocationNumber]= '" & [LOCATION#] & "'") AS [Branch Number],
tblAllPerPayPeriodEarnings.ID, tblAllPerPayPeriodEarnings.CHECK_DT
FROM tblAllPerPayPeriodEarnings
GROUP BY DLookUp("GL_Acct","tblGLAllCodes","Dept=" & [GLDEPT]), DLookUp
("GL_Subacct","tblGLAllCodes","Dept=" & [GLDept]), DLookUp
("AccountDescription","tblGLAllCodes","Dept=" & [GLDept]),
tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.[LOCATION#],
DLookUp("PayrollType","tblGLAllCodes","Dept=" & [GLDept]), DLookUp("
[BranchNumber]","[tblAllADPCoCodes]","[ADPCompany]= '" & [PG] & "' And
[LocationNumber]= '" & [LOCATION#] & "'"), tblAllPerPayPeriodEarnings.ID,
tblAllPerPayPeriodEarnings.CHECK_DT
HAVING (((tblAllPerPayPeriodEarnings.PG)=[Forms]![frmJE]![cboADPCompany]) AND
((tblAllPerPayPeriodEarnings.[LOCATION#])=[Forms]![frmJE]![cboLocationNo])
AND ((tblAllPerPayPeriodEarnings.CHECK_DT) Between ([Forms]![frmje]![txtFrom])
And ([Forms]![frmje]![txtTo])))
ORDER BY DLookUp("GL_Acct","tblGLAllCodes","Dept=" & [GLDEPT]), DLookUp
("GL_Subacct","tblGLAllCodes","Dept=" & [GLDept]);

[\Code]
[QUOTE]
Okay the reason I brought up a DLookup is because that is what I was using in
the original query that I created to send the information to excel. The[/QUOTE]
[quoted text clipped - 7 lines][QUOTE][QUOTE][/QUOTE][/QUOTE][/QUOTE]

 

[SHAWTY721 via AccessMonster.com]

Using the following code I get one record from the query to populate the
excel spreadsheet. Also with the code below I get the error message: {Run-
time error '1004': Application-defined or object-defined error.} This same
message appears when I use the following pieces of code".Cells(j,11).Value =
rst.Fields("GL_Acct").Value
..Cell(j,12).Value = rst.Fields("GL_Subacct").Value
..Cell(j,12).Value = rst.Fields("GROSS").Value
..Cell(j,12).Value = rst.Fields("AccountDescription").Value"

sSQL = "SELECT  tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct,
tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.
AccountDescription , tblAllADPCoCodes.BranchNumber, Sum
(tblAllPerPayPeriodEarnings.GROSS) FROM tblAllADPCoCodes, tblGLAllCodes INNER
JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept =
tblAllPerPayPeriodEarnings.GLDEPT GROUP BY tblAllPerPayPeriodEarnings.GLDEPT,
tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept,
tblGLAllCodes.AccountDescription, tblAllPerPayPeriodEarnings.PG,
tblAllPerPayPeriodEarnings.[LOCATION#], tblAllADPCoCodes.BranchNumber,
tblAllPerPayPeriodEarnings.CHECK_DT HAVING PG = '" & Forms("frmJE").Controls
("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls
("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls
("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls
("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "#"
& ";"

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
.Range("G3") = rst.Fields("BranchNumber").Value
.Range("K15").Value = rst.Fields("GL_Acct").Value
.Range("L15").Value = rst.Fields("GL_Subacct").Value
.Range("O15").Value = rst.Fields("GROSS").Value   'not finding value
.Range("Q15").Value = rst.Fields("AccountDescription").Value
End With
J = J + 1
rst.MoveNext
Loop

[\CODE]

Is there any particular reason I get these messages or I can't get more than
one record to write to the excel spreadsheet.


Thanks!

[QUOTE="John"]
It's not possible to put more than one value in the same cell, so if
you want to put more than one record on the worksheet you'll have to
have one set of cells per record (e.g. one record per row as I
suggested in the 'air code' in my last message).

Yes, you can use a DLookup() in the query, but it's often better to
use a join or a subquery.
[QUOTE]
Yes I need all the records retrieved from the query to be entered into the
same worksheet. I need the information to appear in the different cells that[/QUOTE]
[quoted text clipped - 83 lines][QUOTE][QUOTE][/QUOTE][/QUOTE][/QUOTE]

 

[John Nurick]

There's always a reason. But if you won't step through the code in
order to find out where it goes wrong I'm not willing to guess.

On Fri, 02 Nov 2007 13:19:21 GMT, "SHAWTY721 via AccessMonster.com"

Is there any particular reason I get these messages or I can't get more than
one record to write to the excel spreadsheet.

<snip>

 

[SHAWTY721 via AccessMonster.com]

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then
that record is being written to the spreadsheet. I'm not real sure on how to
get all the results of the query to populate in the spreadsheet. Not sure if
I'm missing something that will allow all the results from the query to be
written into spreadsheet in the preceding cells.

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
J = 15
IRecords = IRecords + 1
.Range("G3") = rst.Fields("BranchNumber").Value
.Cells(J, 11).Value = rst.Fields("GL_Acct").Value
.Cells(J, 12).Value = rst.Fields("GL_Subacct").Value
.Cells(J, 15).Value = rst.Fields("GROSS").Value
.Cells(J, 17).Value = rst.Fields("AccountDescription").Value

End With
J = J + 1
rst.MoveNext
Loop
[\Code]

Any help on how I could accomplish this will be greatly appreciated.

Thanks!