Export Subform to excel

H

hllary

Registered User.
Local time
Today, 14:42
Joined
Sep 23, 2019
Messages
80
I'm trying to write some vba to export filtered records from a subform. I've found a number of post related to this issue and I've cobbled the code below from those post.

When I run it I get a run-time error saying the Object '__temp' already exist. When I click debug it highlights the line Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL).

Thank you for you help.

Code: 
Private Sub yourButtonToImportName()

Dim db As dao.Database
Dim qrydef As dao.QueryDef

Dim strSQL As String
Dim bolWithFilterOn As Boolean
Dim strTempQryDef As String
Dim strRecordSource As String

strTempQryDef = "__temp"

bolWithFilterOn = me.subsearch_frm.Form.FilterOn

strRecordSource = me.subsearch_frm.Form.RecordSource

If InStr(strRecordSource, "SELECT ") <> 0 Then
strSQL = strRecordSource
Else
strSQL = "SELECT * FROM [" & strRecordSource & "]"
End If

' just in case our sql string ends with ";"
strSQL = Replace(strSQL, ";", "")

If bolWithFilterOn Then
strSQL = strSQL & _
IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
me.subsearch_frm.Form.Filter
End If

Set db = CurrentDb

'create temporary query
Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
db.QueryDefs.Append qrydef
Set qrydef = Nothing

DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=strTempQryDef, _
FileName:=Replace(CurrentProject.Path & "\", "\\", "\") & strTempQryDef & ".xlsx"

' Delete the temporary query
db.QueryDefs.Delete strTempQryDef

Set db = Nothing

End Sub
 
Looks like your temp query is not being deleted. Try...
D
SQL: 
DoCmd.DeleteObject acQuery, strTempQryDef
 
it still gave me the same error.
Code: 
db.QueryDefs.Delete strTempQryDef

' I changed it to'

DoCmd.DeleteObject acQuery, strTempQryDef

was that correct?
 
[just export the subForm query,
DocmdTransferspreadsheet....


QUOTE="hllary, post: 1664178, member: 148956"]
I'm trying to write some vba to export filtered records from a subform. I've found a number of post related to this issue and I've cobbled the code below from those post.

When I run it I get a run-time error saying the Object '__temp' already exist. When I click debug it highlights the line Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL).

Thank you for you help.

Code: 
Private Sub yourButtonToImportName()

Dim db As dao.Database
Dim qrydef As dao.QueryDef

Dim strSQL As String
Dim bolWithFilterOn As Boolean
Dim strTempQryDef As String
Dim strRecordSource As String

strTempQryDef = "__temp"

bolWithFilterOn = me.subsearch_frm.Form.FilterOn

strRecordSource = me.subsearch_frm.Form.RecordSource

If InStr(strRecordSource, "SELECT ") <> 0 Then
strSQL = strRecordSource
Else
strSQL = "SELECT * FROM [" & strRecordSource & "]"
End If

' just in case our sql string ends with ";"
strSQL = Replace(strSQL, ";", "")

If bolWithFilterOn Then
strSQL = strSQL & _
IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
me.subsearch_frm.Form.Filter
End If

Set db = CurrentDb

'create temporary query
Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
db.QueryDefs.Append qrydef
Set qrydef = Nothing

DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=strTempQryDef, _
FileName:=Replace(CurrentProject.Path & "\", "\\", "\") & strTempQryDef & ".xlsx"

' Delete the temporary query
db.QueryDefs.Delete strTempQryDef

Set db = Nothing

End Sub
[/QUOTE]
 
hllary said:
it still gave me the same error.
Code: 
db.QueryDefs.Delete strTempQryDef

' I changed it to'

DoCmd.DeleteObject acQuery, strTempQryDef

was that correct?
Click to expand...
Yes that was correct but I see it didn't work. I see Ranman256 gave you some help, did that work?
 
Using doCmd.transferSpreadsheet is a little over my head. Can you please give me a little more info?
 
I got it to work. the problem was I need to delete the temporary query before I created it. I added the code below before the query was created.

Code: 
On Error Resume Next
DoCmd.DeleteObject acQuery, strTempQryDef
On Error GoTo 0

Thanks for everyone's help!
 
Instead of creating and deleting queries all the time, why not just amend the sql for that querydef?
 
Gasman said:
Instead of creating and deleting queries all the time, why not just amend the sql for that querydef?
Click to expand...
Gasman,

When exporting to Excel a query with parameters will error out unless saved first.
 
GinaWhipp said:
Gasman,

When exporting to Excel a query with parameters will error out unless saved first.
Click to expand...
Thanks Gina,
I was not aware of that but I was thinking
Amend Sql
Save
Run
but when I googled changing the sql, everyone appeared to only be amending the sql and then running, so save needed.?
 
Gasman said:
Thanks Gina,
I was not aware of that but I was thinking
Amend Sql
Save
Run
but when I googled changing the sql, everyone appeared to only be amending the sql and then running, so save needed.?
Click to expand...
Correct. If you are just running it with no parameters you are good, no Save needed. It's the parameters that change the *rules*.
 
hllary said:
I'm trying to write some vba to export filtered records from a subform. I've found a number of post related to this issue and I've cobbled the code below from those post.
Click to expand...
tough move
>just try the syntx below, then recode the entry form name []![] to subform
DoCmd.OutputTo acOutputForm, [FrmImprt/Exprt], "MicrosoftExcel5.0/95Workbook(*.xls)", "", True, "", , acExportQualityScreen
>i used this before, hope would work too for you
 
vhung said:
tough move
>just try the syntx below, then recode the entry form name []![] to subform
DoCmd.OutputTo acOutputForm, [FrmImprt/Exprt], "MicrosoftExcel5.0/95Workbook(*.xls)", "", True, "", , acExportQualityScreen
>i used this before, hope would work too for you
Click to expand...
Hmm, again a query with a parameter must be saved first or it will error out. Also the line you recommended is for older version so not recommended.
 
GinaWhipp said:
Hmm, again a query with a parameter must be saved first or it will error out. Also the line you recommended is for older version so not recommended.
Click to expand...
did you try the codes
>if not you cannot evalaute it's accuracy
>"or it will error out" what error is that, guessing error, be prompt
>"must be saved first" of course you have to save it, that is part of confirmation

well anyway, it was Hillary who seek for solution
>good luck to both of you
 
vhung said:
did you try the codes
>if not you cannot evalaute it's accuracy
>"or it will error out" what error is that, guessing error, be prompt
>"must be saved first" of course you have to save it, that is part of confirmation

well anyway, it was Hillary who seek for solution
>good luck to both of you
Click to expand...
Umm, nothing for me to evaluate as I have been doing this for quite some time which is why I replied to Hilary that way.
 
You must log in or register to reply here.

Similar threads

C
Having trouble getting a report to use two different recordsets
Replies
16
Views
855
MajP
MajP
B
missing values from sub
Replies
2
Views
463
Gasman
Gasman
N
How to ensure the open recordset include also any new record entered
Replies
9
Views
707
nector
N
adhoustonj
How many db's do I really have open?
Replies
5
Views
621
Pat Hartman
P
A
Match two numbers and then update
Replies
10
Views
726
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom