Hello All,

Please give me suggestion.

How to wirte vba to find the opening file?


Thanks,

March

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

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

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

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

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

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

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

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

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