Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

if filename is open help vba excel

Posted on 2014-01-22
9
Medium Priority
?
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 18

Expert Comment

by:Alan
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 18

Expert Comment

by:Alan
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 2000 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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

597 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