Solved

if filename is open help vba excel

Posted on 2014-01-22
9
474 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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