|
How to wirte vba to find the opening file?
Hello All,
Please give me suggestion. How to wirte vba to find the opening file? Thanks, March |
How to wirte vba to find the opening file?
Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
I mean the file already open, not the open file dialogue box.
I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
I will let you know how it work.
Thanks "Simon Lloyd" wrote: Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
It works.
However, I have another problem that I set three workbooks to automated run in Windows scheduled task. For example, A-book, B-book, and C-Book, only A-Book and C-Book is scheduled tasks. Once runnig A-book, it will open B-Book, then A-Book and B-Book still opens. B-Book is active workbook on the screen. Next, C-Book is scheduled to run. C-Book will check if B-Book is open, then do copy/pasteSpecial only values into the cells. With the cells contain the fomulas. The fomula is add-on from another source, not excel. Finally, all the job finish processing, C-Book I will close A-Book and B-Book, also itself. From above, once C-Book opens, its error. It seems that the workbook runs separately on another excel object on the window. If I manually run all the workbooks, it works well. [On the other hand, it errors when scheduled task.] My idea is to get it open the same window when C-Book is opened, because of the A-Book and B-Book still open on the screen. Through this point, I have no idea how to get it works. Please give me suggestion. Thanks, March "March" wrote: I will let you know how it work. Thanks "Simon Lloyd" wrote: Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
What was the error you recieved?March;192435 Wrote: It works. However, I have another problem that I set three workbooks to automated run in Windows scheduled task. For example, A-book, B-book, and C-Book, only A-Book and C-Book is scheduled tasks. Once runnig A-book, it will open B-Book, then A-Book and B-Book still opens. B-Book is active workbook on the screen. Next, C-Book is scheduled to run. C-Book will check if B-Book is open, then do copy/pasteSpecial only values into the cells. With the cells contain the fomulas. The fomula is add-on from another source, not excel. Finally, all the job finish processing, C-Book I will close A-Book and B-Book, also itself. From above, once C-Book opens, its error. It seems that the workbook runs separately on another excel object on the window. If I manually run all the workbooks, it works well. [On the other hand, it errors when scheduled task.] My idea is to get it open the same window when C-Book is opened, because of the A-Book and B-Book still open on the screen. Through this point, I have no idea how to get it works. Please give me suggestion. Thanks, March "March" wrote: I will let you know how it work. Thanks "Simon Lloyd" wrote: Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=51302)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
It's Run-time error "9": Sucscript out of range.
This is why I would like to know how to scheduled it to open in the same excel object window. "Simon Lloyd" wrote: What was the error you recieved?March;192435 Wrote: It works. However, I have another problem that I set three workbooks to automated run in Windows scheduled task. For example, A-book, B-book, and C-Book, only A-Book and C-Book is scheduled tasks. Once runnig A-book, it will open B-Book, then A-Book and B-Book still opens. B-Book is active workbook on the screen. Next, C-Book is scheduled to run. C-Book will check if B-Book is open, then do copy/pasteSpecial only values into the cells. With the cells contain the fomulas. The fomula is add-on from another source, not excel. Finally, all the job finish processing, C-Book I will close A-Book and B-Book, also itself. From above, once C-Book opens, its error. It seems that the workbook runs separately on another excel object on the window. If I manually run all the workbooks, it works well. [On the other hand, it errors when scheduled task.] My idea is to get it open the same window when C-Book is opened, because of the A-Book and B-Book still open on the screen. Through this point, I have no idea how to get it works. Please give me suggestion. Thanks, March "March" wrote: I will let you know how it work. Thanks "Simon Lloyd" wrote: Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=51302)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
Runtime error 9 means that it cannot fine a worksheet that you are specifying, check the spelling and look for eroneous spaces before, during and after the sheet names. March;192520 Wrote: It's Run-time error "9": Sucscript out of range. This is why I would like to know how to scheduled it to open in the same excel object window. "Simon Lloyd" wrote: What was the error you recieved?March;192435 Wrote: It works. However, I have another problem that I set three workbooks to automated run in Windows scheduled task. For example, A-book, B-book, and C-Book, only A-Book and C-Book is scheduled tasks. Once runnig A-book, it will open B-Book, then A-Book and B-Book still opens. B-Book is active workbook on the screen. Next, C-Book is scheduled to run. C-Book will check if B-Book is open, then do copy/pasteSpecial only values into the cells. With the cells contain the fomulas. The fomula is add-on from another source, not excel. Finally, all the job finish processing, C-Book I will close A-Book and B-Book, also itself. From above, once C-Book opens, its error. It seems that the workbook runs separately on another excel object on the window. If I manually run all the workbooks, it works well. [On the other hand, it errors when scheduled task.] My idea is to get it open the same window when C-Book is opened, because of the A-Book and B-Book still open on the screen. Through this point, I have no idea how to get it works. Please give me suggestion. Thanks, March "March" wrote: I will let you know how it work. Thanks "Simon Lloyd" wrote: Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com)))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1))) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh....php?t=51302))) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=51302)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
How to wirte vba to find the opening file?
I know what does it mean on the error. However, what I try to tell you is
when I manaully run A-Book then following with C-Book, it works, no any error. BUT If run in Windows Scheduled task, its ERROR. Then what should I do. Thanks "Simon Lloyd" wrote: Runtime error 9 means that it cannot fine a worksheet that you are specifying, check the spelling and look for eroneous spaces before, during and after the sheet names. March;192520 Wrote: It's Run-time error "9": Sucscript out of range. This is why I would like to know how to scheduled it to open in the same excel object window. "Simon Lloyd" wrote: What was the error you recieved?March;192435 Wrote: It works. However, I have another problem that I set three workbooks to automated run in Windows scheduled task. For example, A-book, B-book, and C-Book, only A-Book and C-Book is scheduled tasks. Once runnig A-book, it will open B-Book, then A-Book and B-Book still opens. B-Book is active workbook on the screen. Next, C-Book is scheduled to run. C-Book will check if B-Book is open, then do copy/pasteSpecial only values into the cells. With the cells contain the fomulas. The fomula is add-on from another source, not excel. Finally, all the job finish processing, C-Book I will close A-Book and B-Book, also itself. From above, once C-Book opens, its error. It seems that the workbook runs separately on another excel object on the window. If I manually run all the workbooks, it works well. [On the other hand, it errors when scheduled task.] My idea is to get it open the same window when C-Book is opened, because of the A-Book and B-Book still open on the screen. Through this point, I have no idea how to get it works. Please give me suggestion. Thanks, March "March" wrote: I will let you know how it work. Thanks "Simon Lloyd" wrote: Try this, created by Bob Phillips: Code: -------------------- Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error Goto 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub -------------------- March;185826 Wrote: I mean the file already open, not the open file dialogue box. I want to check, if the file opens or not? "Simon Lloyd" wrote: Do you mean the open file dialogue box or do you mean the Workbook_Open event?March;185764 Wrote: Hello All, Please give me suggestion. How to wirte vba to find the opening file? Thanks, March -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com)))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1))) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh....php?t=51302))) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' ('The Code Cage' (http://www.thecodecage.com))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' ('How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=51302)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'How to wirte vba to find the opening file? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=51302) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51302 |
| All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com