I've seen that transferspreadsheet requires a table name be pre-inserted,and
that the Export command uses the table name as the worksheet's name when
exported to an existing worksheet. Is there a way to make it into a
parameter so the user can enter it, without coding the whole thing?
If not, whats the standard messagebox in access's version of VB?
Any other suggestions?
I don't know how to do it without coding. The gist of coding it would
be: create a query on the fly that is named for a variable that a
user key's into a form (SELECT * from your table) - the query is
opened and used for TransferSpreadSheet.
Sorry I'm not more help.
Bonnie
http://www.dataplus-svc.com
EXAMPLE OF QUERY IN CODE:
Sub CreateQueryDefX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim qdfNew As QueryDef
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
With dbsNorthwind
' Create temporary QueryDef.
Set qdfTemp = .CreateQueryDef("", _
"SELECT * FROM Employees")
' Open Recordset and print report.
GetrstTemp qdfTemp
' Create permanent QueryDef.
Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories")
' Open Recordset and print report.
GetrstTemp qdfNew
' Delete new QueryDef because this is a demonstration.
.QueryDefs.Delete qdfNew.Name
.Close
End With
End Sub
Function GetrstTemp(qdfTemp As QueryDef)
Dim rstTemp As Recordset
With qdfTemp
Debug.Print .Name
Debug.Print " " & .SQL
' Open Recordset from QueryDef.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)
With rstTemp
' Populate Recordset and print number of records.
.MoveLast
Debug.Print " Number of records = " & _
.RecordCount
Debug.Print
.Close
End With
End With
End Function
