I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John

Hi, John,

Here's something that I use for testing whether files exist. This particular
example concatenates the filename from a folder name string generated in a
sub macro and a workbookname string, generated by using the folder name
string and some variables contained within range objects pointing to cells on
the workbook itself, but I'm sure you could adapt it to your particular needs.


Public DBSheet As Worksheet
Public Year As Range
Public WeekNumber As Range

Sub ProcessWorkbook
SetFolderName
DefineWorkbookObjects
ChDir FolderName
CombFileName = FolderName & "SMP " & Format(ExceptionsYear, "00") & _
ExceptionsWeekNumber & ".xls"
If Dir(CombFileName) = "" Then
MsgBox (CombFileName & " does not exist")
Exit Sub
End If
'do your code here if the workbook exists
end sub

Sub SetFolderName()
FolderName = "C:\"
end sub

Sub DefineWorksheetObjects()
Set DBSheet = Worksheets("Database")
Set Year = DBSheet.Range("Year")
Set WeekNumber = DBSheet.Range("WeekNumber")
End Sub

Hope this helps

Pete

"JohnUK" wrote:

I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John

Hi, John,

A couple of bloopers:

I didn't declare a variable for FolderName:
Dim FolderName as String

Also

CombFileName = FolderName & "SMP " & Format(ExceptionsYear, "00") & _
ExceptionsWeekNumber & ".xls"

should read:

CombFileName = FolderName & "SMP " & Format(Year, "00") & _
WeekNumber & ".xls"

I copied the code from two different places..!

Regards

Pete


"Peter Rooney" wrote:

Hi, John,

Here's something that I use for testing whether files exist. This particular
example concatenates the filename from a folder name string generated in a
sub macro and a workbookname string, generated by using the folder name
string and some variables contained within range objects pointing to cells on
the workbook itself, but I'm sure you could adapt it to your particular needs.


Public DBSheet As Worksheet
Public Year As Range
Public WeekNumber As Range

Sub ProcessWorkbook
SetFolderName
DefineWorkbookObjects
ChDir FolderName
CombFileName = FolderName & "SMP " & Format(ExceptionsYear, "00") & _
ExceptionsWeekNumber & ".xls"
If Dir(CombFileName) = "" Then
MsgBox (CombFileName & " does not exist")
Exit Sub
End If
'do your code here if the workbook exists
end sub

Sub SetFolderName()
FolderName = "C:\"
end sub

Sub DefineWorksheetObjects()
Set DBSheet = Worksheets("Database")
Set Year = DBSheet.Range("Year")
Set WeekNumber = DBSheet.Range("WeekNumber")
End Sub

Hope this helps

Pete

"JohnUK" wrote:

I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John

Trying to do almost what you have. Want to ask user if backup done also ask
again to be sure then do a save copy as. May have to MkDir also or is it
Folder. This will retain active sheet for update by other code. Save file
loses active sheet. Am new to this but this is my last problem have done the
rest with help. at 68 never thought I would be trying something like this but
its a challenge. Any thing will help. I've been able to twek others to fit my
needs.
Thanks in Advance.
Curt

"JohnUK" wrote:

I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John

Curt,

Firt of all, you have my total admiration for getting into VBA and the ripe
young age of 68! :-) Second, don't worry about asking for help on these
forums - some of my best pieces of work have only been made possible due to
the help and advice of the contributors, and now, I've started helping other
people, too - what goes around, comes around, and all that!

Are you looking at a means to ask a question and get a response, before
proceeding with your code? It looks like you need a version of MSGBOX (see
help for syntax), coupled with an IF ELSE statement to process the user's
response to that MSGBOX i.e. if they clicked Yes or No etc. Am I on the right
lines?

Welcome to the wierd and wonderful world of VBA!

Pete

"Curt" wrote:

Trying to do almost what you have. Want to ask user if backup done also ask
again to be sure then do a save copy as. May have to MkDir also or is it
Folder. This will retain active sheet for update by other code. Save file
loses active sheet. Am new to this but this is my last problem have done the
rest with help. at 68 never thought I would be trying something like this but
its a challenge. Any thing will help. I've been able to twek others to fit my
needs.
Thanks in Advance.
Curt

"JohnUK" wrote:

I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John

I have suprised myself got all working. need to check if filename entered
into dialog box exists. If does reject filename.

Dim fn As String
fn = Application.GetSaveAsFilename()

If UCase(fn) = "FALSE" Then Exit Sub

Think this is where code for checking should go

ActiveWorkbook.SaveCopyAs fn
End If

Not sure what I need to do this
its a challenge and many have said what are you doing. I just laugh.
Your reply is sure uplifting. I can to conclusion if I am still learning I
am still alive.Am sure thankful for this forum
Thanks again
Curt


"Peter Rooney" wrote:

Curt,

Firt of all, you have my total admiration for getting into VBA and the ripe
young age of 68! :-) Second, don't worry about asking for help on these
forums - some of my best pieces of work have only been made possible due to
the help and advice of the contributors, and now, I've started helping other
people, too - what goes around, comes around, and all that!

Are you looking at a means to ask a question and get a response, before
proceeding with your code? It looks like you need a version of MSGBOX (see
help for syntax), coupled with an IF ELSE statement to process the user's
response to that MSGBOX i.e. if they clicked Yes or No etc. Am I on the right
lines?

Welcome to the wierd and wonderful world of VBA!

Pete

"Curt" wrote:

Trying to do almost what you have. Want to ask user if backup done also ask
again to be sure then do a save copy as. May have to MkDir also or is it
Folder. This will retain active sheet for update by other code. Save file
loses active sheet. Am new to this but this is my last problem have done the
rest with help. at 68 never thought I would be trying something like this but
its a challenge. Any thing will help. I've been able to twek others to fit my
needs.
Thanks in Advance.
Curt

"JohnUK" wrote:

I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John

maybe...

Option Explicit
Sub testme01()

Dim myFileName As Variant
Dim okToSave As Boolean

okToSave = False
Do
myFileName = Application.GetSaveAsFilename _
(filefilter:="Excel Files,*.xls")

If myFileName = False Then
okToSave = False
Exit Do 'cancel
End If

If Dir(myFileName) = "" Then
okToSave = True
Exit Do
Else
MsgBox "Please use a name that doesn't exist"
End If

Loop

If okToSave Then
'do the save
Else
MsgBox "ok, I won't save it"
End If
End Sub




Curt wrote:

Want to take input from filesaveas dialog box then check if that filename
exists if it does reject ask for another. I am suprised got all else working.
This checking is the final step.Think I know wher to put the code just can
not find any help thru vba help or JW's books or any I have got a hold of.
Also may need to create the folder if it does not exist. Going to show it
tomorrow.See what everyone thinks.
Any code idea would sure be greatly appreciated. Think I forgot to check the
notify me last time.
Thanks Curt

"Peter Rooney" wrote:

Curt,

Firt of all, you have my total admiration for getting into VBA and the ripe
young age of 68! :-) Second, don't worry about asking for help on these
forums - some of my best pieces of work have only been made possible due to
the help and advice of the contributors, and now, I've started helping other
people, too - what goes around, comes around, and all that!

Are you looking at a means to ask a question and get a response, before
proceeding with your code? It looks like you need a version of MSGBOX (see
help for syntax), coupled with an IF ELSE statement to process the user's
response to that MSGBOX i.e. if they clicked Yes or No etc. Am I on the right
lines?

Welcome to the wierd and wonderful world of VBA!

Pete

"Curt" wrote:

Trying to do almost what you have. Want to ask user if backup done also ask
again to be sure then do a save copy as. May have to MkDir also or is it
Folder. This will retain active sheet for update by other code. Save file
loses active sheet. Am new to this but this is my last problem have done the
rest with help. at 68 never thought I would be trying something like this but
its a challenge. Any thing will help. I've been able to twek others to fit my
needs.
Thanks in Advance.
Curt

"JohnUK" wrote:

I have developed a workbook that relies on information from other Files and
folders from 'My Documents'
For example there is a macro that runs to save a copy of the workbook as a
backup into the 'Backups Folder', but if the Folder is not present the macro
will return an error.
Another macro when it is run will update the current workbook from another
file called 'Updates'.
The problem I have is that the same set of Files and folders is located on
field staffs laptops, and they have a habit of (I dont know how) losing some
of the files.
So I need a code that can check that all the files and folders are in place
and in the correct location. If not it would come back with a message to say
what files are missing.
Any help greatly appreciated
John


--

Dave Peterson