Exporting from Access2000 query to Excel2000 template file

M

michaelg51

Hi there, I'm hoping to get some help figuring this one out.

I've got a simple select query that I currently send to a report and have
exported to a file in Excel format.
Here is the pertinant code:
Dim stDocName As String

stDocName = "rptPassonReport"
DoCmd.OutputTo acReport, stDocName, acFormatXLS,
"S:\GTIS\NATLServ\PASSON\Passon Reports\temp passon.xls"

This dumps the results of the query into the Excel file starting at cell A1.
I'd like to have it go into a file that's already got some info in it,
basically just start at D1 instead of A1. Is this at all possible?

Thanks.
 
M

michaelg51

Thanks Tom, I'm a little further ahead I think but I'm having trouble getting
it to run based on a query instead of a table.
The pertinant line I believe is here:
Set rs = db.OpenRecordset("???", dbOpenForwardOnly)
I tried the query name and pasting in the SQL statement with no luck.
What little thing am I forgetting? :)

Thanks
Michael
 
T

Tom Wickerath

Hi Michael,

First, which of the three methods are you attempting to use: "sCopyFromRS",
"sCopyRSExample", or "sCopyRSToNamedRange"?
________________________

In each of these examples, I recommend changing this line of code:
Dim rs As Recordset

to this:
Dim rs As DAO.Recordset

The reason I make this recommendation is shown in this article:
ADO and DAO Library References in Access Databases
http://www.accessmvp.com/TWickerath/articles/adodao.htm

(Dev Ashish's article was written in the time of Access 97, before ADO was
made available within Access applications).
________________________
The pertinant line I believe is here:
Set rs = db.OpenRecordset("???", dbOpenForwardOnly)
Click to expand...

I don't understand why you are showing the question marks, instead of the
name of your SELECT query. You have verified that you are specifying a SELECT
query, and that the query returns some records when run, correct? Also, why
are you using dbOpenForwardOnly in place of the dbOpenSnapshot that is shown
in all three examples? Not that I would expect this to cause any issues, but
it still raises a question in my mind.
________________________
I tried the query name and pasting in the SQL statement with no luck.
Click to expand...

What exactly is happening? Are you getting any error messages? If so, what
is the message and error number, if available?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

michaelg51

Hi Tom, sorry I didn't include everything earlier. I'm using
sCopyRSToNamedRange, the question marks were a marker for myself that I
should have taken out, I was trying to figure out the correct syntax for the
SQL statement, and I tried dbOpenForwardOnly just to see if it would make a
difference.
The error I'm getting is 'Runtime error 13 Type mismatch', I checked on your
site and I changed the declarations for db and rs to match what you
reccomended.

Here is the code I have so far, I still think I'm not formatting the SQL
correctly but I can't see where the error is.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.database
Dim rs As DAO.Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Report"
Const conWKB_NAME = "C:\report.xls"
Const conRANGE = "A4:K4"

Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("SELECT tblTurnOver.txtIr,
tblTurnOver.txtSystem, tblTurnOver.txtDept, [intSevB] & " / " & [intSevP] & "
/ " & [intSevC] AS Severity, tblTurnOver.dteDateReported,
tblTurnOver.dteTimeReported, tblTurnOver.txtGroupEsc, ""OPS "" & [txtDmOps] &
" / " & ""OSSD "" & [txtDmOssd] AS DM, tblTurnOver.txtDesc,
tblLIrStatus.txtStatus, tblTurnOver.dteDateResolved,
tblTurnOver.dteTimeResolved, tblTurnOver.memComments,
tblTurnOver.booResolved, tblTurnOver.booPasson FROM tblLIrStatus INNER JOIN
tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus WHERE
(((tblTurnOver.booPasson)=True));", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
 
T

Tom Wickerath

Hi Michael,

Try this variation instead, if you really want to embed the SQL statement
within your code, instead of just referencing a saved query. To assist in
debugging, let's create a separate subroutine first, where you can print the
results to the Immediate window (open with <Ctrl><G>).

Note:
If you do not already have "Option Explicit" shown as the second line of
code, at the top of your module, as indicated below, then add it now:

Option Compare Database
Option Explicit

If you had to add this very important line of code, then see this Gem Tip to
learn more about Option Explicit, and how to configure your Visual Basic
Editor (VBE) to always include these two very important words in all newly
created modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Okay, so here is the test subroutine. My assumption is that this is a class
module, associated with a form, given your references to intSevB, intSevP,
intSevC, txtDmOps and txtDmOssd.

Sub Michael()
Dim strSQL As String

strSQL = _
"SELECT tblTurnOver.txtIr, tblTurnOver.txtSystem, " _
& "tblTurnOver.txtDept, " _
& [intSevB] & " / " & [intSevP] & " / " & [intSevC] & " AS Severity, " _
& "tblTurnOver.dteDateReported, tblTurnOver.dteTimeReported, " _
& "tblTurnOver.txtGroupEsc, OPS " & [txtDmOps] _
& "/ OSSD " & [txtDmOssd] & " AS DM, " _
& "tblTurnOver.txtDesc, tblLIrStatus.txtStatus, " _
& "tblTurnOver.dteDateResolved, tblTurnOver.dteTimeResolved, " _
& "tblTurnOver.memComments, tblTurnOver.booResolved, " _
& "tblTurnOver.booPasson " _
& "FROM tblLIrStatus " _
& "INNER JOIN tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus " _
& "WHERE tblTurnOver.booPasson=" & True

Debug.Print strSQL

End Sub

After copying and pasting the above subroutine into the module associated
with your form, click on Debug | Compile ProjectName (where ProjectName is
the name of your VBA project). Correct any compile time errors that you may
find, including in any other unrelated modules.

Add a temporary command button to your form, with a On Click action to run
the Michael subroutine. Examine the results in the Immediate Window. If you
do not see any obvious errors, then try copying and pasting the result from
the Immediate window into the SQL view of a new query. Try running your new
query. If there is an error, the query parser will usually give you a better
clue as to the source of the error versus trying to run the same SQL
statement in VBA. The query should run without any errors, if your
constructed SQL statement is correct.

After you are able to produce a working SQL statement, copy the declaration
for strSQL and the strSQL = line of code into your existing procedure. Then
make the following change:

Set rs = db.OpenRecordset (strSQL, dbOpenSnapshot)


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

michaelg51 said:
Hi Tom, sorry I didn't include everything earlier. I'm using
sCopyRSToNamedRange, the question marks were a marker for myself that I
should have taken out, I was trying to figure out the correct syntax for the
SQL statement, and I tried dbOpenForwardOnly just to see if it would make a
difference.
The error I'm getting is 'Runtime error 13 Type mismatch', I checked on your
site and I changed the declarations for db and rs to match what you
reccomended.

Here is the code I have so far, I still think I'm not formatting the SQL
correctly but I can't see where the error is.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.database
Dim rs As DAO.Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Report"
Const conWKB_NAME = "C:\report.xls"
Const conRANGE = "A4:K4"

Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("SELECT tblTurnOver.txtIr,
tblTurnOver.txtSystem, tblTurnOver.txtDept, [intSevB] & " / " & [intSevP] & "
/ " & [intSevC] AS Severity, tblTurnOver.dteDateReported,
tblTurnOver.dteTimeReported, tblTurnOver.txtGroupEsc, ""OPS "" & [txtDmOps] &
" / " & ""OSSD "" & [txtDmOssd] AS DM, tblTurnOver.txtDesc,
tblLIrStatus.txtStatus, tblTurnOver.dteDateResolved,
tblTurnOver.dteTimeResolved, tblTurnOver.memComments,
tblTurnOver.booResolved, tblTurnOver.booPasson FROM tblLIrStatus INNER JOIN
tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus WHERE
(((tblTurnOver.booPasson)=True));", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
Click to expand...
 
M

michaelg51

I think I've got it now Tom. I tried again referencing the query directly,
I'm not certain what I did differently but it works now without having to
embed the SQL. It now exports properly without overwriting what is in the
template file.
One last question though if you don't mind. There are two time fields in
the query that displayed fine when I was outputting the query via the
'DoCmd.OutputTo' command, that are now displaying as full date and time
instead of just Short Time(Short Time is the format of the Field in the
table).
Any ideas?

Thanks again for all your help!
Michael

Tom Wickerath said:
Hi Michael,

Try this variation instead, if you really want to embed the SQL statement
within your code, instead of just referencing a saved query. To assist in
debugging, let's create a separate subroutine first, where you can print the
results to the Immediate window (open with <Ctrl><G>).

Note:
If you do not already have "Option Explicit" shown as the second line of
code, at the top of your module, as indicated below, then add it now:

Option Compare Database
Option Explicit

If you had to add this very important line of code, then see this Gem Tip to
learn more about Option Explicit, and how to configure your Visual Basic
Editor (VBE) to always include these two very important words in all newly
created modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Okay, so here is the test subroutine. My assumption is that this is a class
module, associated with a form, given your references to intSevB, intSevP,
intSevC, txtDmOps and txtDmOssd.

Sub Michael()
Dim strSQL As String

strSQL = _
"SELECT tblTurnOver.txtIr, tblTurnOver.txtSystem, " _
& "tblTurnOver.txtDept, " _
& [intSevB] & " / " & [intSevP] & " / " & [intSevC] & " AS Severity, " _
& "tblTurnOver.dteDateReported, tblTurnOver.dteTimeReported, " _
& "tblTurnOver.txtGroupEsc, OPS " & [txtDmOps] _
& "/ OSSD " & [txtDmOssd] & " AS DM, " _
& "tblTurnOver.txtDesc, tblLIrStatus.txtStatus, " _
& "tblTurnOver.dteDateResolved, tblTurnOver.dteTimeResolved, " _
& "tblTurnOver.memComments, tblTurnOver.booResolved, " _
& "tblTurnOver.booPasson " _
& "FROM tblLIrStatus " _
& "INNER JOIN tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus " _
& "WHERE tblTurnOver.booPasson=" & True

Debug.Print strSQL

End Sub

After copying and pasting the above subroutine into the module associated
with your form, click on Debug | Compile ProjectName (where ProjectName is
the name of your VBA project). Correct any compile time errors that you may
find, including in any other unrelated modules.

Add a temporary command button to your form, with a On Click action to run
the Michael subroutine. Examine the results in the Immediate Window. If you
do not see any obvious errors, then try copying and pasting the result from
the Immediate window into the SQL view of a new query. Try running your new
query. If there is an error, the query parser will usually give you a better
clue as to the source of the error versus trying to run the same SQL
statement in VBA. The query should run without any errors, if your
constructed SQL statement is correct.

After you are able to produce a working SQL statement, copy the declaration
for strSQL and the strSQL = line of code into your existing procedure. Then
make the following change:

Set rs = db.OpenRecordset (strSQL, dbOpenSnapshot)


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

michaelg51 said:
Hi Tom, sorry I didn't include everything earlier. I'm using
sCopyRSToNamedRange, the question marks were a marker for myself that I
should have taken out, I was trying to figure out the correct syntax for the
SQL statement, and I tried dbOpenForwardOnly just to see if it would make a
difference.
The error I'm getting is 'Runtime error 13 Type mismatch', I checked on your
site and I changed the declarations for db and rs to match what you
reccomended.

Here is the code I have so far, I still think I'm not formatting the SQL
correctly but I can't see where the error is.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.database
Dim rs As DAO.Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Report"
Const conWKB_NAME = "C:\report.xls"
Const conRANGE = "A4:K4"

Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("SELECT tblTurnOver.txtIr,
tblTurnOver.txtSystem, tblTurnOver.txtDept, [intSevB] & " / " & [intSevP] & "
/ " & [intSevC] AS Severity, tblTurnOver.dteDateReported,
tblTurnOver.dteTimeReported, tblTurnOver.txtGroupEsc, ""OPS "" & [txtDmOps] &
" / " & ""OSSD "" & [txtDmOssd] AS DM, tblTurnOver.txtDesc,
tblLIrStatus.txtStatus, tblTurnOver.dteDateResolved,
tblTurnOver.dteTimeResolved, tblTurnOver.memComments,
tblTurnOver.booResolved, tblTurnOver.booPasson FROM tblLIrStatus INNER JOIN
tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus WHERE
(((tblTurnOver.booPasson)=True));", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
Click to expand...
Click to expand...
 
M

michaelg51

Please disregard my previous post, I was able to format the fields in the
query and have them exported to Excel in the correct format.

Thank you very much for your ealier help Tom!
Michael

michaelg51 said:
I think I've got it now Tom. I tried again referencing the query directly,
I'm not certain what I did differently but it works now without having to
embed the SQL. It now exports properly without overwriting what is in the
template file.
One last question though if you don't mind. There are two time fields in
the query that displayed fine when I was outputting the query via the
'DoCmd.OutputTo' command, that are now displaying as full date and time
instead of just Short Time(Short Time is the format of the Field in the
table).
Any ideas?

Thanks again for all your help!
Michael

Tom Wickerath said:
Hi Michael,

Try this variation instead, if you really want to embed the SQL statement
within your code, instead of just referencing a saved query. To assist in
debugging, let's create a separate subroutine first, where you can print the
results to the Immediate window (open with <Ctrl><G>).

Note:
If you do not already have "Option Explicit" shown as the second line of
code, at the top of your module, as indicated below, then add it now:

Option Compare Database
Option Explicit

If you had to add this very important line of code, then see this Gem Tip to
learn more about Option Explicit, and how to configure your Visual Basic
Editor (VBE) to always include these two very important words in all newly
created modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Okay, so here is the test subroutine. My assumption is that this is a class
module, associated with a form, given your references to intSevB, intSevP,
intSevC, txtDmOps and txtDmOssd.

Sub Michael()
Dim strSQL As String

strSQL = _
"SELECT tblTurnOver.txtIr, tblTurnOver.txtSystem, " _
& "tblTurnOver.txtDept, " _
& [intSevB] & " / " & [intSevP] & " / " & [intSevC] & " AS Severity, " _
& "tblTurnOver.dteDateReported, tblTurnOver.dteTimeReported, " _
& "tblTurnOver.txtGroupEsc, OPS " & [txtDmOps] _
& "/ OSSD " & [txtDmOssd] & " AS DM, " _
& "tblTurnOver.txtDesc, tblLIrStatus.txtStatus, " _
& "tblTurnOver.dteDateResolved, tblTurnOver.dteTimeResolved, " _
& "tblTurnOver.memComments, tblTurnOver.booResolved, " _
& "tblTurnOver.booPasson " _
& "FROM tblLIrStatus " _
& "INNER JOIN tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus " _
& "WHERE tblTurnOver.booPasson=" & True

Debug.Print strSQL

End Sub

After copying and pasting the above subroutine into the module associated
with your form, click on Debug | Compile ProjectName (where ProjectName is
the name of your VBA project). Correct any compile time errors that you may
find, including in any other unrelated modules.

Add a temporary command button to your form, with a On Click action to run
the Michael subroutine. Examine the results in the Immediate Window. If you
do not see any obvious errors, then try copying and pasting the result from
the Immediate window into the SQL view of a new query. Try running your new
query. If there is an error, the query parser will usually give you a better
clue as to the source of the error versus trying to run the same SQL
statement in VBA. The query should run without any errors, if your
constructed SQL statement is correct.

After you are able to produce a working SQL statement, copy the declaration
for strSQL and the strSQL = line of code into your existing procedure. Then
make the following change:

Set rs = db.OpenRecordset (strSQL, dbOpenSnapshot)


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

michaelg51 said:
Hi Tom, sorry I didn't include everything earlier. I'm using
sCopyRSToNamedRange, the question marks were a marker for myself that I
should have taken out, I was trying to figure out the correct syntax for the
SQL statement, and I tried dbOpenForwardOnly just to see if it would make a
difference.
The error I'm getting is 'Runtime error 13 Type mismatch', I checked on your
site and I changed the declarations for db and rs to match what you
reccomended.

Here is the code I have so far, I still think I'm not formatting the SQL
correctly but I can't see where the error is.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.database
Dim rs As DAO.Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Report"
Const conWKB_NAME = "C:\report.xls"
Const conRANGE = "A4:K4"

Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("SELECT tblTurnOver.txtIr,
tblTurnOver.txtSystem, tblTurnOver.txtDept, [intSevB] & " / " & [intSevP] & "
/ " & [intSevC] AS Severity, tblTurnOver.dteDateReported,
tblTurnOver.dteTimeReported, tblTurnOver.txtGroupEsc, ""OPS "" & [txtDmOps] &
" / " & ""OSSD "" & [txtDmOssd] AS DM, tblTurnOver.txtDesc,
tblLIrStatus.txtStatus, tblTurnOver.dteDateResolved,
tblTurnOver.dteTimeResolved, tblTurnOver.memComments,
tblTurnOver.booResolved, tblTurnOver.booPasson FROM tblLIrStatus INNER JOIN
tblTurnOver ON tblLIrStatus.ID = tblTurnOver.intStatus WHERE
(((tblTurnOver.booPasson)=True));", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Wickerath

Glad to see that you got it working. It looks like you ended up referencing
the saved query directly. Did you try using the method where you build the
equivalent SQL statement in VBA code? One advantage to building the SQL
statement in code is that your form's functionality is dependent on one less
query, which someone else might change or delete in the future, if they
didn't realize that it was being used by your export routine.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

michaelg51

Yes I'm referencing the query directly but not building the SQL statement in
VBA, I thought it might be easier to change only the query if the required
fields for output changed instead of hunting for the correct entry through
the code.

Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting query to Temp drive 4
VBA Excel File SaveAs 7
exporting query to a definite cell in excel 1
Export Query to Excel 1
exporting a text file from a query 2
Cant save data to file selected 2
Error 2950 from macro when exporting to Sharepoint 0
Exporting to Excel - Filename 2

Top