How to wirte vba to find the opening file?

[March]

Hello All,

Please give me suggestion.

How to wirte vba to find the opening file?


Thanks,

March

 

[March]

I mean the file already open, not the open file dialogue box.

I want to check, if the file opens or not?

 

[March]

I will let you know how it work.


Thanks

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

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[March]

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]

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.

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

I will let you know how it work.


Thanks

:


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?




:


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/showthread.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/showthread.php?t=51302)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[March]

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

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.

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.

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




:

I will let you know how it work.


Thanks

:


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?




:


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/showthread.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/showthread.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/showthread.php?t=51302)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)