Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Save file with specific name
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Save file with specific name
I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Save file with specific name
If you have A0001.xls through A0024.xls and someone deletes a0018.xls, then this
will use a0018.xls--the first available number:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 1 To 9999
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
FoundOne = True
Exit For
Else
'keep looking
End If

Next iCtr

If FoundOne = False Then
MsgBox "No available files!"
Else
On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0
End If

End Sub

Dr. Schwartz wrote:

I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Save file with specific name
Thank you Dave it works nicely, however I was not good explaing that I always
need the next (high) number. So in your example below A0018 should be ignored
and it should be named A0025.xls instead. Can you add this?

Thanks a lot
The doctor

"Dave Peterson" wrote:

If you have A0001.xls through A0024.xls and someone deletes a0018.xls, then this
will use a0018.xls--the first available number:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 1 To 9999
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
FoundOne = True
Exit For
Else
'keep looking
End If

Next iCtr

If FoundOne = False Then
MsgBox "No available files!"
Else
On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0
End If

End Sub

Dr. Schwartz wrote:

I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Save file with specific name
How about:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
myPath = "C:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 9999 To 1 Step -1
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
'keep looking
Else
If iCtr = 9999 Then
MsgBox "No available numbers!"
Exit For
Else
FoundOne = True
myFileName = myPath & "A" & Format(iCtr + 1, "0000") & ".xls"
Exit For
End If
End If

Next iCtr

If FoundOne = False Then
myFileName = myPath & "A0001.xls"
End If

On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0

End Sub

Dr. Schwartz wrote:

Thank you Dave it works nicely, however I was not good explaing that I always
need the next (high) number. So in your example below A0018 should be ignored
and it should be named A0025.xls instead. Can you add this?

Thanks a lot
The doctor

"Dave Peterson" wrote:

If you have A0001.xls through A0024.xls and someone deletes a0018.xls, then this
will use a0018.xls--the first available number:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 1 To 9999
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
FoundOne = True
Exit For
Else
'keep looking
End If

Next iCtr

If FoundOne = False Then
MsgBox "No available files!"
Else
On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0
End If

End Sub

Dr. Schwartz wrote:

I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor

--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 30
Default Save file with specific name
Good idea - unfortunatly I'm to impatient to wait for the loop to run ~9999
times.

Is this the only way to solve this by looping through the file names?

"Dave Peterson" wrote:

How about:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
myPath = "C:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 9999 To 1 Step -1
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
'keep looking
Else
If iCtr = 9999 Then
MsgBox "No available numbers!"
Exit For
Else
FoundOne = True
myFileName = myPath & "A" & Format(iCtr + 1, "0000") & ".xls"
Exit For
End If
End If

Next iCtr

If FoundOne = False Then
myFileName = myPath & "A0001.xls"
End If

On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0

End Sub

Dr. Schwartz wrote:

Thank you Dave it works nicely, however I was not good explaing that I always
need the next (high) number. So in your example below A0018 should be ignored
and it should be named A0025.xls instead. Can you add this?

Thanks a lot
The doctor

"Dave Peterson" wrote:

If you have A0001.xls through A0024.xls and someone deletes a0018.xls, then this
will use a0018.xls--the first available number:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 1 To 9999
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
FoundOne = True
Exit For
Else
'keep looking
End If

Next iCtr

If FoundOne = False Then
MsgBox "No available files!"
Else
On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0
End If

End Sub

Dr. Schwartz wrote:

I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor

--

Dave Peterson


--

Dave Peterson

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Save file with specific name
How long did it take to do that loop?

I guess you could loop through the directory and look for names that match
A####.xls. Then find the maximum number used and add one to it.

Dr. Schwartz wrote:

Good idea - unfortunatly I'm to impatient to wait for the loop to run ~9999
times.

Is this the only way to solve this by looping through the file names?

"Dave Peterson" wrote:

How about:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
myPath = "C:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 9999 To 1 Step -1
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
'keep looking
Else
If iCtr = 9999 Then
MsgBox "No available numbers!"
Exit For
Else
FoundOne = True
myFileName = myPath & "A" & Format(iCtr + 1, "0000") & ".xls"
Exit For
End If
End If

Next iCtr

If FoundOne = False Then
myFileName = myPath & "A0001.xls"
End If

On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0

End Sub

Dr. Schwartz wrote:

Thank you Dave it works nicely, however I was not good explaing that I always
need the next (high) number. So in your example below A0018 should be ignored
and it should be named A0025.xls instead. Can you add this?

Thanks a lot
The doctor

"Dave Peterson" wrote:

If you have A0001.xls through A0024.xls and someone deletes a0018.xls, then this
will use a0018.xls--the first available number:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 1 To 9999
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
FoundOne = True
Exit For
Else
'keep looking
End If

Next iCtr

If FoundOne = False Then
MsgBox "No available files!"
Else
On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0
End If

End Sub

Dr. Schwartz wrote:

I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
SAVE and SAVE AS options disappeared from the drop down FILE menu [email protected] Excel Discussion (Misc queries) 2 July 12th 07 09:14 AM
Link to specific cell in specific Excel file JeroenM Excel Discussion (Misc queries) 3 July 6th 07 10:08 AM
when i save xls file, debug script is running and canno't save fil Imtiaz Excel Discussion (Misc queries) 1 July 16th 05 03:47 PM
Excell2003 (SP-1) File > Save and File > Save As.. grayed out Joe Murphy Excel Discussion (Misc queries) 0 March 9th 05 10:00 PM
How to save file to a specific folder? rko281 Excel Discussion (Misc queries) 1 January 25th 05 10:43 PM


All times are GMT +1. The time now is 08:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement