• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

subscript out of range error when attempting to activate file

Hello,
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    
Windows(strSourceFilePath).Activate
Public Function GetFile() As String
    
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select source file"
        .Show
        If .SelectedItems.Count = 0 Then
            GetFile = "No File Selected"
        Else
            GetFile = .SelectedItems(1)
        End If
    End With
    
End Function

Open in new window

0
chtullu135
Asked:
chtullu135
  • 4
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try something like

strSourceFilePath = GetFile    
for each win in windows
  if win.name=getfile then
    Win.Activate
  endif
next win
0
 
chtullu135Author Commented:
I've tried it and I get a "Object does not support this property or method" at
If win.Name = GetFile Then
0
 
chtullu135Author Commented:
Hello,
I modified the following code I found at
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_21010666.html?sfQueryTermInfo=1+10+30+excel+open+vba+workbook
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")
  
    s1.Activate

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saqib Husain, SyedEngineerCommented:
So does this mean that you do not need any more assistance?
0
 
chtullu135Author Commented:
I just wanted to confirm and it looks like the solution  I posted earlier will work fine
0
 
chtullu135Author Commented:
The solution worked perfectly.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now