If Then Else

I am trying to figure out how to set my macro to check a certain variable and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

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

I was told this code would check if the value in B7 was the same as the file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value of B7
that I would get a message "File Exists" but if there was no file with the
name as the value in B7 then it would tell me that "File Does Not Exist".

The code above no matter what value I have in B7 it tells me "File Does Not
Exist" even if i use a value in B7 that already has a file saved as the value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" <[email protected]> wrote in message
news:[email protected]...
> I am trying to figure out how to set my macro to check a certain variable
and
> if it meets a specific variable to skip the rest of the macro and end.
>
> If 'variable = X
> Then 'skip to the end of the macro and end
> Else 'continue on with the rest of the macro
>
> I also am having trouble with some code I was given earlier.
>
> 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
>
> I was told this code would check if the value in B7 was the same as the
file
> name saved in the location "\\HCI\HCI Share\C of A's - Building
> 4\00-RMHTest-6
> ParametersXXXX\" and if there was a file named the same as the value of B7
> that I would get a message "File Exists" but if there was no file with the
> name as the value in B7 then it would tell me that "File Does Not Exist".
>
> The code above no matter what value I have in B7 it tells me "File Does
Not
> Exist" even if i use a value in B7 that already has a file saved as the
value
> in the location given.
>
> Any help would be appreciated.
> Thank you
> Ryan Hess
>

That may be the problem Tom however I am not sure how to include the ".xls"
with

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

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

> could it be a problem like:
>
> ? dir("C:\data6\99budget.xls")
> 99budget.xls
> ? dir("C:\data6\99budget")
>
> The second one returns nothing because it is missing the ".xls" on the end.
>
> If x = 10 then Exit sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "ryanmhess" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to figure out how to set my macro to check a certain variable
> and
> > if it meets a specific variable to skip the rest of the macro and end.
> >
> > If 'variable = X
> > Then 'skip to the end of the macro and end
> > Else 'continue on with the rest of the macro
> >
> > I also am having trouble with some code I was given earlier.
> >
> > 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
> >
> > I was told this code would check if the value in B7 was the same as the
> file
> > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > 4\00-RMHTest-6
> > ParametersXXXX\" and if there was a file named the same as the value of B7
> > that I would get a message "File Exists" but if there was no file with the
> > name as the value in B7 then it would tell me that "File Does Not Exist".
> >
> > The code above no matter what value I have in B7 it tells me "File Does
> Not
> > Exist" even if i use a value in B7 that already has a file saved as the
> value
> > in the location given.
> >
> > Any help would be appreciated.
> > Thank you
> > Ryan Hess
> >
>
>
>

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

--
Regards,
Tom Ogilvy

"ryanmhess" <[email protected]> wrote in message
news:[email protected]...
> That may be the problem Tom however I am not sure how to include the
".xls"
> with
>
> If Dir(sStr & sh.Range("B7").Value) <> "" Then
> MsgBox "File Exists"
> Else
> MsgBox "File Does Not Exist"
> End If
>
> to get it to work.
>
> btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> Ryan Hess
>
> "Tom Ogilvy" wrote:
>
> > could it be a problem like:
> >
> > ? dir("C:\data6\99budget.xls")
> > 99budget.xls
> > ? dir("C:\data6\99budget")
> >
> > The second one returns nothing because it is missing the ".xls" on the
end.
> >
> > If x = 10 then Exit sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "ryanmhess" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to figure out how to set my macro to check a certain
variable
> > and
> > > if it meets a specific variable to skip the rest of the macro and end.
> > >
> > > If 'variable = X
> > > Then 'skip to the end of the macro and end
> > > Else 'continue on with the rest of the macro
> > >
> > > I also am having trouble with some code I was given earlier.
> > >
> > > 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
> > >
> > > I was told this code would check if the value in B7 was the same as
the
> > file
> > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > > 4\00-RMHTest-6
> > > ParametersXXXX\" and if there was a file named the same as the value
of B7
> > > that I would get a message "File Exists" but if there was no file with
the
> > > name as the value in B7 then it would tell me that "File Does Not
Exist".
> > >
> > > The code above no matter what value I have in B7 it tells me "File
Does
> > Not
> > > Exist" even if i use a value in B7 that already has a file saved as
the
> > value
> > > in the location given.
> > >
> > > Any help would be appreciated.
> > > Thank you
> > > Ryan Hess
> > >
> >
> >
> >

Thank you very much Tom!

Ryan Hess

"Tom Ogilvy" wrote:

> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> MsgBox "File Exists"
> Else
> MsgBox "File Does Not Exist"
> End If
>
> --
> Regards,
> Tom Ogilvy
>
> "ryanmhess" <[email protected]> wrote in message
> news:[email protected]...
> > That may be the problem Tom however I am not sure how to include the
> ".xls"
> > with
> >
> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> > MsgBox "File Exists"
> > Else
> > MsgBox "File Does Not Exist"
> > End If
> >
> > to get it to work.
> >
> > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> > Ryan Hess
> >
> > "Tom Ogilvy" wrote:
> >
> > > could it be a problem like:
> > >
> > > ? dir("C:\data6\99budget.xls")
> > > 99budget.xls
> > > ? dir("C:\data6\99budget")
> > >
> > > The second one returns nothing because it is missing the ".xls" on the
> end.
> > >
> > > If x = 10 then Exit sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "ryanmhess" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I am trying to figure out how to set my macro to check a certain
> variable
> > > and
> > > > if it meets a specific variable to skip the rest of the macro and end.
> > > >
> > > > If 'variable = X
> > > > Then 'skip to the end of the macro and end
> > > > Else 'continue on with the rest of the macro
> > > >
> > > > I also am having trouble with some code I was given earlier.
> > > >
> > > > 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
> > > >
> > > > I was told this code would check if the value in B7 was the same as
> the
> > > file
> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > > > 4\00-RMHTest-6
> > > > ParametersXXXX\" and if there was a file named the same as the value
> of B7
> > > > that I would get a message "File Exists" but if there was no file with
> the
> > > > name as the value in B7 then it would tell me that "File Does Not
> Exist".
> > > >
> > > > The code above no matter what value I have in B7 it tells me "File
> Does
> > > Not
> > > > Exist" even if i use a value in B7 that already has a file saved as
> the
> > > value
> > > > in the location given.
> > > >
> > > > Any help would be appreciated.
> > > > Thank you
> > > > Ryan Hess
> > > >
> > >
> > >
> > >
>
>
>

Understand part of it. Am new to this so here goes. Have filesaveas called
user enters a filename then save a copy as. in between I should check to see
if file exists. Then call for a diff name if it does or reject however. Not
sure how to do this. Any assistance greatly appreciated.
Thanks Curt

"Tom Ogilvy" wrote:

> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> MsgBox "File Exists"
> Else
> MsgBox "File Does Not Exist"
> End If
>
> --
> Regards,
> Tom Ogilvy
>
> "ryanmhess" <[email protected]> wrote in message
> news:[email protected]...
> > That may be the problem Tom however I am not sure how to include the
> ".xls"
> > with
> >
> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> > MsgBox "File Exists"
> > Else
> > MsgBox "File Does Not Exist"
> > End If
> >
> > to get it to work.
> >
> > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> > Ryan Hess
> >
> > "Tom Ogilvy" wrote:
> >
> > > could it be a problem like:
> > >
> > > ? dir("C:\data6\99budget.xls")
> > > 99budget.xls
> > > ? dir("C:\data6\99budget")
> > >
> > > The second one returns nothing because it is missing the ".xls" on the
> end.
> > >
> > > If x = 10 then Exit sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "ryanmhess" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I am trying to figure out how to set my macro to check a certain
> variable
> > > and
> > > > if it meets a specific variable to skip the rest of the macro and end.
> > > >
> > > > If 'variable = X
> > > > Then 'skip to the end of the macro and end
> > > > Else 'continue on with the rest of the macro
> > > >
> > > > I also am having trouble with some code I was given earlier.
> > > >
> > > > 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
> > > >
> > > > I was told this code would check if the value in B7 was the same as
> the
> > > file
> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > > > 4\00-RMHTest-6
> > > > ParametersXXXX\" and if there was a file named the same as the value
> of B7
> > > > that I would get a message "File Exists" but if there was no file with
> the
> > > > name as the value in B7 then it would tell me that "File Does Not
> Exist".
> > > >
> > > > The code above no matter what value I have in B7 it tells me "File
> Does
> > > Not
> > > > Exist" even if i use a value in B7 that already has a file saved as
> the
> > > value
> > > > in the location given.
> > > >
> > > > Any help would be appreciated.
> > > > Thank you
> > > > Ryan Hess
> > > >
> > >
> > >
> > >
>
>
>

How about this

Sub filesave()
Dim sFilename
Dim sNewFilename As String
Do
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If sFilename <> False Then
sNewFilename = Dir(sFilename)
If sNewFilename <> "" Then
MsgBox sFilename & " already Exists"
Else
ActiveWorkbook.SaveAs sFilename
End If
End If
Loop Until sFilename = False Or sNewFilename = ""
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curt" <[email protected]> wrote in message
news:[email protected]...
> Understand part of it. Am new to this so here goes. Have filesaveas called
> user enters a filename then save a copy as. in between I should check to
see
> if file exists. Then call for a diff name if it does or reject however.
Not
> sure how to do this. Any assistance greatly appreciated.
> Thanks Curt
>
> "Tom Ogilvy" wrote:
>
> > If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> > MsgBox "File Exists"
> > Else
> > MsgBox "File Does Not Exist"
> > End If
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ryanmhess" <[email protected]> wrote in message
> > news:[email protected]...
> > > That may be the problem Tom however I am not sure how to include the
> > ".xls"
> > > with
> > >
> > > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> > > MsgBox "File Exists"
> > > Else
> > > MsgBox "File Does Not Exist"
> > > End If
> > >
> > > to get it to work.
> > >
> > > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> > > Ryan Hess
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > could it be a problem like:
> > > >
> > > > ? dir("C:\data6\99budget.xls")
> > > > 99budget.xls
> > > > ? dir("C:\data6\99budget")
> > > >
> > > > The second one returns nothing because it is missing the ".xls" on
the
> > end.
> > > >
> > > > If x = 10 then Exit sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "ryanmhess" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I am trying to figure out how to set my macro to check a certain
> > variable
> > > > and
> > > > > if it meets a specific variable to skip the rest of the macro and
end.
> > > > >
> > > > > If 'variable = X
> > > > > Then 'skip to the end of the macro and end
> > > > > Else 'continue on with the rest of the macro
> > > > >
> > > > > I also am having trouble with some code I was given earlier.
> > > > >
> > > > > 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
> > > > >
> > > > > I was told this code would check if the value in B7 was the same
as
> > the
> > > > file
> > > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > > > > 4\00-RMHTest-6
> > > > > ParametersXXXX\" and if there was a file named the same as the
value
> > of B7
> > > > > that I would get a message "File Exists" but if there was no file
with
> > the
> > > > > name as the value in B7 then it would tell me that "File Does Not
> > Exist".
> > > > >
> > > > > The code above no matter what value I have in B7 it tells me "File
> > Does
> > > > Not
> > > > > Exist" even if i use a value in B7 that already has a file saved
as
> > the
> > > > value
> > > > > in the location given.
> > > > >
> > > > > Any help would be appreciated.
> > > > > Thank you
> > > > > Ryan Hess
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Curt

Why not use GetSaveAsFilename

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
'On Error Resume Next
If fname = False Then Exit Sub
If Dir(fname) <> "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Curt" <[email protected]> wrote in message news:[email protected]...
> Understand part of it. Am new to this so here goes. Have filesaveas called
> user enters a filename then save a copy as. in between I should check to see
> if file exists. Then call for a diff name if it does or reject however. Not
> sure how to do this. Any assistance greatly appreciated.
> Thanks Curt
>
> "Tom Ogilvy" wrote:
>
>> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
>> MsgBox "File Exists"
>> Else
>> MsgBox "File Does Not Exist"
>> End If
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "ryanmhess" <[email protected]> wrote in message
>> news:[email protected]...
>> > That may be the problem Tom however I am not sure how to include the
>> ".xls"
>> > with
>> >
>> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
>> > MsgBox "File Exists"
>> > Else
>> > MsgBox "File Does Not Exist"
>> > End If
>> >
>> > to get it to work.
>> >
>> > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
>> > Ryan Hess
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > could it be a problem like:
>> > >
>> > > ? dir("C:\data6\99budget.xls")
>> > > 99budget.xls
>> > > ? dir("C:\data6\99budget")
>> > >
>> > > The second one returns nothing because it is missing the ".xls" on the
>> end.
>> > >
>> > > If x = 10 then Exit sub
>> > >
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > >
>> > > "ryanmhess" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > I am trying to figure out how to set my macro to check a certain
>> variable
>> > > and
>> > > > if it meets a specific variable to skip the rest of the macro and end.
>> > > >
>> > > > If 'variable = X
>> > > > Then 'skip to the end of the macro and end
>> > > > Else 'continue on with the rest of the macro
>> > > >
>> > > > I also am having trouble with some code I was given earlier.
>> > > >
>> > > > 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
>> > > >
>> > > > I was told this code would check if the value in B7 was the same as
>> the
>> > > file
>> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
>> > > > 4\00-RMHTest-6
>> > > > ParametersXXXX\" and if there was a file named the same as the value
>> of B7
>> > > > that I would get a message "File Exists" but if there was no file with
>> the
>> > > > name as the value in B7 then it would tell me that "File Does Not
>> Exist".
>> > > >
>> > > > The code above no matter what value I have in B7 it tells me "File
>> Does
>> > > Not
>> > > > Exist" even if i use a value in B7 that already has a file saved as
>> the
>> > > value
>> > > > in the location given.
>> > > >
>> > > > Any help would be appreciated.
>> > > > Thank you
>> > > > Ryan Hess
>> > > >
>> > >
>> > >
>> > >
>>
>>
>>

And you have another response at your other thead.

Curt wrote:
>
> Understand part of it. Am new to this so here goes. Have filesaveas called
> user enters a filename then save a copy as. in between I should check to see
> if file exists. Then call for a diff name if it does or reject however. Not
> sure how to do this. Any assistance greatly appreciated.
> Thanks Curt
>
> "Tom Ogilvy" wrote:
>
> > If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> > MsgBox "File Exists"
> > Else
> > MsgBox "File Does Not Exist"
> > End If
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ryanmhess" <[email protected]> wrote in message
> > news:[email protected]...
> > > That may be the problem Tom however I am not sure how to include the
> > ".xls"
> > > with
> > >
> > > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> > > MsgBox "File Exists"
> > > Else
> > > MsgBox "File Does Not Exist"
> > > End If
> > >
> > > to get it to work.
> > >
> > > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> > > Ryan Hess
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > could it be a problem like:
> > > >
> > > > ? dir("C:\data6\99budget.xls")
> > > > 99budget.xls
> > > > ? dir("C:\data6\99budget")
> > > >
> > > > The second one returns nothing because it is missing the ".xls" on the
> > end.
> > > >
> > > > If x = 10 then Exit sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "ryanmhess" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I am trying to figure out how to set my macro to check a certain
> > variable
> > > > and
> > > > > if it meets a specific variable to skip the rest of the macro and end.
> > > > >
> > > > > If 'variable = X
> > > > > Then 'skip to the end of the macro and end
> > > > > Else 'continue on with the rest of the macro
> > > > >
> > > > > I also am having trouble with some code I was given earlier.
> > > > >
> > > > > 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
> > > > >
> > > > > I was told this code would check if the value in B7 was the same as
> > the
> > > > file
> > > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > > > > 4\00-RMHTest-6
> > > > > ParametersXXXX\" and if there was a file named the same as the value
> > of B7
> > > > > that I would get a message "File Exists" but if there was no file with
> > the
> > > > > name as the value in B7 then it would tell me that "File Does Not
> > Exist".
> > > > >
> > > > > The code above no matter what value I have in B7 it tells me "File
> > Does
> > > > Not
> > > > > Exist" even if i use a value in B7 that already has a file saved as
> > the
> > > > value
> > > > > in the location given.
> > > > >
> > > > > Any help would be appreciated.
> > > > > Thank you
> > > > > Ryan Hess
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson

Need to save the existing workbook and relocate values for next application
useing savecopy allows workbook to stay inplace and be updated.
Thanks much

"Ron de Bruin" wrote:

> Hi Curt
>
> Why not use GetSaveAsFilename
>
> Sub Test()
> Dim fname As Variant
> Dim Wb As Workbook
> Set Wb = ActiveWorkbook
>
> Again:
> fname = Application.GetSaveAsFilename("", _
> fileFilter:="Excel Files (*.xls), *.xls")
> 'On Error Resume Next
> If fname = False Then Exit Sub
> If Dir(fname) <> "" Then GoTo Again
> Wb.SaveCopyAs fname
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Curt" <[email protected]> wrote in message news:[email protected]...
> > Understand part of it. Am new to this so here goes. Have filesaveas called
> > user enters a filename then save a copy as. in between I should check to see
> > if file exists. Then call for a diff name if it does or reject however. Not
> > sure how to do this. Any assistance greatly appreciated.
> > Thanks Curt
> >
> > "Tom Ogilvy" wrote:
> >
> >> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> >> MsgBox "File Exists"
> >> Else
> >> MsgBox "File Does Not Exist"
> >> End If
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >> "ryanmhess" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > That may be the problem Tom however I am not sure how to include the
> >> ".xls"
> >> > with
> >> >
> >> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> >> > MsgBox "File Exists"
> >> > Else
> >> > MsgBox "File Does Not Exist"
> >> > End If
> >> >
> >> > to get it to work.
> >> >
> >> > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> >> > Ryan Hess
> >> >
> >> > "Tom Ogilvy" wrote:
> >> >
> >> > > could it be a problem like:
> >> > >
> >> > > ? dir("C:\data6\99budget.xls")
> >> > > 99budget.xls
> >> > > ? dir("C:\data6\99budget")
> >> > >
> >> > > The second one returns nothing because it is missing the ".xls" on the
> >> end.
> >> > >
> >> > > If x = 10 then Exit sub
> >> > >
> >> > > --
> >> > > Regards,
> >> > > Tom Ogilvy
> >> > >
> >> > >
> >> > >
> >> > > "ryanmhess" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > I am trying to figure out how to set my macro to check a certain
> >> variable
> >> > > and
> >> > > > if it meets a specific variable to skip the rest of the macro and end.
> >> > > >
> >> > > > If 'variable = X
> >> > > > Then 'skip to the end of the macro and end
> >> > > > Else 'continue on with the rest of the macro
> >> > > >
> >> > > > I also am having trouble with some code I was given earlier.
> >> > > >
> >> > > > 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
> >> > > >
> >> > > > I was told this code would check if the value in B7 was the same as
> >> the
> >> > > file
> >> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> >> > > > 4\00-RMHTest-6
> >> > > > ParametersXXXX\" and if there was a file named the same as the value
> >> of B7
> >> > > > that I would get a message "File Exists" but if there was no file with
> >> the
> >> > > > name as the value in B7 then it would tell me that "File Does Not
> >> Exist".
> >> > > >
> >> > > > The code above no matter what value I have in B7 it tells me "File
> >> Does
> >> > > Not
> >> > > > Exist" even if i use a value in B7 that already has a file saved as
> >> the
> >> > > value
> >> > > > in the location given.
> >> > > >
> >> > > > Any help would be appreciated.
> >> > > > Thank you
> >> > > > Ryan Hess
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>

Don't understand. Are you saying there is still a problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curt" <[email protected]> wrote in message
news:[email protected]...
> Need to save the existing workbook and relocate values for next
application
> useing savecopy allows workbook to stay inplace and be updated.
> Thanks much
>
> "Ron de Bruin" wrote:
>
> > Hi Curt
> >
> > Why not use GetSaveAsFilename
> >
> > Sub Test()
> > Dim fname As Variant
> > Dim Wb As Workbook
> > Set Wb = ActiveWorkbook
> >
> > Again:
> > fname = Application.GetSaveAsFilename("", _
> > fileFilter:="Excel Files
(*.xls), *.xls")
> > 'On Error Resume Next
> > If fname = False Then Exit Sub
> > If Dir(fname) <> "" Then GoTo Again
> > Wb.SaveCopyAs fname
> > End Sub
> >
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> > "Curt" <[email protected]> wrote in message
news:[email protected]...
> > > Understand part of it. Am new to this so here goes. Have filesaveas
called
> > > user enters a filename then save a copy as. in between I should check
to see
> > > if file exists. Then call for a diff name if it does or reject
however. Not
> > > sure how to do this. Any assistance greatly appreciated.
> > > Thanks Curt
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > >> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> > >> MsgBox "File Exists"
> > >> Else
> > >> MsgBox "File Does Not Exist"
> > >> End If
> > >>
> > >> --
> > >> Regards,
> > >> Tom Ogilvy
> > >>
> > >> "ryanmhess" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > That may be the problem Tom however I am not sure how to include
the
> > >> ".xls"
> > >> > with
> > >> >
> > >> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> > >> > MsgBox "File Exists"
> > >> > Else
> > >> > MsgBox "File Does Not Exist"
> > >> > End If
> > >> >
> > >> > to get it to work.
> > >> >
> > >> > btw, Thanks for the Exit Sub part, was exactly what I was looking
for.
> > >> > Ryan Hess
> > >> >
> > >> > "Tom Ogilvy" wrote:
> > >> >
> > >> > > could it be a problem like:
> > >> > >
> > >> > > ? dir("C:\data6\99budget.xls")
> > >> > > 99budget.xls
> > >> > > ? dir("C:\data6\99budget")
> > >> > >
> > >> > > The second one returns nothing because it is missing the ".xls"
on the
> > >> end.
> > >> > >
> > >> > > If x = 10 then Exit sub
> > >> > >
> > >> > > --
> > >> > > Regards,
> > >> > > Tom Ogilvy
> > >> > >
> > >> > >
> > >> > >
> > >> > > "ryanmhess" <[email protected]> wrote in
message
> > >> > > news:[email protected]...
> > >> > > > I am trying to figure out how to set my macro to check a
certain
> > >> variable
> > >> > > and
> > >> > > > if it meets a specific variable to skip the rest of the macro
and end.
> > >> > > >
> > >> > > > If 'variable = X
> > >> > > > Then 'skip to the end of the macro and end
> > >> > > > Else 'continue on with the rest of the macro
> > >> > > >
> > >> > > > I also am having trouble with some code I was given earlier.
> > >> > > >
> > >> > > > 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
> > >> > > >
> > >> > > > I was told this code would check if the value in B7 was the
same as
> > >> the
> > >> > > file
> > >> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> > >> > > > 4\00-RMHTest-6
> > >> > > > ParametersXXXX\" and if there was a file named the same as the
value
> > >> of B7
> > >> > > > that I would get a message "File Exists" but if there was no
file with
> > >> the
> > >> > > > name as the value in B7 then it would tell me that "File Does
Not
> > >> Exist".
> > >> > > >
> > >> > > > The code above no matter what value I have in B7 it tells me
"File
> > >> Does
> > >> > > Not
> > >> > > > Exist" even if i use a value in B7 that already has a file
saved as
> > >> the
> > >> > > value
> > >> > > > in the location given.
> > >> > > >
> > >> > > > Any help would be appreciated.
> > >> > > > Thank you
> > >> > > > Ryan Hess
> > >> > > >
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >

Hi Bob

> Don't understand.

Same for me

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" <[email protected]> wrote in message news:ODRwHI2%[email protected]...
> Don't understand. Are you saying there is still a problem?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Curt" <[email protected]> wrote in message
> news:[email protected]...
>> Need to save the existing workbook and relocate values for next
> application
>> useing savecopy allows workbook to stay inplace and be updated.
>> Thanks much
>>
>> "Ron de Bruin" wrote:
>>
>> > Hi Curt
>> >
>> > Why not use GetSaveAsFilename
>> >
>> > Sub Test()
>> > Dim fname As Variant
>> > Dim Wb As Workbook
>> > Set Wb = ActiveWorkbook
>> >
>> > Again:
>> > fname = Application.GetSaveAsFilename("", _
>> > fileFilter:="Excel Files
> (*.xls), *.xls")
>> > 'On Error Resume Next
>> > If fname = False Then Exit Sub
>> > If Dir(fname) <> "" Then GoTo Again
>> > Wb.SaveCopyAs fname
>> > End Sub
>> >
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> > "Curt" <[email protected]> wrote in message
> news:[email protected]...
>> > > Understand part of it. Am new to this so here goes. Have filesaveas
> called
>> > > user enters a filename then save a copy as. in between I should check
> to see
>> > > if file exists. Then call for a diff name if it does or reject
> however. Not
>> > > sure how to do this. Any assistance greatly appreciated.
>> > > Thanks Curt
>> > >
>> > > "Tom Ogilvy" wrote:
>> > >
>> > >> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
>> > >> MsgBox "File Exists"
>> > >> Else
>> > >> MsgBox "File Does Not Exist"
>> > >> End If
>> > >>
>> > >> --
>> > >> Regards,
>> > >> Tom Ogilvy
>> > >>
>> > >> "ryanmhess" <[email protected]> wrote in message
>> > >> news:[email protected]...
>> > >> > That may be the problem Tom however I am not sure how to include
> the
>> > >> ".xls"
>> > >> > with
>> > >> >
>> > >> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
>> > >> > MsgBox "File Exists"
>> > >> > Else
>> > >> > MsgBox "File Does Not Exist"
>> > >> > End If
>> > >> >
>> > >> > to get it to work.
>> > >> >
>> > >> > btw, Thanks for the Exit Sub part, was exactly what I was looking
> for.
>> > >> > Ryan Hess
>> > >> >
>> > >> > "Tom Ogilvy" wrote:
>> > >> >
>> > >> > > could it be a problem like:
>> > >> > >
>> > >> > > ? dir("C:\data6\99budget.xls")
>> > >> > > 99budget.xls
>> > >> > > ? dir("C:\data6\99budget")
>> > >> > >
>> > >> > > The second one returns nothing because it is missing the ".xls"
> on the
>> > >> end.
>> > >> > >
>> > >> > > If x = 10 then Exit sub
>> > >> > >
>> > >> > > --
>> > >> > > Regards,
>> > >> > > Tom Ogilvy
>> > >> > >
>> > >> > >
>> > >> > >
>> > >> > > "ryanmhess" <[email protected]> wrote in
> message
>> > >> > > news:[email protected]...
>> > >> > > > I am trying to figure out how to set my macro to check a
> certain
>> > >> variable
>> > >> > > and
>> > >> > > > if it meets a specific variable to skip the rest of the macro
> and end.
>> > >> > > >
>> > >> > > > If 'variable = X
>> > >> > > > Then 'skip to the end of the macro and end
>> > >> > > > Else 'continue on with the rest of the macro
>> > >> > > >
>> > >> > > > I also am having trouble with some code I was given earlier.
>> > >> > > >
>> > >> > > > 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
>> > >> > > >
>> > >> > > > I was told this code would check if the value in B7 was the
> same as
>> > >> the
>> > >> > > file
>> > >> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
>> > >> > > > 4\00-RMHTest-6
>> > >> > > > ParametersXXXX\" and if there was a file named the same as the
> value
>> > >> of B7
>> > >> > > > that I would get a message "File Exists" but if there was no
> file with
>> > >> the
>> > >> > > > name as the value in B7 then it would tell me that "File Does
> Not
>> > >> Exist".
>> > >> > > >
>> > >> > > > The code above no matter what value I have in B7 it tells me
> "File
>> > >> Does
>> > >> > > Not
>> > >> > > > Exist" even if i use a value in B7 that already has a file
> saved as
>> > >> the
>> > >> > > value
>> > >> > > > in the location given.
>> > >> > > >
>> > >> > > > Any help would be appreciated.
>> > >> > > > Thank you
>> > >> > > > Ryan Hess
>> > >> > > >
>> > >> > >
>> > >> > >
>> > >> > >
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >
>
>

RON:
Want to thank you. A little tweeking with what I had and it works just like
I wanted it to.
Thanks Again

"Ron de Bruin" wrote:

> Hi Curt
>
> Why not use GetSaveAsFilename
>
> Sub Test()
> Dim fname As Variant
> Dim Wb As Workbook
> Set Wb = ActiveWorkbook
>
> Again:
> fname = Application.GetSaveAsFilename("", _
> fileFilter:="Excel Files (*.xls), *.xls")
> 'On Error Resume Next
> If fname = False Then Exit Sub
> If Dir(fname) <> "" Then GoTo Again
> Wb.SaveCopyAs fname
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Curt" <[email protected]> wrote in message news:[email protected]...
> > Understand part of it. Am new to this so here goes. Have filesaveas called
> > user enters a filename then save a copy as. in between I should check to see
> > if file exists. Then call for a diff name if it does or reject however. Not
> > sure how to do this. Any assistance greatly appreciated.
> > Thanks Curt
> >
> > "Tom Ogilvy" wrote:
> >
> >> If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
> >> MsgBox "File Exists"
> >> Else
> >> MsgBox "File Does Not Exist"
> >> End If
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >> "ryanmhess" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > That may be the problem Tom however I am not sure how to include the
> >> ".xls"
> >> > with
> >> >
> >> > If Dir(sStr & sh.Range("B7").Value) <> "" Then
> >> > MsgBox "File Exists"
> >> > Else
> >> > MsgBox "File Does Not Exist"
> >> > End If
> >> >
> >> > to get it to work.
> >> >
> >> > btw, Thanks for the Exit Sub part, was exactly what I was looking for.
> >> > Ryan Hess
> >> >
> >> > "Tom Ogilvy" wrote:
> >> >
> >> > > could it be a problem like:
> >> > >
> >> > > ? dir("C:\data6\99budget.xls")
> >> > > 99budget.xls
> >> > > ? dir("C:\data6\99budget")
> >> > >
> >> > > The second one returns nothing because it is missing the ".xls" on the
> >> end.
> >> > >
> >> > > If x = 10 then Exit sub
> >> > >
> >> > > --
> >> > > Regards,
> >> > > Tom Ogilvy
> >> > >
> >> > >
> >> > >
> >> > > "ryanmhess" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > I am trying to figure out how to set my macro to check a certain
> >> variable
> >> > > and
> >> > > > if it meets a specific variable to skip the rest of the macro and end.
> >> > > >
> >> > > > If 'variable = X
> >> > > > Then 'skip to the end of the macro and end
> >> > > > Else 'continue on with the rest of the macro
> >> > > >
> >> > > > I also am having trouble with some code I was given earlier.
> >> > > >
> >> > > > 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
> >> > > >
> >> > > > I was told this code would check if the value in B7 was the same as
> >> the
> >> > > file
> >> > > > name saved in the location "\\HCI\HCI Share\C of A's - Building
> >> > > > 4\00-RMHTest-6
> >> > > > ParametersXXXX\" and if there was a file named the same as the value
> >> of B7
> >> > > > that I would get a message "File Exists" but if there was no file with
> >> the
> >> > > > name as the value in B7 then it would tell me that "File Does Not
> >> Exist".
> >> > > >
> >> > > > The code above no matter what value I have in B7 it tells me "File
> >> Does
> >> > > Not
> >> > > > Exist" even if i use a value in B7 that already has a file saved as
> >> the
> >> > > value
> >> > > > in the location given.
> >> > > >
> >> > > > Any help would be appreciated.
> >> > > > Thank you
> >> > > > Ryan Hess
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>