Getting "file cannot be found in excel 2007 & 2010

I have a vba program I wrote in excel 2003 that has the user select which excel spreadsheet they want to open and then opens it for them.  This works fine in excel 2000 & 2003.  When I try it in excel 2007 or 2010 I get " '\\win2kb\d\queries\excel files\QuoteConverter' cannot be found.  Check your spelling or try a different path."

The code that opens the spreadsheet is attached.  It doesn't matter which I try, I get the same error message.  Is there a new way of opening files in vba with 2007 & 2010?



strFilePath = "\\win2kb\d\queries\excel files\"

        Select Case lstQueryList.ListIndex
           Case 0
              '  strSelectString = "SOwNoMRP"
               strFilePath = strFilePath & "Open SO with No MRP.xls"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 1
              '  strSelectString = "PartAvailability"
               strFilePath = strFilePath & "Part Availability"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 2
              '  strSelectString = "COS"
               strFilePath = strFilePath & "COS"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 3
              '  strSelectString = "BOMPrices"
               strFilePath = strFilePath & "BOMPrices"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 4
              '  strSelectString = "DirInfo"
               strFilePath = strFilePath & "DirInfo"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 5
              '  strSelectString = "QuoteConverter"
               strFilePath = strFilePath & "QuoteConverter"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 6
              '  strSelectString = "Manuf Xref to Company List"
               strFilePath = strFilePath & "Manuf Xref to Company List"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 7
              '  strSelectString = "Backlog Report"
               strFilePath = strFilePath & "Backlog Report"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 8
              '  strSelectString = "BBE Convert"
               strFilePath = strFilePath & "BBE Convert"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 9
              '  strSelectString = "Gem Merge"
               strFilePath = strFilePath & "Gem Merge"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 10
              '  strSelectString = "Create Cutlist"
               strFilePath = strFilePath & "Create Cutlist"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 11
              '  strSelectString = "TopLevelByCC"
               strFilePath = strFilePath & "TopLevelByCC"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 12
              '  strSelectString = "Completed Work Orders"
               strFilePath = strFilePath & "Completed Work Orders"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
               
           Case 13
              '  strSelectString = "Get Buyer #"
               strFilePath = strFilePath & "GetBuyerno"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
               
           Case 14
              '  strSelectString = "Assign Forced Lengths"
               strFilePath = strFilePath & "Assign Forced Lengths"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False

          Case Else
               MsgBox ("You didn't select anything")
       End Select

Open in new window

glennmgrayAsked:
Who is Participating?
 
jppintoConnect With a Mentor Commented:
I think I see the problem:

Only on the first part of your code you have mentioned the file extension:

Case 0
              '  strSelectString = "SOwNoMRP"
               strFilePath = strFilePath & "Open SO with No MRP.xls"
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False
           Case 1
              '  strSelectString = "PartAvailability"
               strFilePath = strFilePath & "Part Availability"  - here you're missing the .xls
               frmMain.Hide
               Workbooks.Open (strFilePath)
               Workbooks("Query Launcher.xls").Close savechanges:=False


On the Case 1 group, instead of this:

strFilePath = strFilePath & "Part Availability"

you should have like this:

strFilePath = strFilePath & "Part Availability.xls"

On the other's, you're also missing the file extensions...

jppinto
0
 
jppintoCommented:
Do the files still have the .xls extension or where they saved as Excel 2007 and have a .xlsx extension?
0
 
glennmgrayAuthor Commented:
That worked.  Apparently the older excel defaulted to .xls as it works fine with 2000 & 2003.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.