transferSpreadsheet export problems

[janetb99]

Anybody know why this isn't working from AccessXP?

Dim strFileName As String

strFileName = "f:\users\janetb\fhc" & StrConv(Date, "mm-dd'yy") & ".xls"
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryFHCxlsRpt", strFileName, True
DoCmd.TransferSpreadsheet acExport, 8, "qryFHCxlsRpt", strFileName, True,

 

[PHV]

this isn't working
Any error message ?
Try to replace this:
StrConv(Date, "mm-dd'yy")
By this:
StrConv(Date, "mm-dd-yy")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Shouldn't it be the format function?

[tt]format$(Date, "mm-dd-yy")[/tt]

Roy-Vidar

 

[janetb99]

Eureka! Thanks Roy-Vidar and PH Appreciate the assistance...

Just in case someone else runs across this:
Does not work:
strFileName = "f:\users\janetb\fhc" & StrConv(Date, vbShortDate) & ".xls"

Works like a charm:
strFileName = "f:\users\janetb\fhc" & Format$(Date, "mm-dd-yy") & ".xls

 

[janetb99]

Roy-Vidar,
Any ideas about this? I've tried three different ways and can't get the list from the recordset to populate the email body. Email comes through fine, but just with the sentence.

Try One:
Dim rs As DAO.Recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
Dim intI As Integer, intJ As Integer
Dim strFileName As String

strFileName = "f:\users\janetb\fhc" & Format$(Date, "mm-dd-yy") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryFHCxlsRpt", strFileName, True, ""

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
rs.MoveLast
vArray = rs.GetRows
strMsg = "Detailed summary report for the following docs:" & vbCrLf

For intI = 0 To UBound(vArray, 0)
strMsg = strMsg & vArray(intI)
Next intI


strTo = "janetb@mtn.ncahec.org"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

Try Two:
Dim db As Database
Dim rs As Recordset
Dim data As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String

strMsg = "Detailed summary report for the following docs:" & vbCrLf
Set db = OpenDatabase("f:\users\janetb\listXP.mdb")
Set rs = db.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
Set data = rs.GetRows(0)

strMsg = strMsg & data

strTo = "janetb@mtn.ncahec.org"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close
db.Close

Try Three:
Dim rs As DAO.Recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
rs.MoveLast
vArray = rs.GetRows
strMsg = "Detailed summary report for the following docs:" & vbCrLf
strMsg = strMsg & Join(vArray)

strTo = "janetb@mtn.ncahec.org"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

 

[RoyVidar]

You may wish to investigate the second dimention of the returned array, to find how many records are returned.

[tt] For intI = 0 To UBound(vArray, 2)
strMsg = strMsg & vArray(0, intI)
Next intI[/tt]

But why not just

[tt]do while not rs.eof
strmsg = strmsg & rs.fields(0).value
rs.movenext
loop[/tt]

Had you been using ADO, you could have done

[tt]strmsg = rs.getstring[/tt]

With or without column/row separators.

Roy-Vidar