Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Code not always working
Have a macro unsed by many people to append information their worksheet to
another worksheet called 'Master.xls'. At the end of the day, the Master.xls is renamed for batch process. So at the start of each day the the first person who tries to update the Master.xls creates it. Been using the below code being used to check if the master.xls worksheet already exist. Problem is it does not always work. Sometimes the result for the code '.Execute 1' will be true, others times result is false, the Master file exist in both instances. Any suggestion on how to correct? 'Test to see if a Workbook exists '''''''''''''''''''''''''''''''' Dim i As Integer With Application.FileSearch .LookIn = "\\s1racft1\public\PreventativeExpedite" .Filename = "Master.xls" If .Execute 1 Then 'Workbook exists, open the workbook Workbooks.Open Filename:="\\server\public\PreventativeExpedite\Ma ster.xls" Else 'There is not a Workbook, create a new workbook Set NewBook = Workbooks.Add With NewBook .Title = "Master" .SaveAs Filename:="\\server\public\PreventativeExpedite\Ma ster.xls" End With End If End With End Sub Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Code not always working
Here is some un-tested code but it might give you some ideas...
Public Function ReturnMaster() as workbook dim wbkReturn as workbook on error resume next set wbkReturn = workbooks("master.xls") if wbkReturn is nothing then set wbkReturn = workbooks.open("\\s1racft1" & _ "\public\PreventativeExpedite\master.xls") end if if wbkReturn is nothing then set wbkReturn = workbooks.add wbkreturn.saveas("\\s1racft1\public\PreventativeEx pedite\master.xls") end if set ReturnMaster = wbkReturn end function -- HTH... Jim Thomlinson "twperplex" wrote: Have a macro unsed by many people to append information their worksheet to another worksheet called 'Master.xls'. At the end of the day, the Master.xls is renamed for batch process. So at the start of each day the the first person who tries to update the Master.xls creates it. Been using the below code being used to check if the master.xls worksheet already exist. Problem is it does not always work. Sometimes the result for the code '.Execute 1' will be true, others times result is false, the Master file exist in both instances. Any suggestion on how to correct? 'Test to see if a Workbook exists '''''''''''''''''''''''''''''''' Dim i As Integer With Application.FileSearch .LookIn = "\\s1racft1\public\PreventativeExpedite" .Filename = "Master.xls" If .Execute 1 Then 'Workbook exists, open the workbook Workbooks.Open Filename:="\\server\public\PreventativeExpedite\Ma ster.xls" Else 'There is not a Workbook, create a new workbook Set NewBook = Workbooks.Add With NewBook .Title = "Master" .SaveAs Filename:="\\server\public\PreventativeExpedite\Ma ster.xls" End With End If End With End Sub Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Code Is Not Working | Excel Discussion (Misc queries) | |||
Pop up code not working | Excel Programming | |||
Wht is this Code not Working ? | Excel Programming | |||
Vb Code not working | Excel Programming | |||
Code not working | Excel Programming |