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

 

[Simon Lloyd]

Do you mean the open file dialogue box or do you mean the Workbook_Ope

Hello All

Please give me suggestion

How to wirte vba to find the opening file


Thanks

Marc

--
Simon Lloy

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

 

[March]

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

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

 

[Simon Lloyd]

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

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

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

I want to check, if the file opens or not







Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=51302

--
Simon Lloy

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

 

[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

 

[Simon Lloyd]

It works


However, I have another problem that I set three workbooks to automate
ru
in Windows scheduled task. For example, A-book, B-book, and C-Book
onl
A-Book and C-Book is scheduled tasks. Once runnig A-book, it will ope
B-Book, then A-Book and B-Book still opens. B-Book is active workboo
on th
screen

Next, C-Book is scheduled to run. C-Book will check if B-Book is open
the
do copy/pasteSpecial only values into the cells. With the cells contai
th
fomulas. The fomula is add-on from another source, not excel

Finally, all the job finish processing, C-Book I will close A-Book an
B-Book, also itself


From above, once C-Book opens, its error. It seems that the workboo
run
separately on another excel object on the window. If I manually run al
th
workbooks, it works well. [On the other hand, it errors when schedule
task.


My idea is to get it open the same window when C-Book is opened
because o
the A-Book and B-Book still open on the screen. Through this point,
have n
idea how to get it works

Please give me suggestion


Thanks

Marc

I will let you know how it work










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

Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=51302

--
Simon Lloy

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

 

[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)

 

[Simon Lloyd]

Runtime error 9 means that it cannot fine a worksheet that you ar
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 th
sam
excel object window

What was the error you recieved?March;192435 Wrote

It works

However, I have another problem that I set three workbooks t automate
ru
in Windows scheduled task. For example, A-book, B-book, and C-Book
onl
A-Book and C-Book is scheduled tasks. Once runnig A-book, it wil ope
B-Book, then A-Book and B-Book still opens. B-Book is activ workboo
on th
screen

Next, C-Book is scheduled to run. C-Book will check if B-Book i open
the
do copy/pasteSpecial only values into the cells. With the cell contai
th
fomulas. The fomula is add-on from another source, not excel

Finally, all the job finish processing, C-Book I will close A-Boo an
B-Book, also itself

From above, once C-Book opens, its error. It seems that th workboo
run
separately on another excel object on the window. If I manually ru al
th
workbooks, it works well. [On the other hand, it errors whe schedule
task.

My idea is to get it open the same window when C-Book is opened
because o
the A-Book and B-Book still open on the screen. Through this point
have n
idea how to get it works

Please give me suggestion






"March" wrote

I will let you know how it work


Thank



Try this, created by Bob Phillips

Code
-------------------
Function IsFileOpen(FileName As String
Dim iFilenum As Lon
Dim iErr As Lon

On Error Resume Nex
iFilenum = FreeFile(
Open FileName For Input Lock Read As #iFilenu
Close iFilenu
iErr = Er
On Error Goto

Select Case iEr
Case 0: IsFileOpen = Fals
Case 70: IsFileOpen = Tru
Case Else: Error iEr
End Selec

End Functio

Sub test(
If Not IsFileOpen("C:\MyTest\volker2.xls") The
Workbooks.Open "C:\MyTest\volker2.xls
End I
End Su

-------------------
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 th
Workbook_Ope
event?March;185764 Wrote
Hello All

Please give me suggestion

How to wirte vba to find the opening file


Thanks

Marc


-
Simon Lloy

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

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

Simon Lloyd's Profile: 'The Code Cage Forums - Vie Profile
Simo
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 openin 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' (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)