confirmation of file before opening spreadsheet
 

I want to write code in the Worksheet so that when users open the
spreadsheet it looks for and confirms that a file with a certain name
exists in the Windows directory. Otherwise it closes the spreadsheet.
Does anyone know how to do this?
your help will be appreciated

phil in da uk

confirmation of file before opening spreadsheet
 

Hi,

This is a modification of Tom Olgive's code found by searching the Google
group.
If the workbook is not open it will open it and then activate your
spreadsheet.
You need to change some info to suit your needs.


Sub chectforworkbook()
Dim wkbk As Workbook
Dim sName As String
sName = "Integrity.xls"
On Error Resume Next
Set wkbk = Workbooks(sName)
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(sName)
Windows("Book2").Activate
End If
End Sub
"phil" wrote in message
...
I want to write code in the Worksheet so that when users open the
spreadsheet it looks for and confirms that a file with a certain name
exists in the Windows directory. Otherwise it closes the spreadsheet.
Does anyone know how to do this?
your help will be appreciated

phil in da uk

confirmation of file before opening spreadsheet
 

Thanks for trying to help, but I haven't explained it properly.
I'm attaching the code below. When the workbook (TMR) opens, I want it
to close again, unless the file 'hyperlink.twd' is found in Windows sys32.
thanks for your patience
here is the code

Private Sub Workbook_Open()
Worksheets.Select
Columns("A:M").Select
ActiveWindow.Zoom = True
ActiveSheet.ScrollArea = "A1:M30"
Range("A1").Select
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Enabled = False
Dim NumberofTBs As Integer
Dim TBC As Integer
Let NumberofTBs = Toolbars.Count
For TBC = 1 To NumberofTBs
Toolbars(TBC).Visible = False
Next TBC
For TBC = 2 To 18
Application.CommandBars(TBC).Visible = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
Next TBC
Application.Run "TMR.xls!Macro74"
Application.Run "TMR.xls!Macro76"

Sheets("MENU").Select

If ActiveWorkbook.FullName = "C:\TMR\TMR.xls" Then
Sheets("MENU").Select
Else
Sheets("End").Select
Range("A1").Select
MsgBox "Contact Psyfactor Ltd for Licence", vbOKOnly, "TMR"
Application.ActiveWorkbook.Save
Application.QUIT


End If

End Sub



"Charles Harmon" wrote:

Hi,

This is a modification of Tom Olgive's code found by searching the Google
group.
If the workbook is not open it will open it and then activate your
spreadsheet.
You need to change some info to suit your needs.


Sub chectforworkbook()
Dim wkbk As Workbook
Dim sName As String
sName = "Integrity.xls"
On Error Resume Next
Set wkbk = Workbooks(sName)
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(sName)
Windows("Book2").Activate
End If
End Sub
"phil" wrote in message
...
I want to write code in the Worksheet so that when users open the
spreadsheet it looks for and confirms that a file with a certain name
exists in the Windows directory. Otherwise it closes the spreadsheet.
Does anyone know how to do this?
your help will be appreciated

phil in da uk

confirmation of file before opening spreadsheet
 

Phil,

Not sure if this is what you want but I changed some of your code to check
for hyperfile.twd and if it does not exist then the workbook should close.
Not tested.

Charles

"phil" wrote in message
...
Thanks for trying to help, but I haven't explained it properly.
I'm attaching the code below. When the workbook (TMR) opens, I want it
to close again, unless the file 'hyperlink.twd' is found in Windows sys32.
thanks for your patience
here is the code

Private Sub Workbook_Open()
Worksheets.Select
Columns("A:M").Select
ActiveWindow.Zoom = True
ActiveSheet.ScrollArea = "A1:M30"
Range("A1").Select
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Enabled = False
Dim NumberofTBs As Integer
Dim TBC As Integer
Let NumberofTBs = Toolbars.Count
For TBC = 1 To NumberofTBs
Toolbars(TBC).Visible = False
Next TBC
For TBC = 2 To 18
Application.CommandBars(TBC).Visible = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
Next TBC
Application.Run "TMR.xls!Macro74"
Application.Run "TMR.xls!Macro76"

Sheets("MENU").Select

On Error Resume Next
ChDir "C:\hyperlink.twd"
If Err = "76" Then
MsgBox "File : " & "Hyperlink.twd" & " Does not exist"
Sheets("End").Select
Range("A1").Select
MsgBox "Contact Psyfactor Ltd for Licence", vbOKOnly, "TMR"
Application.ActiveWorkbook.Save
Application.QUIT
else
If ActiveWorkbook.FullName = "C:\TMR\TMR.xls" Then
Sheets("MENU").Select

End If
End Sub



"Charles Harmon" wrote:

Hi,

This is a modification of Tom Olgive's code found by searching the Google
group.
If the workbook is not open it will open it and then activate your
spreadsheet.
You need to change some info to suit your needs.


Sub chectforworkbook()
Dim wkbk As Workbook
Dim sName As String
sName = "Integrity.xls"
On Error Resume Next
Set wkbk = Workbooks(sName)
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(sName)
Windows("Book2").Activate
End If
End Sub
"phil" wrote in message
...
I want to write code in the Worksheet so that when users open the
spreadsheet it looks for and confirms that a file with a certain name
exists in the Windows directory. Otherwise it closes the spreadsheet.
Does anyone know how to do this?
your help will be appreciated

phil in da uk

confirmation of file before opening spreadsheet
 

Charles, thanks for this.
I

"Charles Harmon" wrote:

Phil,

Not sure if this is what you want but I changed some of your code to check
for hyperfile.twd and if it does not exist then the workbook should close.
Not tested.

Charles

"phil" wrote in message
...
Thanks for trying to help, but I haven't explained it properly.
I'm attaching the code below. When the workbook (TMR) opens, I want it
to close again, unless the file 'hyperlink.twd' is found in Windows sys32.
thanks for your patience
here is the code

Private Sub Workbook_Open()
Worksheets.Select
Columns("A:M").Select
ActiveWindow.Zoom = True
ActiveSheet.ScrollArea = "A1:M30"
Range("A1").Select
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Enabled = False
Dim NumberofTBs As Integer
Dim TBC As Integer
Let NumberofTBs = Toolbars.Count
For TBC = 1 To NumberofTBs
Toolbars(TBC).Visible = False
Next TBC
For TBC = 2 To 18
Application.CommandBars(TBC).Visible = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
Next TBC
Application.Run "TMR.xls!Macro74"
Application.Run "TMR.xls!Macro76"

Sheets("MENU").Select

On Error Resume Next
ChDir "C:\hyperlink.twd"
If Err = "76" Then
MsgBox "File : " & "Hyperlink.twd" & " Does not exist"
Sheets("End").Select
Range("A1").Select
MsgBox "Contact Psyfactor Ltd for Licence", vbOKOnly, "TMR"
Application.ActiveWorkbook.Save
Application.QUIT
else
If ActiveWorkbook.FullName = "C:\TMR\TMR.xls" Then
Sheets("MENU").Select

End If
End Sub



"Charles Harmon" wrote:

Hi,

This is a modification of Tom Olgive's code found by searching the Google
group.
If the workbook is not open it will open it and then activate your
spreadsheet.
You need to change some info to suit your needs.


Sub chectforworkbook()
Dim wkbk As Workbook
Dim sName As String
sName = "Integrity.xls"
On Error Resume Next
Set wkbk = Workbooks(sName)
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(sName)
Windows("Book2").Activate
End If
End Sub
"phil" wrote in message
...
I want to write code in the Worksheet so that when users open the
spreadsheet it looks for and confirms that a file with a certain name
exists in the Windows directory. Otherwise it closes the spreadsheet.
Does anyone know how to do this?
your help will be appreciated

phil in da uk