Export Subform to excel (1 Viewer)

[hllary]

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.

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

 

[GinaWhipp]

Looks like your temp query is not being deleted. Try...
D

DoCmd.DeleteObject acQuery, strTempQryDef

 

[hllary]

it still gave me the same error.

db.QueryDefs.Delete strTempQryDef

' I changed it to'

DoCmd.DeleteObject acQuery, strTempQryDef

was that correct?

 

[Ranman256]

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

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]

 

[GinaWhipp]

it still gave me the same error.

db.QueryDefs.Delete strTempQryDef

' I changed it to'

DoCmd.DeleteObject acQuery, strTempQryDef

was that correct?

Yes that was correct but I see it didn't work. I see Ranman256 gave you some help, did that work?

 

[Uncle Gizmo]

Would this be of any use to you? I'm happy to let you have a free copy!

Show Hide, Export to Excel

 

[hllary]

Using doCmd.transferSpreadsheet is a little over my head. Can you please give me a little more info?

 

[hllary]

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.

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

Thanks for everyone's help!

 

[Gasman]

Instead of creating and deleting queries all the time, why not just amend the sql for that querydef?

 

[GinaWhipp]

Instead of creating and deleting queries all the time, why not just amend the sql for that querydef?

Gasman,

When exporting to Excel a query with parameters will error out unless saved first.

 

[Gasman]

Gasman,

When exporting to Excel a query with parameters will error out unless saved first.

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

 

[GinaWhipp]

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

Correct. If you are just running it with no parameters you are good, no Save needed. It's the parameters that change the *rules*.

 

[vhung]

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.

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

 

[GinaWhipp]

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

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.

 

[vhung]

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.

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

 

[GinaWhipp]

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

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.