microsoft.public.access

Assuming you're trying to include whatever's in txtDept on the form as part
of the file name:

"U:\Directory1\Directory2\" & [Forms]![FRpts].[txtDept] & ".xls"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Jeff C" wrote in message
news:26727463-FF33-40B3-BB19-E78FA210CB2F@microsoft.com...
> Every morning a large mainframe report is spooled to a text file. Using a
> data extraction program, I have the report dropped into an Excel
> Spreadsheet.
> Each morning it overwrites itself with a new report. I link to this
> spreadsheet from Access. I built a macro that I will schedule to run
> every
> morning after the report drops. The macro will open a form, close the
> form,
> and quit the application.
>
> I have a query built:
>
> SELECT DISTINCT AncChgRpt.Dept
> FROM AncChgRpt;
>
> The On Open Event of the form:
> Dim Dept As ADODB.Recordset
> Set Dept = New ADODB.Recordset
>
> Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic
>
> Do Until Dept.EOF
>
> [Forms]![FRpts]![txtDept].Value = QDepts![Dept]
>
> On Error Resume Next
> DoCmd.OutputTo acQuery, "QReport", "MicrosoftExcelBiff8(*.xls)",
> "U:\Directory1\Directory2\[Forms]![FRpts].[txtDept].xls", False, "", 0
> On Error Resume Next
> Dept.MoveNext
> Loop
> End Sub
>
> I am attempting to get a separate spreadsheet for each department, named
> with the name of each department but I cannot come up with the correct way
> to
> pull the name of the department from the textbox on the form and use it as
> a
> text string as the name of the spreadsheet.
>
> Once I get this I have a VBS routine that will copy the sheet in each
> workbook adding it as a new sheet in the departments workbook. Then I
> delete
> everything so the sequence runs the next morning.
>
> Can someone help me with this?
>
> Thanks in advance.
>
>
>
>
> --
> Jeff C
> Live Well .. Be Happy In All You Do