I am not versed in VBA.

Right now I have a macro that saves 1 worksheet as a new workbook and saves
as a value in cell B5. Everytime I change the value of B5 and run the macro
I get a new workbook.

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the folder
with the workbooks that were saved as the previous values in B5 Then give me
a warning that the value in B5 has been used so that I do not accidently save
over one of the saved workbooks.

Hi Ryan,

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the folder
with the workbooks that were saved as the previous values in B5 Then give
me
a warning that the value in B5 has been used so that I do not accidently
save
over one of the saved workbooks.

This is normal behaviour.

Unless alert messages have been disabled by means of an

Application.DisplayAlerts = False

instruction, any attempt to save to an existing file name will engender a
confirmatory warning.

If you want to check pre-emptively, then try something like:

'=========================
Public Sub Tester()
Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

If Dir(sStr & sh.Range("B5").Value) < "" Then
MsgBox "file exists"
' A workbook already exists with this name
' so do something
Else
'Workbook does not exist, OK to save!
MsgBox "Workbook does not exist"
End If

End Sub

'<<=========================


---
Regards,
Norman



"ryanmhess" wrote in message
...
I am not versed in VBA.

Right now I have a macro that saves 1 worksheet as a new workbook and
saves
as a value in cell B5. Everytime I change the value of B5 and run the
macro
I get a new workbook.

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the folder
with the workbooks that were saved as the previous values in B5 Then give
me
a warning that the value in B5 has been used so that I do not accidently
save
over one of the saved workbooks.

Norman,

I embedded the code you gave me into my macro -

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\\Hci\HCI Share\C of A's -
Building 4\00-RMHTest-6 ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists" 'A workbook already exists with this name
Else
MsgBox "File Does Not Exist" 'A workbook does not exists with this
name
End If
---------------------

When I engage my macro I get the MsgBox "File Does Not Exist" everytime I
use the same value for B7. I'm not sure what to do so that it recognizes
that the workbook of that name already exists. Also Is it possible when I
get this to work and it does recognize the file already exists to skip to the
end of the macro so I dont save over or have to [end]/[debug] the macro?


"Norman Jones" wrote:

Hi Ryan,

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the folder
with the workbooks that were saved as the previous values in B5 Then give
me
a warning that the value in B5 has been used so that I do not accidently
save
over one of the saved workbooks.

This is normal behaviour.

Unless alert messages have been disabled by means of an

Application.DisplayAlerts = False

instruction, any attempt to save to an existing file name will engender a
confirmatory warning.

If you want to check pre-emptively, then try something like:

'=========================
Public Sub Tester()
Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

If Dir(sStr & sh.Range("B5").Value) < "" Then
MsgBox "file exists"
' A workbook already exists with this name
' so do something
Else
'Workbook does not exist, OK to save!
MsgBox "Workbook does not exist"
End If

End Sub

'<<=========================


---
Regards,
Norman



"ryanmhess" wrote in message
...
I am not versed in VBA.

Right now I have a macro that saves 1 worksheet as a new workbook and
saves
as a value in cell B5. Everytime I change the value of B5 and run the
macro
I get a new workbook.

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the folder
with the workbooks that were saved as the previous values in B5 Then give
me
a warning that the value in B5 has been used so that I do not accidently
save
over one of the saved workbooks.

Hi Ryan,

In my demo code,

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

Application.DefaultFilePath & "\"

and

"YourFolderPath" & "\"

were intended as alternatives.


If you are providing the full path, then try using:

sStr = "\\Hci\HCI Share\C of A's Building 4\00-RMHTest-6 ParametersXXXX\"


---
Regards,
Norman



"ryanmhess" wrote in message
...
Norman,

I embedded the code you gave me into my macro -

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\\Hci\HCI Share\C of A's -
Building 4\00-RMHTest-6 ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists" 'A workbook already exists with this
name
Else
MsgBox "File Does Not Exist" 'A workbook does not exists with this
name
End If
---------------------

When I engage my macro I get the MsgBox "File Does Not Exist" everytime I
use the same value for B7. I'm not sure what to do so that it recognizes
that the workbook of that name already exists. Also Is it possible when I
get this to work and it does recognize the file already exists to skip to
the
end of the macro so I dont save over or have to [end]/[debug] the macro?


"Norman Jones" wrote:

Hi Ryan,

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the
folder
with the workbooks that were saved as the previous values in B5 Then
give
me
a warning that the value in B5 has been used so that I do not
accidently
save
over one of the saved workbooks.

This is normal behaviour.

Unless alert messages have been disabled by means of an

Application.DisplayAlerts = False

instruction, any attempt to save to an existing file name will engender a
confirmatory warning.

If you want to check pre-emptively, then try something like:

'=========================
Public Sub Tester()
Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

If Dir(sStr & sh.Range("B5").Value) < "" Then
MsgBox "file exists"
' A workbook already exists with this name
' so do something
Else
'Workbook does not exist, OK to save!
MsgBox "Workbook does not exist"
End If

End Sub

'<<=========================


---
Regards,
Norman



"ryanmhess" wrote in message
...
I am not versed in VBA.

Right now I have a macro that saves 1 worksheet as a new workbook and
saves
as a value in cell B5. Everytime I change the value of B5 and run the
macro
I get a new workbook.

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the
folder
with the workbooks that were saved as the previous values in B5 Then
give
me
a warning that the value in B5 has been used so that I do not
accidently
save
over one of the saved workbooks.

Hello Norman,

I changed the code below since your last reply.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

Unfortunately it still does not recognize that there is already a file saved
as "B7" when I use the same value for "B7" more than once and nomatter what
value "B7" is it tell me [File Does Not Exist].

Where you have < in the code I changed it to = and with that change
it tells me [File Exists] nomatter what value is in "B7".

"Norman Jones" wrote:

Hi Ryan,

In my demo code,

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

Application.DefaultFilePath & "\"

and

"YourFolderPath" & "\"

were intended as alternatives.


If you are providing the full path, then try using:

sStr = "\\Hci\HCI Share\C of A's Building 4\00-RMHTest-6 ParametersXXXX\"


---
Regards,
Norman



"ryanmhess" wrote in message
...
Norman,

I embedded the code you gave me into my macro -

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\\Hci\HCI Share\C of A's -
Building 4\00-RMHTest-6 ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists" 'A workbook already exists with this
name
Else
MsgBox "File Does Not Exist" 'A workbook does not exists with this
name
End If
---------------------

When I engage my macro I get the MsgBox "File Does Not Exist" everytime I
use the same value for B7. I'm not sure what to do so that it recognizes
that the workbook of that name already exists. Also Is it possible when I
get this to work and it does recognize the file already exists to skip to
the
end of the macro so I dont save over or have to [end]/[debug] the macro?


"Norman Jones" wrote:

Hi Ryan,

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the
folder
with the workbooks that were saved as the previous values in B5 Then
give
me
a warning that the value in B5 has been used so that I do not
accidently
save
over one of the saved workbooks.

This is normal behaviour.

Unless alert messages have been disabled by means of an

Application.DisplayAlerts = False

instruction, any attempt to save to an existing file name will engender a
confirmatory warning.

If you want to check pre-emptively, then try something like:

'=========================
Public Sub Tester()
Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

If Dir(sStr & sh.Range("B5").Value) < "" Then
MsgBox "file exists"
' A workbook already exists with this name
' so do something
Else
'Workbook does not exist, OK to save!
MsgBox "Workbook does not exist"
End If

End Sub

'<<=========================


---
Regards,
Norman



"ryanmhess" wrote in message
...
I am not versed in VBA.

Right now I have a macro that saves 1 worksheet as a new workbook and
saves
as a value in cell B5. Everytime I change the value of B5 and run the
macro
I get a new workbook.

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the
folder
with the workbooks that were saved as the previous values in B5 Then
give
me
a warning that the value in B5 has been used so that I do not
accidently
save
over one of the saved workbooks.

Hi Ryan,

Unfortunately it still does not recognize that there is already a file
saved
as "B7" when I use the same value for "B7" more than once and nomatter
what
value "B7" is it tell me [File Does Not Exist].


Try opening the pre-existing file and then run the following simplr macro:

Public Sub Tester()
With ActiveWorkbook
Debug.Print .Path
Debug.Print .Name
End With
End Sub

Now, in the original code, ensure that the path value returned in the
intermediate window is assigned to the sStr variable and append a final
slash (\).

Also ensure that the filename in B7 includes the .xls extension.


---
Regards,
Norman



"ryanmhess" wrote in message
...
Hello Norman,

I changed the code below since your last reply.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

Unfortunately it still does not recognize that there is already a file
saved
as "B7" when I use the same value for "B7" more than once and nomatter
what
value "B7" is it tell me [File Does Not Exist].

Where you have < in the code I changed it to = and with that change
it tells me [File Exists] nomatter what value is in "B7".

"Norman Jones" wrote:

Hi Ryan,

In my demo code,

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

Application.DefaultFilePath & "\"

and

"YourFolderPath" & "\"

were intended as alternatives.


If you are providing the full path, then try using:

sStr = "\\Hci\HCI Share\C of A's Building 4\00-RMHTest-6 ParametersXXXX\"


---
Regards,
Norman



"ryanmhess" wrote in message
...
Norman,

I embedded the code you gave me into my macro -

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\\Hci\HCI Share\C of A's -
Building 4\00-RMHTest-6 ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists" 'A workbook already exists with
this
name
Else
MsgBox "File Does Not Exist" 'A workbook does not exists with
this
name
End If
---------------------

When I engage my macro I get the MsgBox "File Does Not Exist"
everytime I
use the same value for B7. I'm not sure what to do so that it
recognizes
that the workbook of that name already exists. Also Is it possible
when I
get this to work and it does recognize the file already exists to skip
to
the
end of the macro so I dont save over or have to [end]/[debug] the
macro?


"Norman Jones" wrote:

Hi Ryan,

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the
folder
with the workbooks that were saved as the previous values in B5 Then
give
me
a warning that the value in B5 has been used so that I do not
accidently
save
over one of the saved workbooks.

This is normal behaviour.

Unless alert messages have been disabled by means of an

Application.DisplayAlerts = False

instruction, any attempt to save to an existing file name will
engender a
confirmatory warning.

If you want to check pre-emptively, then try something like:

'=========================
Public Sub Tester()
Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = Application.DefaultFilePath & "\" '"YourFolderPath" & "\"

If Dir(sStr & sh.Range("B5").Value) < "" Then
MsgBox "file exists"
' A workbook already exists with this name
' so do something
Else
'Workbook does not exist, OK to save!
MsgBox "Workbook does not exist"
End If

End Sub

'<<=========================


---
Regards,
Norman



"ryanmhess" wrote in message
...
I am not versed in VBA.

Right now I have a macro that saves 1 worksheet as a new workbook
and
saves
as a value in cell B5. Everytime I change the value of B5 and run
the
macro
I get a new workbook.

What I want is a macro that asks...

If the value in cell B5 is the same as any of the file names in the
folder
with the workbooks that were saved as the previous values in B5 Then
give
me
a warning that the value in B5 has been used so that I do not
accidently
save
over one of the saved workbooks.