File name extension

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Below is a sub routine that worked great on my machine but when I gave it to
a user, I would get a subscript out or range error. This was due to not
having the .xls extension in the wbname variable or in the windows ( )
activate piece of the code. When calling a file does it always need the
..xls extension?

Sub See_Vendor()
x = ActiveWorkbook.Name

wbname = "Current Vendors.xls" <<<<<<<<<<<<=========

Call WorkbookIsOpen(wbname)
If Err <> 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
End If

Windows("current vendors.xls").Activate <<<<<<<<<<<<<============

For Each n In ActiveWorkbook.Names
n.Delete
Next n
Range("a2").Select
Top = ActiveCell.Address
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
bottom = ActiveCell.Address
Worksheets("Sheet1").Range(Top, bottom).Name = "Vendor"

Load Vendor_Lookup
Windows(x).Activate
Vendor_Lookup.Show

End Sub
 
You show these lines of code...

x = ActiveWorkbook.Name
wbname = "Current Vendors.xls"

However, the x variable is not used in the rest of your code... was the
assignment to wbname supposed to be x or is it a hard-coded value you user
types in? If you were supposed to be assigning x, then it should have the
xls extension IF the workbook has been saved at least once. So, I'll assume
the problem is because the xls extension was not provided by your user. If
you don't think you can impress upon him/her the importance of providing the
extension, then you can have your code do it for them. Just add this line of
code after the wbname assignment statement...

If LCase(Left(wbname, 4)) <> ".xls" Then wbname = wbname & ".xls"
 
Back
Top