microsoft.public.access

If you're opening a recordset, why bother to write the value of the Dept
field to the form just so that you can then use it in the
TransferSpreadsheet action? Do it directly:


DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & QDepts![Dept] & ".xls"



Now, define "hanging here" for us -- are you getting an error message from
VBE? is nothing happening? more details, please.
--

Ken Snell





"Jeff C" wrote in message
news:80B1456A-7163-47F6-9DCA-319F16F4F713@microsoft.com...
> Thanks guys
>
> I now have :
>
> Dim Dept As ADODB.Recordset
> Set Dept = New ADODB.Recordset
>
> Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic
>
> Do Until Dept.EOF
>
>
> Me.txtDept.Value = QDepts![Dept]
>
> On Error Resume Next
> DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
> "U:\DocDirectReports\PACPAJ01\" & [Forms]![FRpts].[txtDept] & ".xls"
> On Error Resume Next
> Dept.MoveNext
> Loop
> End Sub
>
>
> I am hanging here now: Me.txtDept.Value = QDepts![Dept]
>
> --
> Jeff C
> Live Well .. Be Happy In All You Do
>
>
>