subscript out of range error when attempting to activate file

Posted on 2011-10-26
Last Modified: 2012-06-21
The attached function returns a string containing the file path of the selected file.
I am getting a "Subscript Out of Range Error in the calling procedure in the second line of the following calling code.  When I used a debug.print statement after the first line, the correct file was being called.  I am running Excel 2003

strSourceFilePath = GetFile    
Public Function GetFile() As String
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select source file"
        If .SelectedItems.Count = 0 Then
            GetFile = "No File Selected"
            GetFile = .SelectedItems(1)
        End If
    End With
End Function

Open in new window

Question by:chtullu135
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Try something like

    strSourceFilePath = GetFile    
    for each win in windows
      if then
    next win

    Author Comment

    I've tried it and I get a "Object does not support this property or method" at
    If win.Name = GetFile Then

    Accepted Solution

    I modified the following code I found at
    I'd like to thank dambuster99 who provided that code in answer to the question posted on the above link
    Dim strSourceFilePath As String
        strSourceFilePath = GetFile
        Dim xlapp As Excel.Application
        Dim xlbook As Excel.Workbook
        Dim s1 As Worksheet
        Dim s2 As Worksheet
        Dim s3 As Worksheet
        Set xlapp = CreateObject("excel.application")
        Set xlbook = xlapp.Workbooks.Open(strSourceFilePath)
        xlapp.Visible = True
        Set s1 = xlbook.Sheets("New Error Transactions Recieved")
        Set s2 = xlbook.Sheets("Summary")
        Set s3 = xlbook.Sheets("Error Count by Message ID")

    Open in new window

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    So does this mean that you do not need any more assistance?

    Author Comment

    I just wanted to confirm and it looks like the solution  I posted earlier will work fine

    Author Closing Comment

    The solution worked perfectly.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    760 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

    12 Experts available now in Live!

    Get 1:1 Help Now