Solved

if filename is open help vba excel

Posted on 2014-01-22
9
467 Views
Last Modified: 2014-02-05
hi,
i am trying to write a vba macro in excel that says if a filename (which is a string) is open, then to do something with it.

I am getting an error, highlighting "If Sheets(Fname).Open = True Then" saying subscript out of range.

Everything works up until that line, I add msgboxes to help me see i'm keeping my filename and my code works.

thanks.


Sub CopyPaste2()
    Dim Wb As Workbook                  ' current workbook
    Dim Report As Workbook              ' referenced workbook
    Dim Ffn As String                   ' full file name of Report
    Dim Fname As String                    ' retrieved file name
    
    Ffn = "H:\Futures\Macros\F&O Report\F&O Report Instructions Macro 1-22-2014.xlsm"
    Set Report = GetWorkbook(Ffn)
    If Report Is Nothing Then
        MsgBox "Couldn't find the Report", vbCritical, _
               "Missing workbook"
        Exit Sub
    Else
        Fname = Report.Worksheets(1).Range("A11").Text & ".xlsx"
        'MsgBox Fname
    End If
    If Sheets(Fname) = True Then
    MsgBox "Good, file opened."
    Else
    MsgBox "Today's report is currently not opened."
    End If
    'If Len(Fn) Then
    End Sub

Open in new window

0
Comment
Question by:jfrank14
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 4

Expert Comment

by:tmx84
ID: 39801498
Is the "FName" workbook open at the time of running this?  If not, you will need to provide the full path to the file, not just the filename..

If this doesn't work, what is 'MsgBox Fname displaying when it pops right before the error happens?
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 39801710
Hi,

In your post you cite a line that does not exist as you have referenced it, but which, in your code, reads:

If Sheets(Fname) = True Then

I think you are using this to try and see if the workbook in question is open.

If so, then something like:

Set wbOpen = Workbooks(Fname)
If wbOpen is nothing then

could be the way to go.

You'd want to Dim the additional variable / object as a workbook, and you might need to error trap in there too (on error resume next / on error goto 0).

You could even do it without the variable.  Personally, I like to use a variable, since I can re-use it later even if i have no anticipated use right now, but that's a choice of course.

HTH,

Alan.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39801987
if you know the name of workbook fully (including its extension)

Sub test()


    Dim wb As Workbook, fnd1 as boolean
    fnd1 = false
    For Each wb In Application.Workbooks
        If wb.Name = Fname  Then
            'MsgBox "Found open workbook"
            fnd1 = true : exit for
        End If
    Next wb
    if fnd1 then
       MsgBox "Good, file opened."
    Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub

Open in new window

0
 

Author Comment

by:jfrank14
ID: 39829525
sorry for the delay as i was traveling.

Thanks for all 3 of your inputs. I tried the above and didnt have success..

Maybe i made my query a little more advanced that it needed to be.

basically, these are the steps i need to perform.

I have a workbook
In cell A11, would be the name of the file (minus the .xlsx).
If the file is open, display a MSGBOX saying its open, if not, MSGBOX saying it cant find it.

I then want to copy data on this "open" sheet once it's opened.

How can I do this?

This part of the code does show the correct file name needed:

Set Report = GetWorkbook(Ffn)
Fname = Report.Worksheets(1).Range("A11").Text & ".xlsx"

Open in new window


However, I want to set report to = current workbook. Ffn was making it too complex for no reason.

Thanks!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39832226
Fname = Report.Worksheets(1).Range("A11").Text & ".xlsx"

if IsOpen(fName) then
          MsgBox "Good, file opened."
    Else
        MsgBox "Today's report is currently not opened."
    End If

Function IsOpen(wbname As String)


    Dim wb As Workbook, fnd1 As Boolean
    fnd1 = False
    For Each wb In Application.Workbooks
        If wb.Name = wbname Then
            'MsgBox "Found open workbook"
            fnd1 = True: Exit For
        End If
    Next wb
    IsOpen = fnd1
End Function

Open in new window

0
 

Author Comment

by:jfrank14
ID: 39834161
thanks RobberBaron.

So I tried the above and still having some issues. Getting variable not defined on (Ffn).

basically I want to set Report = currentworkbook. So I wouldnt need Ffn. How can I do this?


See my current code:
      Sub robberbaron()
     Dim Fname As String
     Dim Report As Workbook
Set Report = GetWorkbook(Ffn)
Fname = Report.Worksheets(1).Range("A11").Text & ".xlsx"


If IsOpen(Fname) Then
          MsgBox "Good, file opened."
    Else
        MsgBox "Today's report is currently not opened."
    End If
End Sub
Function IsOpen(wbname As String)


    Dim wb As Workbook, fnd1 As Boolean
    fnd1 = False
    For Each wb In Application.Workbooks
        If wb.Name = wbname Then
            'MsgBox "Found open workbook"
            fnd1 = True: Exit For
        End If
    Next wb
    IsOpen = fnd1
End Function

    End Function

Open in new window

0
 
LVL 12

Expert Comment

by:Alan3285
ID: 39834172
Hi JFrank14,

Your line (4) above which reads:

Set Report = GetWorkbook(Ffn)

will not work since you have not defined Ffn prior to that line.

Perhaps you mean to have it below the subsequent line, and actually read:

Set Report = Workbooks(Fname)

which will work if you know for sure that the Workbook with the name held in Fname will exist and be open in the instance of Excel at that point.

If not, it will error too, which you could use to determine whether it exists / is open as per my post above above with something akin to:

On Error Goto WBDoesNotExist

Set Report = Workbooks.open(Fname)

On Error Goto 0

Open in new window


Then create a section of code named 'WBDoesNotExist' that does whatever you like if the file is not there.

HTH,

Alan.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39834330
i thought there was more code above the GetWorkbook.....

try this part
Sub CheckReportOpen()
    Dim fName As String, Report As Workbook
    
    Set Report = ActiveWorkbook
    
    fName = Report.Worksheets(1).Range("A11").Text & ".xlsx"
        
    If IsOpen(fName) Then
        MsgBox "Good, file opened."
    Else
        MsgBox "Today's report is currently not opened."
    End If
    
End Sub
Function IsOpen(wbname As String)

    Dim wb As Workbook, fnd1 As Boolean
    fnd1 = False
    For Each wb In Application.Workbooks
        If wb.Name = wbname Then
            'MsgBox "Found open workbook"
            fnd1 = True: Exit For
        End If
    Next wb
    IsOpen = fnd1
End Function

Open in new window

0
 

Author Closing Comment

by:jfrank14
ID: 39836274
perfect, works like a charm! thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now