Solved

if filename is open help vba excel

Posted on 2014-01-22
9
456 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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
perfect, works like a charm! thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

8 Experts available now in Live!

Get 1:1 Help Now