Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set up opening a file from a cell in excel spreadsheet | Excel Discussion (Misc queries) | |||
how do i set up opening a file from a cell in excel spreadsheet | Excel Discussion (Misc queries) | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
Error:Invalid File format,while opening an Excel Template file | Excel Programming |