File name extension

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
 
R

Rick Rothstein

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top