Can you automate "save as"? (so that it behaves like "save")

[SirStevie3]

question in title.

can you use some code to bypass the save as prompt, and just save the file?

i have a command button thats saving 8 reports as RTF. but when i run it, the save as prompt comes up for each report and i have to hit enter for each one. i would rather it just save automatically as i dont need to change the names of the reports or where they are being saved.

Thanks!!

 

[pr2-eugin]

How are you automating this process? Normally if you use DoCmd.OutputTo with a path name it will not bring up the Save As dialog..

 

[SirStevie3]

im not using a path name. i'm learning all this by google and trial and error. here's what im using now:

Private Sub Command50_Click()
On Error GoTo ProcError

DoCmd.OutputTo acOutputReport, "Delivery Builder - DWG", acFormatRTF
DoCmd.OutputTo acOutputReport, "Delivery Builder - ECN", acFormatRTF
DoCmd.OutputTo acOutputReport, "Delivery Details", acFormatRTF
DoCmd.OutputTo acOutputReport, "Delivery Details", acFormatXLS
DoCmd.OutputTo acOutputReport, "Delivery Statistics", acFormatRTF
DoCmd.OutputTo acOutputReport, "Delivery List - DWG", acFormatRTF
DoCmd.OutputTo acOutputReport, "Delivery List - ECN", acFormatRTF
DoCmd.OutputTo acOutputReport, "Provisioning Statistics (Career)", acFormatRTF
DoCmd.OutputTo acOutputReport, "Provisioning Statistics (Delivery)", acFormatRTF

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdGenerateReports_Click..."
Resume ExitProc
End Sub

its basically a copy paste from two different examples i found on the web that i meshed together and modified slightly.

 

[pr2-eugin]

Okay, what I emant was, if you have the FilePath argument of the DoCmd.OutputTo method, it will not prompt for the location..

Private Sub Command50_Click()
On Error GoTo ProcError
    DoCmd.OutputTo acOutputReport, "Delivery Builder - DWG", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Delivery Builder - DWG.rtf" [/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Delivery Builder - ECN", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Delivery Builder - ECN.rtf"[/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Delivery Details", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Delivery Details.rtf"[/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Delivery Details", acFormatXLS, [B][COLOR=Blue][COLOR=Red]Environ("userprofile") & "\Documents\[/COLOR]Delivery Details.xlsx"[/COLOR][/B]
    DoCmd.OutputTo acOutputReport, "Delivery Statistics", acFormatRTF,[COLOR=Red] [B]Environ("userprofile") & "\Documents\[COLOR=Blue]Delivery Statistics.rtf"[/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Delivery List - DWG", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Delivery List - DWG.rtf"[/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Delivery List - ECN", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Delivery List - ECN.rtf"[/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Provisioning Statistics (Career)", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Provisioning Statistics (Career).rtf"[/COLOR][/B][/COLOR]
    DoCmd.OutputTo acOutputReport, "Provisioning Statistics (Delivery)", acFormatRTF, [COLOR=Red][B]Environ("userprofile") & "\Documents\[COLOR=Blue]Provisioning Statistics (Delivery).rtf"[/COLOR][/B][/COLOR]

ExitProc:
    Exit Sub
ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
            vbCritical, "Error in procedure cmdGenerateReports_Click..."
    Resume ExitProc
End Sub

The above code will just go into My Documents. You can change the code as you wish to.

 

[SirStevie3]

I see what you mean now!!

I thought you were asking, not suggesting! My bad. Thanks for the great advice!!

 

[pr2-eugin]

That's alright. Glad to have helped. Good luck !

 

[SirStevie3]

sorry to bug you again, but im having a hard time with this (im not so good with VBA yet...)

Environ("userprofile") & "\Documents\

can you explain the elements here and how it works? is environ something you used just for an example or is that a legit part of the code?

 

[David R]

Environ() is a built in function that accesses several Windows variables...BUT it can sometimes fail to run if your Access is set up to run in Sandboxed mode for security reasons. There are workarounds, but they're not elegant.

You know how to find the VBA Immediate window? Try typing

? Environ("userprofile") & "\Documents\

and hit Enter. If that gives you garbage, you may have to go with the API version. http://www.utteraccess.com/wiki/index.php/Environ()_Function

 

[pr2-eugin]

Environ is a function associated with the Operating system, that holds most information regarding the environment variables, like computer name, user name, profile, path etc.

So you can use that to get the path of the User profile for Windows Vista & higher the path would be normally C:\Users\<<user name>>\Documents\ the Environ("userprofile") will return that info..

? Environ("userprofile")
C:\Users\pef

You can use anything you wish.. "D:\someFolder\newFolder\FileName.xlsx". The one I gave is example, as you normally hit enter. Thought you wanted the file to be saved at the Documents I gave that option.

 

[SirStevie3]

Cool!! works as expected now, thanks so much guys! much appreciated!