Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Thanks for any help.
I have a macro that pastes some info into another workbook. I open the workbook, then run the macro. I would like to make the macro code to open the file if not already open. If I make the code open a file without checking if it is already open, I would get a notice about opening it twice. So I would like to code it so that it checks if the file is already open. How do I tell if a file is already open? With workbooks.something? Or windows.something? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Hi Ian,
Try a function posted by chip Pearson: '================= Function WorkBookExists(ByVal WBName As String) As Boolean On Error Resume Next Dim Pos As Long If InStr(1, WBName, Application.PathSeparator) 0 Then For Pos = Len(WBName) To 1 Step -1 If Mid$(WBName, Pos, 1) = _ Application.PathSeparator Then Exit For End If Next Pos WBName = Mid(WBName, Pos + 1) End If If Right$(WBName, 4) < ".xls" Then WBName = WBName & ".xls" End If If Pos 0 Then WorkBookExists = CBool(Len(Workbooks(WBName).Name)) _ And (Workbooks(WBName).FullName = WBName) Else WorkBookExists = CBool(Len(Workbooks(WBName).Name)) End If End Function '<<================= --- Regards, Norman "Ian Elliott" wrote in message ... Thanks for any help. I have a macro that pastes some info into another workbook. I open the workbook, then run the macro. I would like to make the macro code to open the file if not already open. If I make the code open a file without checking if it is already open, I would get a notice about opening it twice. So I would like to code it so that it checks if the file is already open. How do I tell if a file is already open? With workbooks.something? Or windows.something? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
?B?SWFuIEVsbGlvdHQ=?= wrote
Thanks for any help. I have a macro that pastes some info into another workbook. I open the workbook, then run the macro. I would like to make the macro code to open the file if not already open. If I make the code open a file without checking if it is already open, I would get a notice about opening it twice. So I would like to code it so that it checks if the file is already open. How do I tell if a file is already open? With workbooks.something? Or windows.something? Thanks. I'm currently using the following Function I found (wish I could credit the author): Function IsOpen(ByRef BookName As String) As Boolean On Error Resume Next IsOpen = Not (Application.Workbooks(BookName) Is Nothing) End Function Called like this: If IsOpen("yourfilename.xls") Then '<your code here Else Workbooks.Open "yourfilename.xls" End If -- David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Here's a quick, sneaky way to do it.
In the other workbook, create a short, dumb macro like "Range("A1").select" Now, when you want to open it, in the current book just say "Run other book's dumb macro" (you can record a macro to do this). If the other book is open, the dumb macro will run and that's it. If it's not, the "Run" command will open the book and then run the macro. HTH Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
your code will work without the .xls extension in the isopen call.
just fyi kanan "David" wrote: ?B?SWFuIEVsbGlvdHQ=?= wrote Thanks for any help. I have a macro that pastes some info into another workbook. I open the workbook, then run the macro. I would like to make the macro code to open the file if not already open. If I make the code open a file without checking if it is already open, I would get a notice about opening it twice. So I would like to code it so that it checks if the file is already open. How do I tell if a file is already open? With workbooks.something? Or windows.something? Thanks. I'm currently using the following Function I found (wish I could credit the author): Function IsOpen(ByRef BookName As String) As Boolean On Error Resume Next IsOpen = Not (Application.Workbooks(BookName) Is Nothing) End Function Called like this: If IsOpen("yourfilename.xls") Then '<your code here Else Workbooks.Open "yourfilename.xls" End If -- David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Just for information, that depends on the windows settings. It will always
work with .xls, so suggest always to use .xls. -- Regards, Tom Ogilvy "Kanan" wrote in message ... your code will work without the .xls extension in the isopen call. just fyi kanan "David" wrote: ?B?SWFuIEVsbGlvdHQ=?= wrote Thanks for any help. I have a macro that pastes some info into another workbook. I open the workbook, then run the macro. I would like to make the macro code to open the file if not already open. If I make the code open a file without checking if it is already open, I would get a notice about opening it twice. So I would like to code it so that it checks if the file is already open. How do I tell if a file is already open? With workbooks.something? Or windows.something? Thanks. I'm currently using the following Function I found (wish I could credit the author): Function IsOpen(ByRef BookName As String) As Boolean On Error Resume Next IsOpen = Not (Application.Workbooks(BookName) Is Nothing) End Function Called like this: If IsOpen("yourfilename.xls") Then '<your code here Else Workbooks.Open "yourfilename.xls" End If -- David |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
?B?S2FuYW4=?= wrote
your code will work without the .xls extension in the isopen call. just fyi kanan Thanks -- David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
?B?S2FuYW4=?= wrote
your code will work without the .xls extension in the isopen call. just fyi kanan Actually, just tested with XL2000 and I need the extension to avoid the 'already open' alert. -- David |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Tom Ogilvy wrote
Just for information, that depends on the windows settings. It will always work with .xls, so suggest always to use .xls. I see by this that XL version isn't the controlling factor then? I need the extension as stated in a 2nd reply to OP. -- David |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Over all versions of Excel and all verions of windows, to the best of my
knowledge, it always works when you use the extension. It doesn't always work when you don't use the extension. So always use the extension. -- Regards, Tom Ogilvy "David" wrote in message ... Tom Ogilvy wrote Just for information, that depends on the windows settings. It will always work with .xls, so suggest always to use .xls. I see by this that XL version isn't the controlling factor then? I need the extension as stated in a 2nd reply to OP. -- David |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how tell if file already open?
Tom Ogilvy wrote
Over all versions of Excel and all verions of windows, to the best of my knowledge, it always works when you use the extension. It doesn't always work when you don't use the extension. So always use the extension. Ok, thanks. -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
Open a file do a macro ( made) and open next succesive file | Excel Programming | |||
Open File or Switch Between Windows if File is Open | Excel Programming | |||
Open File or Switch Between Windows if File is Open | Excel Programming |