How can I increment filename?

[Sevn]

I am only a beginner...

I have the following code running on a report, and am wondering how I can auto increment the exports (Example: : 001, 002, etc.). Any help is appreciated.

Private Sub Report_GotFocus()
If Forms!SelectReportType!Combo6 = "Normal" Then
MkDir "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Normal\" & "\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
Else
MkDir "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Ad Hoc\" & "\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
End If

If Forms!SelectReportType!Combo6 = "Normal" Then
DoCmd.OutputTo acOutputReport, "OTDComparisonsByOrder", acFormatRTF, "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Normal\" & "\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "\OSM-" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".rtf", False
Else
DoCmd.OutputTo acOutputReport, "OTDComparisonsByOrder", acFormatRTF, "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\AdHoc\" & "\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "\OSM-" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".rtf", False
End If
End Sub

 

[gemma-the-husky]

get a filename say

filename = mypath & "\" myfile & "_1" & ".txt

then

dim exists as boolean
exists = dir(filename)<>vbnullstring

if it exists then, add one to the sequence, and try again.
keep trying unitl you get an unused number.

 

[Sevn]

Thanks, but I don't quite understand what you are saying. Can you explain a little further? I'm kind of a newby, and need a little more info.

 

[gemma-the-husky]

like this

sub savefile

dim filename as string
dim try as long

try=1
another:
filename = "c:\test_" & try & ".txt"
if dir(filename)<>vbnullstring then 'the fiel exists! try another
try=try+1
goto another

msgbox("unused filename is " & filename)

end sub

 

[Sevn]

I have attempted to apply your logic, and don't think I'm doing it right. Let me explain what my code was doing, and see if we are on the same page.

Please keep in mind that I am a beginner.

Private Sub Report_GotFocus()
'Takes value from Combo6 to determine which folder to create new dated folder. Normal or Ad Hoc
If Forms!SelectReportType!Combo6 = "Normal" Then
MkDir "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Normal\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
Else
MkDir "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Ad Hoc\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
End If

Dim filename As String
Dim try As Long
try = 1
another:
'I replace your "c:\test_" & try & ".txt" with my export path.
filename = "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Normal\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "\OSM-" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".rtf"
If Dir(filename) <> vbNullString Then 'the fiel exists! try another
try = try + 1
GoTo another
MsgBox ("unused filename is " & filename)
End If
End Sub

 

[Sevn]

I have tried this code with & without modifications, and it isn't working.

Just to be clear. I am trying to do the following:
I have a switchboard item that queues a report.
On the report's "On Focus" event, I want to export the report.
I want the exports to go into specific folders based on Combo Box criteria (Normal/Ad Hoc).
The folders need to be created dynamically, based on current date (20080617)
Finally; if there existing files, I need the export to increment by 1 to prevent overwriting the other reports.

This senerio will only occur for Ad Hoc reports.

The code below gets the files to export to the proper folders, but I need to have the files increment by one. Can someone please provide a clear explanation how to do this. The previous post was somewhat helpful, but I can't it to work for me.

Private Sub Report_GotFocus()
If Forms!SelectReportType!Combo6 = "Normal" Then
MkDir "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Normal\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
Else
MkDir "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exports\Ad Hoc\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
End If

If Forms!SelectReportType!Combo6 = "Normal" Then
DoCmd.OutputTo acOutputReport, "OTDComparisonsByOrder", acFormatRTF, "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exp orts\Normal\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "\OSM-" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".rtf", False
Else
DoCmd.OutputTo acOutputReport, "OTDComparisonsByOrder", acFormatRTF, "C:\Documents and Settings\youngje\Desktop\Orders_Shipped_Metric\Exp orts\AdHoc\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "\OSM-" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".rtf", False
End If
End Sub