Exporting with specified file name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

colin - 31 Jan 2007 13:40 GMT

Hi,

I hope that someone can help?

i have a report that generates a billing run for multiple customers and i
want to save each invoice in the report to a location with the filename being
the customer account number and invoice number.

I have set up a form that is used to generate individual criteria and I want
to use the following to generate the filename when I output the report within
a macro:

strFileName = "C:\test\ & Forms![ControlMinimum]![MinOfAccNo] & "_"&
Forms![ControlMinimum]![InvoiceNumber] )

I get the following error MsAccess cant save the output data to the file you
have selected. The file format is HTML

I would be grateful if someone could point me in the right direction.

Many thanks

Colin

Reply to this Message

Klatuu - 31 Jan 2007 13:49 GMT

Just a little syntax problem.
strFileName = "C:\test\ & Forms![ControlMinimum]![MinOfAccNo] & "_"&
Forms![ControlMinimum]![InvoiceNumber] )

The reference to the form controls needs to be outside the quotes:
strFileName = "C:\test\" & Forms![ControlMinimum]![MinOfAccNo] & "_" &
Forms![ControlMinimum]![InvoiceNumber]

Signature

Dave Hargis, Microsoft Access MVP

> Hi,
>
[quoted text clipped - 19 lines]
>
> Colin

Reply to this Message

colin - 31 Jan 2007 14:04 GMT

Hi Dave,

I was hoping you were online as your previous posts gave me the idea.
could you check your syntax as I presume you need an opening ( somewhere ?

Many thanks for your help

Cheers Colin

> Just a little syntax problem.
> strFileName = "C:\test\ & Forms![ControlMinimum]![MinOfAccNo] & "_"&
[quoted text clipped - 27 lines]
> >
> > Colin

Reply to this Message

Klatuu - 31 Jan 2007 14:10 GMT

There is no requirement for an opening paren in the code I posted. I noticed
you had a closing paren in the original post with no opening paren. I would
have to see the entire statement to know where it should be.

Signature

Dave Hargis, Microsoft Access MVP

> Hi Dave,
>
[quoted text clipped - 36 lines]
> > >
> > > Colin

Reply to this Message

colin - 31 Jan 2007 14:20 GMT

The converted Macro to Module code (Now) is as follows:

'------------------------------------------------------------
' Output
'
'------------------------------------------------------------
Function Output()
On Error GoTo Output_Err

' strFileName = "C:\test\" & Forms![ControlMinimum]![MinOfAccNo] & "_" &
Forms![ControlMimimum]![invoiceNumber]
DoCmd.OutputTo acReport, "V2MasterBillReport", "HTML(*.html)",
"strFileName = ""C:\test\"" & Forms![ControlMinimum]![MinOfAccNo] & ""_"" &
Forms![ControlMimimum]![invoiceNumber]", False, ""

Output_Exit:
Exit Function

Output_Err:
MsgBox Error$
Resume Output_Exit

End Function

Thanks Colin

> There is no requirement for an opening paren in the code I posted. I noticed
> you had a closing paren in the original post with no opening paren. I would
[quoted text clipped - 40 lines]
> > > >
> > > > Colin

Reply to this Message

Klatuu - 31 Jan 2007 14:34 GMT

Since I don't use macros, I don't know why it is putting out code like that.
It does not appear to be syntactically correct. What error are you getting?

Signature

Dave Hargis, Microsoft Access MVP

> The converted Macro to Module code (Now) is as follows:
>
[quoted text clipped - 66 lines]
> > > > >
> > > > > Colin

Reply to this Message

colin - 31 Jan 2007 17:42 GMT

Hi Dave,

I have found that html does not retaine the report format and so have
decided to use snapshot (until I get Office 2007 which I believe supports PDF)

would you have any example code that will say output report "A" to location
C:\test\Filename(formcontrol "a") & space (formcontrol "b")

I dont know if this is possible as there is an issue with msaccess 2000 in
using wildcards see article
http://support.microsoft.com/default.aspx?scid=kb;en-us;226526

Many thanks for your time and assistance

kind regards

Colin

> Since I don't use macros, I don't know why it is putting out code like that.
> It does not appear to be syntactically correct. What error are you getting?
[quoted text clipped - 69 lines]
> > > > > >
> > > > > > Colin

Reply to this Message

Klatuu - 31 Jan 2007 17:55 GMT

Try it like this in VBA:
DoCmd.OutputTo acReport, "V2MasterBillReport", acFormatHTML, "C:\test\'"
& Forms![ControlMinimum]![MinOfAccNo] & "'_'" &
Forms![ControlMimimum]![invoiceNumber]", False

Signature

Dave Hargis, Microsoft Access MVP

> Hi Dave,
>
[quoted text clipped - 87 lines]
> > > > > > >
> > > > > > > Colin

Reply to this Message