MkDir and add file

[w0od0o]

Hi all,

i am looking to create a directory named from a field in my table and then add a report to that directory, i can create a directory but not with the name from the field and i can also export the report but not to the directory i created

heres what i have so far

Private Sub create_quote_Click()
'create quote number
Me.quote_number = DMax("[quote_number]", "quote") + 1
MsgBox "Quote Number Order Generated"
'Export pdf
If IsNull(Me!quote_number) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
MkDir "F:\test\[quote_number]"
cdir = MsgBox("Creating Quote Directory", vbOKOnly, "Create Directory")
DoCmd.OpenReport "quote", acViewPreview, , _
"quote_number = " & Me!quote_number
Save = MsgBox("Saving report to file", vbOKOnly, "Save file as")
DoCmd.OutputTo acOutputReport, "quote", "PDF Format (*.pdf)", "F:\test\" & quote_number & ".pdf", False
End Sub

p.s. im new to vba so please bare with me

many thanks

w0od0o

 

[pbaldy]

This type of thing?

MkDir "F:\test\" & Me.quote_number

 

[w0od0o]

This type of thing?

MkDir "F:\test\" & Me.quote_number

yes i think so, would this create a folder:

F:\test\1234 <----sample

but is it possible to save a report in that folder?

as i can only get it to save in a folder that already exists

thanks

w0od0o

 

[GaryPanic]

heres the mkdir code
QuoteNo is a field on my form ..

On Error GoTo Err_cmbMkDir_Click

Dim DirName As String
Dim Response As String

DirName = "C:\Project X\" & Left(Me!QuoteNo, 10)


If Dir(DirName, vbDirectory) = "" Then
If MsgBox("OK to create folder!", vbOKCancel) = vbOK Then
MkDir DirName
Else
MsgBox "Create folder cancelled. Folder not created."
Exit Sub
End If
Else
MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
Exit Sub
End If

Response = MsgBox(DirName, vbOKOnly)



Exit_cmbMkDir_Click:
Exit Sub

Err_cmbMkDir_Click:
MsgBox Err.Description
Resume Exit_cmbMkDir_Click

 

[pbaldy]

Sure; just do the same type of thing in your output code.

 

[w0od0o]

Gary, the code works great to add the directory thanks, but im still struggling to save my report in that directory as it isnt created and i would like each report to go in there own folder

w0od0o

 

[pbaldy]

Doesn't this work, after creating the directory?

DoCmd.OutputTo acOutputReport, "quote", "PDF Format (*.pdf)", "F:\test\" & Me.quote_number & "\" & quote_number & ".pdf", False

 

[GaryPanic]

Try Pauls solution -

Pauls knowledge is far in excess of mine - i just hack my way through - grabbing what I need.

Can you get the file to save on a click of a button in the right directory ??

if so -its a case of cut and paste the code -( I would put a pause in the middle just to give the network time to make a dir)

 

[w0od0o]

yes!!!! you two are smokey

ive added pauls line to a new button and works fine, when u say add a pause, whats the code for that?

Thanks both, very appreciated.

w0od0o

 

[pbaldy]

No problem, glad it worked for you.

i just hack my way through - grabbing what I need.

Don't tell anybody Gary, but I do too.

 

[w0od0o]

ive added a MsgBox to ask to save the file? this should allow the time to create the folder?

 

[pbaldy]

Looks like Gary's gone offline. I would only add a pause if you're getting errors without. If you are and think timing is the issue, rather than a message box I'd try adding DoEvents. The message box would probably work, but requires user intervention, which I assume you don't otherwise need.

 

[GaryPanic]

I'm back - ...
My think behind a 2-3 second pause was purely a timing issue - if your server/network is ok then no need for it -


(thanks Paul - I seen your posts - your right up there with the best of them - I hardly understand what i am doing - i just know it can be done - and thrash around until it works )

 

[GaryPanic]

I have make Dir on my project - but i don't have a file to save - rather various docs that I need to store in the directory relative to the quoteno .. and i use one of the function to display the records in the directory ...

 

[Rx_]

Nothing really new here.
In my situation, each users My Documents are really a networked path so the IT group can back them up each night.
This code (as shown above) will check for a sub directory, ask the user to create one (if absent). Not shown - code to create a lengthly Excel report from the Database. B.T.W. the Excel is not visable, it straight away just saves in a directory folder for the user to go open.
Hope this helps:

            ' Mapped Network path for My Documents (not on C:\ drive) with user login then a subdirectory for "StandardReport"
10          UserLogin = Environ("username")
15          UserPath = "[URL="file://\\ANetworkPath\"]\\ANetworkPath\[/URL]" & UserLogin & "$\StandardReport"
20          strNewReportPath = UserPath
50          DirName = strNewReportPath
60              If Dir(DirName, vbDirectory) = "" Then
62                    If MsgBox("Is it OK to create a new folder in My Documents? (recommended yes)", vbOKCancel) = vbOK Then
65                        MkDir DirName
67                Else
69                    MsgBox "Create new folder cancelled. Folder not created.", vbOKOnly, "Report Cancelled, must allow folder to be created"
70                        Exit Function
72                End If
75              Else
77                  'MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
                    ' if it exist, don't bother letting the user know
79              End If      '
80      If Err.Number <> 0 Then
82          MsgBox "Network path problem needs to be resolved " & Err.Description, vbOKOnly, "Network path to My Documents not found"
85          Err.Raise 3580, "ExcelReports", "Code module"
90      End If
 
' then loads of code to read Access recordset and create a custom Excel report using VBA
 
' Ready to save in new folder - date time stamp - name of report format
2290        strSaveAsFileName = strNewReportPath & "\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & " Standard Report.xlsx"
2300        objXL.ActiveWorkbook.SaveAs FileName:=strSaveAsFileName
2320        objXL.Visible = False ' except for development / troubleshooting
            objXL.Application.Quit
2325        msgString = "My Documents\StandardReports\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & " Standard Report.xlsx"
 
2330        MsgBox "Excel report saved at : " & msgString, vbOKOnly, "Please Open This File Location for Your Report"